Nested IF vs. IFS | CSE 148 – Excel Exam 2

Still feeling nervous for your exam? Check out the Excel Cram Kit (Exam 2) for practice modules that emulate the difficulty that you'll face on your exam. Each module includes a thorough step-by-step explanation where I walk you through the entire problem solving process!

Nested IF & IFS explained

If you recall in the IF | Excel in 30 Minutes (Test #1) article, we created a spreadsheet for Michael Scott's C.R.I.M.E.A.I.D. (Crime Reduces Innocence Makes Everyone Angry I Declare) fundraising event.

(I do not own this image. Rights to The Office and YouTube for this image. Educational purposes only.)

With our spreadsheet, we created the following IF statement...

=IF(SUM(D:D) >= 1000000, "YES", "NO") determine whether or not the goal of $1,000,000 was reached for the fundraiser. We then displayed this determination in cell F2.

Challenge question: What if the only potential outcome of the fundraiser was not just whether or not $1,000,000 was reached?

In other words... what if there were multiple conditions that could result in differing outcomes of the fundraiser?

This is where nested IF statements and IFS come into play!

Nested IF statements and IFS enable us to check if more than one condition is true, and returns the designated value for the first true condition.

To exemplify how we could incorporate multiple conditions into Michael's fundraiser, let's say that if the fundraiser does not surpass $10,000, then Michael Scott will be forced to sell his very own (and privately held) Threat Level Midnight movie to the local theatre.

Coding a nested IF

How can we incorporate this new condition into our current IF statement below?

=IF(SUM(D:D) >= 1000000, "YES", "NO")

With a nested IF statement!

We are currently just checking if the SUM is greater than or equal to $1,000,000. If that is true, then we want to display "YES" in the output. However, if that is false, we now need to check if it has not surpasses $10,000.

To do so, let's place the IF statement template into the spot where the IF statement currently has "NO".

=IF(SUM(D:D) >= 1000000, "YES",IF(logical_test, [value_if_true], [value_if_false]))

Notice how there is now an IF statement inside of an IF statement...

A nested IF statement is nested because there's an IF inside of an IF!

Within this nested IF statement, our logical_test is going to be assessing if the sum of all donations does not exceed $10,000. In other words, is the sum of all donations less than or equal to $10,000?

=IF(SUM(D:D) >= 1000000, "YES",IF(SUM(D:D) <= 10000, [value_if_true], [value_if_false]))

If that is the case (that all donations don't exceed $10,000), then we need to display "THREAT LEVEL MIDNIGHT" to the user within [value_if_true].

=IF(SUM(D:D) >= 1000000, "YES",IF(SUM(D:D) <= 10000, "THREAT LEVEL MIDNIGHT", [value_if_false]))

For [value_if_false], we need to consider what needs to happen if the fundraiser does not reach the $1,000,000 goal, but does exceed $10,000? In this case, we just need to display "NO".

=IF(SUM(D:D) >= 1000000, "YES",IF(SUM(D:D) <= 10000, "THREAT LEVEL MIDNIGHT", "NO"))

Let's first consider what happens if the fundraiser reaches $1,000,000. In that case, we want to display "YES" because the fundraiser has reached its goal.

The next thing to consider is if the fundraiser doesn't even surpass $10,000. If this happens, Michael Scott has decided to take things into his own hands and release his very own Threat Level Midnight movie to the local theatre to bring in more funds.

If, however, the fundraiser is somewhere between $10,000 and $1,000,000, then the fundraiser did not reach it's goal, yet Michael Scott will feel good enough to not need to release Threat Level Midnight to obtain more funding. That is why we just need to place "NO" in the [value_if_false].

With our donations looking like so...

...when we plug this formula into F2, we get the following result:

Coding an IFS

We can use an IFS function to accomplish what we did before, but in a cleaner method.

IFS are essentially nested IF statements, but easier to read.

Here is the template for an IFS function:

=IFS(logical_test1, value1, [logical_test2, value2], [logical_test3, value3])

So, how can we write what we had above in this IFS function?

Let's start with our first logical_test1 and place our first condition in our nested IF above.

If the sum of all donations exceeds $1,000,000...

=IFS(SUM(D:D) >= 1000000, value1, [logical_test2, value2], [logical_test3, value3])

...then we want to display "YES".

=IFS(SUM(D:D) >= 1000000, "YES", [logical_test2, value2], [logical_test3, value3])

Now for our next logical_test2. In this case, let's figure out whether or not Michael will need to release Threat Level Midnight.

If the sum of all donations does not exceed $10,000...

=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, value2, [logical_test3, value3])

...then we need to display "THREAT LEVEL MIDNIGHT".

=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, "THREAT LEVEL MIDNIGHT", [logical_test3, value3])

