Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a very complicated Excel spreadsheet (formulas accessing cells with other formulas across multiple worksheets) that in the end computes a single cell (output) with input from several other cells (parameters). Is there an automated way (an Excel macro or a standalone tool) that could start at the output cell and recursively unroll the computations until it expressed the formula in terms of parameter cells directly?

Clarification

In the comments there was a suggestion of Evaluate Formula tool. While I could not find how to activate it in Excel 2008 for Mac, from its description it sounds like it allows the user to step through evaluation of a cell. That's not what I need. What I need is a way to convert the formula in a given cell that may reference other cells containing formulas, to an equivalent formula expressed in terms of final cells (those that contain values, but not formulas).

Here's a simple example. Let

  • A1 contain = B1 + C1
  • B1 contain = B2 * B2
  • C1 contain = C2 * C2
  • B2 contain 1
  • C2 contain 2

Evaluate Formula would allow me to step through the calculation of A1 to get to the final value of 5. What I need is a tool that would unroll A1 to the formula = B2 * B2 + C2 * C2 without actually evaluating it.

share|improve this question
1  
Does “Evaluate Formula” do what you want? If not, can you please explain a bit more clearly (perhaps by giving an example) what you do want? –  Scott Feb 27 '13 at 1:22
    
@Scott - Thanks for the suggestion, but it doesn't look like that's what I need. To answer your question, I need a tool that would "unroll the computations until it expressed the formula in terms of parameter cells directly". I thought this sentence is pretty clear, but I added an explicit example of what I need and of what I don't need, and an explanation for why Evaluate Formula is probably not the right solution for my problem. –  George Skoptsov Feb 27 '13 at 4:32
    
Good example. My second guess would have been that you wanted = (1*1) + (2*2) for that situation; thanks for clearing that up. You can sort-of use “Evaluate Formula” for this, using the “Step In” function, but you would need to take a million screen shots along the way, and assemble the results manually. (This composite image, which is heavily edited for cosmetic purposes, illustrates the process.) That’s clearly not practical. –  Scott Feb 27 '13 at 18:36
    
I think boost pro does that, but I have not tried it and it is not free (there is a free trial though). Not sure if it's available on Mac either (does not look like it). –  assylias Feb 27 '13 at 19:31
    
@George, this website is looking kind of out-of-date now, but I think there are several commercial solutions of this sort. Bascially, you develop your spreadsheet formulas "natively", and then run a tool to convert them to code, which you can then compile and call from anywhere, including Excel itself: calc4web.com –  jtolle Dec 15 at 13:26

2 Answers 2

up vote 2 down vote accepted
+50

The problem

You can't do this with the Evaluate Formula because this isn't the purpose of this function. Thats why it is called evaluate, it is for evaluating the formulas. What you want is some kind of unpacking. This is a bit special need so it isn't implemented as a tool in Excel, but there are solutions if you create some Visual Basic functions/macros.

Create a VBA code module (macro) as you can see in this tutorial.

  1. Press Alt+F11
  2. Click to Module in Insert.
  3. Paste code.
Function CellFormula(Target As Range) As String
   CellFormula = Target.Formula
End Function

Then enter the following to a cell: =CellFormula(A1)

This will tell the formula of the cell. The only problem with this code, that it is only works for one level. If you want to unpack the contained cells formulas too, than you need a more complex code with recursion.

The solution

It was a long jurney but I created a VBA macro for you that implements this function. I don't state, that this code will work for every formula, but it will work in most/some of them. Also I don't state that this code will generate formulas that equivalent with the originaly entered code or will give the same result as the original.

Source code

Option Explicit

Function isChar(char As String) As Boolean
    Select Case char
        Case "A" To "Z"
            isChar = True
        Case Else
            isChar = False
    End Select
End Function

Function isNumber(char As String, isZero As Boolean) As Boolean
    Select Case char
        Case "0"
            If isZero = True Then
                isNumber = True
            Else
                isNumber = False
            End If
        Case "1" To "9"
            isNumber = True
        Case Else
            isNumber = False
    End Select
End Function

