Module 3A Excel Nested IF

What is a Nested IF?

An IF function placed inside another IF function to handle more than 2 outcomes.

Why should you use a Nested IF if there’s more than 2 outcomes instead of a regular IF?

A normal IF only gives True/False. Nested IF allows 3+ categories.

When should you use a Nested IF? Ex. Loan decisions, grades, salary levels

When you need multiple conditions that produce different results (3 or more outcomes).

What are the 3 parts of an IF function?

Logical_Test, Value_IF_True, Value_IF_False

In a Nested IF, where do you place the second IF statement?

Inside the “value_if_false” section of the first IF.

Why doesn’t the last category in a Nested IF need another IF statement?

Because if all previous conditions are false, the last result is the only possible answer left.

Should you test the highest or lowest threshold first?

Highest first. Prevents lower conditions from being triggered incorrectly.

Write the Nested IF formula for the credit score example. > 780: credit approval, 700-799 is conditional loan approval, < 699 = denied

=IF(C8>=780,"Loan Approved",IF(C8>=700,"Conditional Loan Approval","Denied"))

What happens when the first condition in a Nested IF is TRUE?

Excel returns that value and stops checking further conditions.

What happens if a condition is written in the wrong order?

Excel may return wrong results even if the formula is typed correctly.

Write the Nested IF formula for the credit score example: ≥ 780 = Loan Approved
≥ 700 = Conditional Loan Approval
Else = Denied

=IF(C8>=780,"Loan Approved",IF(C8>=700,"Conditional Loan Approval","Denied"))

=IF(C8>=780,"Loan Approved",IF(C8>=700,"__________","Denied"))

Conditional Loan Approval

Write a Nested IF to return:
≥ 90 = A
≥ 80 = B
Else = C

=IF(A2>=90,"A",IF(A2>=80,"B","C"))

Create a Nested IF for delivery time:
≤ 2 days = “Fast”
≤ 5 days = “Normal”
Else = “Slow”

=IF(D2<=2,"Fast",IF(D2<=5,"Normal","Slow"))

Make a Nested IF for salary:
≥ 100,000 = “High”
≥ 70,000 = “Medium”
Else = “Low”

=IF(B2>=100000,"High",IF(B2>=70000,"Medium","Low"))

Find the mistake:
=IF(C8>=780,"Loan Approved",IF(C8>=700,"Conditional Loan Approval",Denied))

Missing  " “ around “Denied”.

What’s wrong with this formula? =IF(C8>=700,"Conditional Loan Approval",IF(C8>=780,"Loan Approved","Denied"))

Conditions are reversed; the higher value must be tested first

What Excel feature should you use to apply the Nested IF to multiple rows?

Autofill (drag the cell’s bottom-right corner)

Where can you edit an existing formula?

In the formula bar

True or False: All text outputs must be inside quotation marks in an IF function.

True

In one sentence, explain how Nested IF works.

Excel checks conditions in order; once one is true, it returns that result and stops.

If your Nested IF always returns the last value, what’s likely wrong?

The earlier conditions are incorrect or in the wrong order.

Why should you avoid writing too many Nested IFs if possible?

It becomes hard to read and increases the chance of logic mistakes.

4-Outcome Challenge:
≥ 3.7 = “Excellent”
≥ 3.3 = “Good”
≥ 3.0 = “Average”
Else = “Below Average”

=IF(C2>=3.7,"Excellent",IF(C2>=3.3,"Good",IF(C2>=3.0,"Average","Below Average")))