Sometimes, understanding how a figure is calculated is difficult because the formula seems quite complicated; comprising of several intermediate calculations and logical tests. Luckily Microsoft® Excel® has a powerful auditing tool, Evaluate Formula, which will help you to unravel the different parts of a formula, in the order that it is calculated, to understand how the end result is constructed.
For instance, a formula like =IF(AVERAGE(C4:C15)>61000,7%*C16),0) is easier to understand when you can see the intermediate steps using the Evaluate Formula tool. Our example in this tip shows you how to evaluate a formula that calculates a bonus for sales staff.
Note: Download the sample workbook to practice this exercise.
Applies to: Microsoft Excel 2007, 2010 and 2013
The screenshot below will be used for this example.
To evaluate the formula for the amount $51 450.00 in cell B20 follow the steps below.
1. Select cell B20.
2. Under the Formulas Tab, in the Formula Auditing group, select Evaluate Formula.
3. The Evaluate Formula screen will pop up.
Note: The ‘IF’ statement has the following syntax
- Logical test: Average(C4:C15)>6100
- Value if true: C16(Total Sales) * 7%
- Value if false: 0
4. Select Evaluate and the values will be displayed in the formula.
5. Select Evaluate again.
- As the value of 61250 is higher than 6100, it will be evaluated to True which means the next part of the formula will be calculated (C16*7%).
6. Select Evaluate again.
- The value 735000 is the total sales figure in cell C16 which is multiplied by 7% to get the bonus amount for the sale staff.
7. After selecting Evaluate again the evaluation will be displayed as shown below. The value 7% is converted to 0.07.
8. The final result of 51450 is displayed.
- The ‘IF’ statement returns True, since the value of average (C4:C15) > 61000.
9. Select Evaluate one last time to arrive at the final value.
NOTE:
- If the underlined part of the formula is a reference to another formula, click Step In to display the other formula in the Evaluation box. Click Step Out to go back to the previous cell and formula.
- The Step In button is not available for a reference the second time the reference appears in the formula, or if the formula refers to a cell in a separate workbook.