Function CellFormulaExpand(formula As String) As String
    Dim result As String
    Dim previousResult As String
    Dim cell As Range
    Dim stringArray() As String
    Dim arraySize As Integer
    Dim n As Integer
    Dim trimmer As String

    Dim c As Integer 'character number
    Dim chr As String 'current character
    Dim tempcell As String 'suspected cell's temporaly result
    Dim state As Integer 'state machine's state:
    Dim stringSize As Integer

    result = formula
    previousResult = result
    state = 0
    stringSize = 0

    For c = 0 To Len(formula) Step 1
        chr = Mid(formula, c + 1, 1)
        Select Case state
            Case 0
                If isChar(chr) Then
                    state = 1
                    tempcell = tempcell & chr
                ElseIf chr = "$" Then
                    state = 5
                    tempcell = tempcell & chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 1
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell & chr
                ElseIf isChar(chr) Then
                    state = 2
                    tempcell = tempcell & chr
                ElseIf chr = "$" Then
                    state = 6
                    tempcell = tempcell & chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 2
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell + chr
                ElseIf isChar(chr) Then
                    state = 3
                    tempcell = tempcell + chr
                ElseIf chr = "$" Then
                    state = 6
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 3
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell + chr
                ElseIf chr = "$" Then
                    state = 6
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 4
                If isNumber(chr, True) Then
                    state = 4
                    tempcell = tempcell + chr
                Else
                    state = 0
                    stringSize = stringSize + 1
                    ReDim Preserve stringArray(stringSize - 1)
                    stringArray(stringSize - 1) = tempcell
                    tempcell = ""
                End If
            Case 5
                If isChar(chr) Then
                    state = 1
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 6
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case Else
                state = 0
                tempcell = ""
        End Select
    Next c
    If stringSize = 0 Then
        CellFormulaExpand = result
    Else
        arraySize = UBound(stringArray)
        For n = 0 To arraySize Step 1
            Set cell = Range(stringArray(n))
            If Mid(cell.formula, 1, 1) = "=" Then
                trimmer = Mid(cell.formula, 2, Len(cell.formula) - 1)
                If trimmer <> "" Then
                    result = Replace(result, stringArray(n), trimmer)
                End If
            End If
        Next
        If previousResult <> result Then
            result = CellFormulaExpand(result)
        End If
    End If
    CellFormulaExpand = result
End Function

Function CellFormula(rng As Range) As String
    CellFormula = CellFormulaExpand(rng.formula)
End Function

For make it work just create a macro (as I described it in the beggining of the answer) and copy-paste the code. After this you can use it with the =CellFormula(A1) where A1 can be any kind of 1x1 cell.

Cases it works

I created some examples so you can see it in action. In this case I demonstrate the use with strings. You can see it works perfectly. The only little bug is that somewhy the algorithm changes the semicolons to commas. After you replace them (as I did in this example), you get the correct output. Work with strings

Here you can see how it works with numbers. Now we face the first problem, that the algorithm doesn't cares about the mathematical operation sequence, thats why the red number is 6 when it should be 10. If we put the sensitive operations (like addition and subtraction) into parenthesis then the given formula entered back will give the same output as you can see in the green number in the bottom that says 10. Work with numbers

Cases it doesn't work

This algorithm is not perfect. I only tried to implement the most common uses, so it can be improved by adding more features that handle other cases like ranges.
As you can see in this example I used SUM() with a range as a parameter. Because the algorithm decrypt the cells content from top to down it starts with the replacement of the SUM() parameters than later with anything else. Therefore the : stays in his place while around it everything is replaced, so new cells are replaced near to it, who will change the meaning of it. Thus the output will be wrong. So in this case you can only use this macro to study the original formula. Work with ranges

share|improve this answer
    
Thanks for putting the effort into this. I understand the limitations, but I think this is closest to what I requested that can be reasonably done. –  George Skoptsov Mar 8 '13 at 13:55

Given your usage of the word "trace" in your question, I would assume you are familiar with the Trace Precendents and Trace Dependents functions in the Formula Auditing section of Excel?

Here is a brief explanation of each of the formula auditing tools from dummies.com:

Understanding Excel 2010's Formula Auditing Tools

share|improve this answer
    
Yes, I'm aware of these tools -- they draw dependency arrows (which fails miserably when the worksheet is large or the dependency is to a cell from a different sheet). I need more than knowing dependencies -- I need a way to produce the actual expression. Regarding the use of word 'trace' -- @killermist edited my original title - rather gratuitously and incompentently. –  George Skoptsov Mar 7 '13 at 23:49
1  
If you are comfortable with VBA, you could unroll the entire function by iterating through all the precedents with the function described here. –  user1936123 Mar 8 '13 at 2:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.