Nested IF functions in Google Sheets - Step-by-Step Guide

Introduction

Nested IF functions help you check multiple conditions one after another in a single formula. This is useful when you want to give different results based on several rules, like grading scores or categorizing data.

When you want to assign letter grades based on numeric scores, like A, B, C, etc.

When you need to categorize sales amounts into ranges like low, medium, and high.

When you want to check if a date falls into different time periods and return labels.

When you want to apply different discounts based on purchase quantity tiers.

When you want to display different messages based on multiple conditions in a survey.

Steps

Step 1: Click

- the cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the formula bar

You start entering the formula

💡 Begin with =IF(condition1, value_if_true1, IF(condition2, value_if_true2, value_if_false2))

Step 3: Enter

- the full nested IF formula, for example: =IF(A1>90, "A", IF(A1>80, "B", IF(A1>70, "C", "F")))

The formula checks the first condition; if false, it checks the next, and so on

Step 4: Press

- Enter key

The cell shows the result based on the first true condition or the last value if none are true

Step 5: Copy

- the cell with the nested IF formula

You can paste it to other cells to apply the same logic to other data

Before vs After

Before

Cell A1 has a score of 85, and cell B1 is empty

After

Cell B1 shows "B" after entering =IF(A1>90, "A", IF(A1>80, "B", IF(A1>70, "C", "F")))

Settings Reference

Formula input

📍 Formula bar at the top of the sheet

Where you type your nested IF formula

Default: Empty

Cell format

📍 Format menu > Number

Controls how the result of the formula is displayed

Default: Automatic

Common Mistakes

Forgetting to close all parentheses in the nested IF formula

Google Sheets shows an error because the formula is incomplete

Count and match each opening parenthesis '(' with a closing one ')'

Using incorrect logical conditions like =IF(A1>90, "A", IF(A1>80, "B", "C")) without covering all cases

Some values might not fit any condition and give unexpected results

Make sure the last value_if_false covers all remaining cases

Typing text values without quotes inside the formula

Google Sheets treats unquoted text as invalid and shows an error

Always put text results inside double quotes, like "A" or "Low"

Summary

Nested IF functions let you test multiple conditions in one formula.

Each IF checks a condition and moves to the next if false.

Remember to close all parentheses and use quotes for text results.

Practice

1. What does a nested IF function do in Google Sheets?

easy

A. It creates charts automatically.

B. It adds numbers in a list.

C. It checks multiple conditions one after another.

D. It sorts data alphabetically.

Solution

1. Step 1: Understand the purpose of IF function

   The IF function checks one condition and returns a value if true or another if false.

2. Step 2: Understand nested IF

   Nested IF means putting one IF inside another to check multiple conditions step by step.

3. Final Answer:

   It checks multiple conditions one after another. -> Option C

4. Quick Check:

   Nested IF = multiple condition checks [OK]

Hint: Nested IF checks conditions stepwise, like a decision tree [OK]

Common Mistakes:

• Thinking nested IF adds or sorts data
• Confusing IF with other functions like SUM or SORT
• Believing nested IF creates charts

2. Which of the following is the correct syntax for a nested IF in Google Sheets to check if A1 is greater than 10, then greater than 5, else "Low"?

easy

A. =IF(A1>10, IF(A1>5, "Medium", "Low"), "High")

B. =IF(A1>10, "High", IF(A1>5, "Medium", "Low"))

C. =IF(A1>10, "High", IF(A1<5, "Medium", "Low"))

D. =IF(A1>10, "High", IF(A1>5, "Low", "Medium"))

Solution

1. Step 1: Check the first condition

   The formula first checks if A1 is greater than 10, returning "High" if true.

2. Step 2: Check the nested condition

   If the first is false, it checks if A1 is greater than 5, returning "Medium" if true, else "Low".

3. Final Answer:

   =IF(A1>10, "High", IF(A1>5, "Medium", "Low")) -> Option B

4. Quick Check:

   Correct nested IF syntax = =IF(A1>10, "High", IF(A1>5, "Medium", "Low")) [OK]

Hint: Put second IF inside the false part of first IF [OK]

Common Mistakes:

• Swapping true and false parts
• Using wrong comparison operators
• Misplacing parentheses

3. Given the formula =IF(B2>90, "A", IF(B2>75, "B", IF(B2>60, "C", "F"))) and B2 = 80, what will be the output?

medium

A. "B"

B. "C"

C. "A"

D. "F"

Solution

1. Step 1: Check if B2 > 90

   Since B2 is 80, 80 > 90 is false, so move to next IF.

2. Step 2: Check if B2 > 75

   80 > 75 is true, so the formula returns "B".

3. Final Answer:

   "B" -> Option A

4. Quick Check:

   80 > 75 = "B" [OK]

Hint: Check conditions top-down; first true stops checking [OK]

Common Mistakes:

• Choosing "A" because 80 is close to 90
• Ignoring order of conditions
• Confusing output labels

4. Identify the error in this nested IF formula: =IF(A1>50, "Pass", IF(A1>30, "Retake", IF(A1<30, "Fail")))

medium

A. Wrong use of quotes around text

B. Incorrect comparison operators

C. Too many IF functions nested

D. Missing value if all conditions are false

Solution

1. Step 1: Analyze the nested IF structure

   The last IF checks if A1<30 and returns "Fail" if true, but no value is given if false.

2. Step 2: Identify missing else value

   If none of the conditions are true (for example, A1=30), the formula returns FALSE because the last IF lacks a false value.

3. Final Answer:

   Missing value if all conditions are false -> Option D

4. Quick Check:

   Last IF missing false part = Missing value if all conditions are false [OK]

Hint: Always provide value for false in last IF [OK]

Common Mistakes:

• Leaving out false value in last IF
• Assuming IF auto-returns something
• Misreading comparison logic

5. You want to assign a label in cell C2 based on score in B2: "Excellent" if >90, "Good" if >75, "Average" if >50, "Poor" otherwise. Which formula correctly uses nested IF to do this?

hard

A. =IF(B2>90, "Excellent", IF(B2>75, "Good", IF(B2>50, "Average", "Poor")))

B. =IF(B2>50, "Average", IF(B2>75, "Good", IF(B2>90, "Excellent", "Poor")))

C. =IF(B2>90, "Excellent", IF(B2>50, "Average", IF(B2>75, "Good", "Poor")))

D. =IF(B2>90, "Excellent", IF(B2>75, "Good", IF(B2<50, "Poor", "Average")))

Solution

1. Step 1: Order conditions from highest to lowest

   Check if B2 > 90 first, then > 75, then > 50, else "Poor".

2. Step 2: Verify nested IF matches order

   =IF(B2>90, "Excellent", IF(B2>75, "Good", IF(B2>50, "Average", "Poor"))) correctly nests IFs in descending order of score thresholds with correct labels.

3. Final Answer:

   =IF(B2>90, "Excellent", IF(B2>75, "Good", IF(B2>50, "Average", "Poor"))) -> Option A

4. Quick Check:

   Descending order nested IF = =IF(B2>90, "Excellent", IF(B2>75, "Good", IF(B2>50, "Average", "Poor"))) [OK]

Hint: Check highest condition first, nest lower next [OK]

Common Mistakes:

• Ordering conditions incorrectly
• Mixing up labels with wrong conditions
• Using < instead of > in conditions