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
contain1
C2
contain2
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.
Best Answer
The problem
You can't do this with Evaluate Formula because this isn't the purpose of the function. That's 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.
Module
inInsert
.Then enter the following to a cell:
=CellFormula(A1)
This will tell the formula of the cell. The only problem with this code is that it only works for one level. If you want to unpack the contained cells formulas too, then you need a more complex code with recursion.
The solution
It was a long journey 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 is equivalent with the originally entered code or will give the same result as the original.
Source code
To make it work, just create a macro (as I described it in the beginning of the answer) and copy-paste the code. After this, you can use it with
=CellFormula(A1)
whereA1
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.
Here, you can see how it works with numbers. Now, we face the first problem that the algorithm doesn't care about the mathematical operation sequence, that's 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.
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. Since the algorithm decrypts the cells content from top to down, it starts with the replacement of theSUM()
parameters than later with anything else. Therefore, the:
stays in its 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.