Overview Transcript



Nested IF functions, meaning one IF function inside of another, allow you to test multiple criteria and increases the number of possible outcomes.

Want more?

Nest a function within a function

IF function

SUMIFS function

COUNTIFS function

AVERAGEIFS function

IFERROR function

One IF function has one test and two possible outcomes, TRUE or FALSE.

Nested IF functions, meaning one IF function inside of another, allows you to test multiple criteria and increases the number of possible outcomes.

We want to determine a student's grade based on their score.

If Bob's score in B2 is greater than or equal to 90, return an A.

We nest an IF function by setting value_if_false to IF B2 greater than or equal to 80, return B.

We use additional nested IF functions to test for C, D, and F grades.

I am copying the formula.

In this formula, we must test B2 greater than or equal to 90 first, and then, B2 greater than or equal to 80, and so on.

Let me show you why.

If B2 greater than or equal to 90 evaluates to TRUE, the formula returns A, and we are done.

If it is FALSE, B2 greater than or equal to 80 is evaluated.

Since B2 greater than or equal to 90 has already been evaluated to FALSE, greater than or equal to 80 is essentially greater than or equal to 80 and less than 90.

This continues for greater than or equal to 70 and 60. F is the final value_if_false.

Here is another example. We want to determine Commissions for our Sales staff.

But the Commission percentage varies depending on how much they have sold.

If Bob's Sales, B2, are greater than or equal to 10000 (the cursor is next to the cell reference; I am pressing F4 to make it an absolute cell reference), the Commission is 5%. I am pressing F4 again.

If Bob's Sales are greater than or equal to 5000, his Commission is 2%.

Otherwise his Commission is 0.

We then multiply the result by his Sales.

This is another example where the order of the formula matters.

Sales greater than or equal to 10000 is tested first.

If it evaluates to TRUE, it returns value in G2, 5%.

If it evaluates to FALSE, Sales greater than or equal to 5000 is evaluated next.

Since 10000 has already been evaluated as FALSE, Sales greater than or equal to 5000 is essentially greater than or equal to 5000 and less than 10000.

If neither greater than or equal to 10000 nor greater than or equal to 5000 is TRUE, the function returns the value in G4: 0.

Up next, IF with AND and OR functions.