You received an Excel file filled with long, complicated formulas that need updating by the end of the day.
You’re struggling to understand the difficult formulas. But you don’t want to ask for help and appear stupid.
Have you been in this predicament?
Don’t know where to start?
Let’s take a look at the spreadsheet.
One of the formulas is a confusing IF formula to determine if the Salesperson earned a bonus.
You’re struggling to understand the puzzling formula.
You wish you could break down the formula into parts.
Let me introduce you to Excel’s helpful Evaluate Formula feature.
It will help you solve a formula one step at a time.
Find the Evaluate Formula feature in the Formula Auditing group on the Formulas tab.
- Select the cell with the formula to evaluate, in this example, cell G3.
- Click the Evaluate Formula command.
The Evaluate Formula window displays the formula in the cell with the first step AVERAGE(C3:E3) underlined.
- Click the Evaluate button to solve the underlined formula.
Excel evaluates the AVERAGE function, displays the result and underlines the formula’s next step.
Each time you click the Evaluate button, Excel solves the underlined part of the formula and displays the result.
- Click Evaluate again to determine if the result is greater than 500.
Since the result is not greater than 500, False displays and the False portion of the IF function is underlined.
- Click Evaluate again and the FALSE portion of the IF function is evaluated and calculated.
Note: Some parts of formulas that use the IF function are not evaluated.
In this example, since the IF function evaluated to FALSE, the TRUE portion does not need to be evaluated and #N/A displays in the Evaluation box as shown above.
- Click Evaluate one more time to display the result.
When the formula evaluation is complete, the Evaluate button changes to Restart.
If you need to repeat the steps, click the Restart button.
Excel evaluates the formula again for you from the beginning.
Repeat the process as many times as you need, to help you understand the formula.
Use Excel’s Evaluate Formula to help you break down a formula’s logic.
The next time you are struggling to understand how a formula works, give it a try!