This is one of the coolest tricks I have seen in Excel, as there are countless times wherein I had a hard time understand formulas. Especially long and complex ones!

Excel provides the way to evaluate your formula, and break it down step by step so that you can understand it!

Let us take the formulas I’ve created below in the IS THE VALUE IN BETWEEN column . We will see how this formula is resolved in a series of steps:

evaluate-formulas-01

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: You can see our formula uses both the If formula and the Median formula. 

The goal of this formula is to evaluate if a value (VALUE TO BE EVALUATED) is in between the range (START OF RANGE to VALUE TO BE EVALUATED)

For example: Is 50 the median of the range 20; 60; 50?

=IF(C7=MEDIAN(A7:C7), “Yes”, “No”)

To start understanding our formula, highlight the formula, then go to Formulas > Evaluate Formula:

evaluate-formulas-02

 

STEP 2: Our formula is now shown on screen, and the part that is underlined is the one to be evaluated first.  Click Evaluate.

evaluate-formulas-03

 

STEP 3: C7 has been evaluated to 50.  Click Evaluate.

evaluate-formulas-04

 

STEP 4:  The median of the values from A7 to C7 (20, 60, 50) is evaluated as 50.  Click Evaluate.

evaluate-formulas-05

 

STEP 5:  Is 50 equal to 50?

Excel has evaluated it to TRUE.  Click Evaluate.

evaluate-formulas-06

 

STEP 6:  Since the If formula received a TRUE, Excel evaluated it as a Yes end result. We have seen how the formula gave us the result in a few easy steps!

evaluate-formulas-07

evaluate-formulas

HELPFUL RESOURCE:

oztraining_728x90

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

RANDBETWEEN Function I use the RANDBETWEEN function all the time whenever I need to create a sample data set. The cool thing about the RANDBETWEEN function is that if you don't like the numbers that it has given you, you can press F9 in a cell and it will give you new numbers.  Try it out by downl...
SUMIF Function: One Criteria & Sum Range The SUMIF function can also be used to apply the criteria to one range and sum the corresponding values in a different range. So if you have a list of Sales Reps in one list and their Sales in another list and want to Sum the sales of only one of the Sales Reps, then the SUMIF...
Named Ranges with Vlookup Formula What does it do? Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, ) What it means: =VLOOKUP...
IF Function Combined With The AND Function When combining (or nesting) the AND function with the IF function, it allows you to add more than one condition to your formula, something that is not possible with the IF function by itself. So you can show the results of Sales Reps that have made more than $3,000 of sales AN...