Lastly, if none of these conditions are true... a.k.a. the sum of all donations lies somewhere between $10,000 and $1,000,000...

=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, "THREAT LEVEL MIDNIGHT", AND(SUM(D:D) > 10000, SUM(D:D) < 1000000)), value3)

...then we need to display "NO" to the user.

Check out our AND vs. OR article here!

=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, "THREAT LEVEL MIDNIGHT", AND(SUM(D:D) > 10000, SUM(D:D) < 1000000), "NO")

When we plug this into F2, we get the same result as before!

It is important to note what's going on behind the scenes here...

First, we're checking if the sum exceeds $1,000,000, and if it does we're showing "YES".

=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, "THREAT LEVEL MIDNIGHT", AND(SUM(D:D) > 10000, SUM(D:D) < 1000000)), "NO")

Then, we're checking if the sum is less than $10,000, and if it is we're displaying "THREAT LEVEL MIDNIGHT".

=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, "THREAT LEVEL MIDNIGHT", AND(SUM(D:D) > 10000, SUM(D:D) < 1000000)), "NO")

Lastly, we're checking if the SUM if between $10,000 and $1,000,000, and if it is we're displaying "NO".

=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, "THREAT LEVEL MIDNIGHT", AND(SUM(D:D) > 10000, SUM(D:D) < 1000000)), "NO")

IFS check for each condition in order, and returns the value of the first true condition it finds.

Practice problem

Imagine that you run a basketball team and want to create incentives based on their play. You have the following conditions going into your upcoming game (and their designated outputs), in order of importance:

  • If John scores more than 12 points, everyone gets ice cream >>> "ICE CREAM"
  • If Steve and Mary combine for at least 8 points, the team won't practice Friday >>> "NO PRACTICE"
  • If Sarah scores above 25 points, she gets a medal >>> "MEDAL"

Considering the following stat line for the game, write an IFS function to determine what reward (if any) the team will receive.

=IFS(C2 > 12, "ICE CREAM", SUM(C5, C6) >= 8, "NO PRACTICE", C4 > 25, "MEDAL")

This IFS function returns "NO PRACTICE", because that was the first condition to return true!

Excel (Exam 2) Follow-along Guide

It's no secret you retain info better when you write it down. That's why I created the Excel (Exam 2) Follow-along Guide for you!

As you come upon key concepts highlighted in yellow, like this sentence here, you can fill-in-the-blanks on your Follow-along Guide so that you remember all the important stuff for later!

You can obtain the Excel (Exam 2) Follow-along Guide for FREE by entering your email below!

Next let's learn how to utilize all of the features of Excel tables!

Follow-along Guide

Fill-in-the-blanks for highlighted, key points in the concepts below!

Free Resources

Each exam concept broken down in simple, real-world examples!

ToolsExcel (Exam 2) Follow-along Guide
LessonAND vs. OR
LessonNested IF vs. IFS
LessonExcel tables
Lesson3D Cell References

Practice Problems

Step-by-step walkthrough for each of the questions you need to be ready for!

"I got this review for my 2nd CSE exam after not doing so well on the first one, and it was so helpful! Will definitely be using again in the future."

Erin Graham
Oct. 26, 2021

Rating: 5 out of 5.

Leave a Comment