Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to evaluate complex formulas

Tags 
Summary 
In this video, we look at how to use Evaluate Formula to solve complex formulas one step at a time. It's a great way to understand how Excel solves a formula.
Video Transcript 

Excel has a handy feature called Evaluate Formula, which solves a formula one step at a time.

Each time you click the Evaluate button, Excel will solve the underlined part of the formula and show you the result.

Let's take a look.

Here's the same worksheet I showed you earlier, when we talked about debugging formulas using the F9 key. The Age column contains a formula that calculates age from Birthdate, and the Status column uses the IF function to label each person in the list as as either an Adult or Minor.

Let's use the Evaluate feature to see how these formulas work.

You can find Evaluate Formula on the Formulas tab of the ribbon, in the Formula Auditing group.

To use Evaluate Formula, select a formula, and click the button on the ribbon.

When the window opens, you'll see the formula displayed in a text box with an Evaluate button below.

One part of the formula will be underlined -- this is the part currently "under evaluation". In this case, solving the TODAY function is the first step in solving this formula.

When we click Evaluate, the TODAY function is evaluated and returns a date in Excel's serial number format, and the YEARFRAC function is underlined, as the next step in the evaluation process.

On the next click, the YEARFRAC function is evaluated, and the INT function is underlined.

One last click and the formula is solved with a result of 12.

Notice that the Evaluate button changes to Restart at the end of the evaluate process. Click Restart to evaluate the formula again if you like.

Now let's Evaluate the Status formula.

In this case, E5 is solved first to get Age, which is used in the logical test in the IF statement. 12 is less than 20, so FALSE is the result of the logical test.  "Minor" is the final result, since "Minor" is given as the value to use if false.

Let's restart the evaluation, and look at the "Step in" and "Step out" feature.

In this case, because E5 is itself a formula, we can "step in" and evaluate that formula, too. The process is the same as a normal evaluation. When done, click "step out" to continue evaluating of the original formula.

The ability to step in to other formulas is an optional feature. It's available when a formula refers to other formulas or to cell references.

If you step into another formula and decide you don't want to evaluate it, just click "Step out" to continue evaluating the main formula.

So there you have it. Evaluate Formula is a handy compliment to using the the F9 key. And it's a great way to understand how Excel solves formulas.

Author 
Dave Bruns
Many thanks.This site is pinned to my home page and has proven itself very useful. - Glynn
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course