 Do You Struggle to Understand Complex Excel Formulas?

By Marie McCooey

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. 1. Select the cell with the formula to evaluate, in this example, cell G3.
2. Click the Evaluate Formula command.
The Evaluate Formula window displays the formula in the cell with the first step AVERAGE(C3:E3) underlined. 3. 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.

1. 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. 2. 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.

1. 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!

3 thoughts on “Do You Struggle to Understand Complex Excel Formulas?”

1. Brian

I have been an Excel user for many years and never used the Evaluate function before finding this Blog. Will be using frequently from now on. Thanks so much, very, very useful.

2. Evan

Hello,

I was searching for a way to learn which part of a nested formula calculates first. For instance:

=RIGHT(A1,LEN(A1)-FIND(“^”, SUBSTITUTE(A1,” “,”^”,LEN(A1)-LEN( SUBSTITUTE(A1,” “,””) ))))

If you had to numerically list the order of operations and place those numbers over each section of the formula, how would you know where to start? Is there a rule to follow etc? Thanks so much for your time.

3. Evan

Hello,
In my previous comment, I should have added that I understand how the evaluate formula command works. I was looking to learn an easier way to understand the order of how and why one part of the formula has to calculate first before the next etc etc.

I feel that knowing this will help me write sensible formulas without wasting time trying to figure out what to nest etc.

Thanks