You just wrote an Excel formula and it isn’t producing the result you expected? Have no fear – this article will introduce you to the Evaluate Formula’ tool which you can use to discover why your formula isn’t working. The Evaluate Formula tool achieves this by showing you the steps Excel takes to calculate your formula. This is very useful when you need to find a problem with a formula, or to understand the calculation process.
Finding the wrong value
Let’s look how the Evaluate formula’ tool can be used to trace a problem, by looking at the following sample:
You can see that something is amiss with the formula in cell C3. The formula adds 1+2+3 (cells A2, A3 and A4) and then divides the result by 3 (cell B3); this should evaluate to 2 (6 divided by 3). But we got 2.4. By opening the Evaluate Formula tool (from the Formula Auditing area of the Formula ribbon), we can work the calculation of this value step by step (click the Evaluate button in the Evaluate Formula dialog to advance):
Listed are the evaluation steps for the formula above:
- (A2+A3+A4)/B3
- (1+A3+A4)/B3
- (1+2+A4)/B3
- (3+A4)/B3
- (3+3)/B3
- (6)/B3
- 6/B3
- 6/2.5
- 2.4
First we’ll examine the mechanics of the display. The underlined part of the formula in each step is the part that will be evaluated in the next step. The italic part of the formula in each step is the part that was evaluated in the previous step.
In this case, the one-but-last step seems off: we expected B3 to contain 3, but it seems to contain 2.5. The problem is probably that the cell’s display is set to show zero decimal places:
And there you have it – we’ve found the problem. Changing the cell’s formatting to show decimal places will show that cell B3 contains 2.5.
Summary
This article provides a little taste of how the Formula Evaluation tool can be used to help us fix problems in our formulas. It’s also a great tool for helping us write formulas, but that discussion can wait for another time. Can you think of ways this tool can serve you? Please share with us in the comments…
==== About the Author ====
Joseph Reese is the founder of Excel-Formulas.com an Excel consulting firm. Head over to his website if you want to improve your performance with Excel.
