﻿ Excel's Evaluate Formula Tool - How To Use It
Trouble with Excel Formulas? Try the Evaluate Formula Tool
By on February 25th, 2011

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

#### 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

#### 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.

Tags: , ,
Author: Guest Posts
Posts written by Guest Authors on Techie Buzz. If you want to write a Guest Post for us please read our Guest Posting Guidelines.

Guest Posts has written and can be contacted at guest@techie-buzz.com.