 ### Excel Cram Kit (Exam 2)

AND vs. OR
HLOOKUP
Excel Tables
PivotTables
COUNTIF, SUMIF, AVERAGEIF
SUMPRODUCT
3D Cell References

# Nested IFs

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.

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

However, if that is false, what if we wanted to check if it has not surpasses \$10,000? How can we incorporate this new condition into our current IF statement below?

With a nested IF statement!

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: