IFS function in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to categorize sales performance for each store based on monthly sales numbers.

📊 Data: You have a table with store names and their monthly sales amounts.

🎯 Deliverable: Create a new column that uses the IFS function to assign performance categories: 'Excellent' for sales above 10000, 'Good' for sales between 7000 and 10000, 'Average' for sales between 4000 and 7000, and 'Poor' for sales below 4000.

Sample Data

Store	Monthly Sales
Store A	12000
Store B	8500
Store C	6700
Store D	3900
Store E	15000
Store F	7200
Store G	3000
Store H	9800

1

Step 1: Insert a new column next to 'Monthly Sales' and label it 'Performance Category'.

Expected Result

A new column ready to hold performance categories.

2

Step 2: In the first cell under 'Performance Category' (e.g., C2), enter the IFS formula to categorize sales.

=IFS(B2>10000, "Excellent", B2>7000, "Good", B2>4000, "Average", B2<=4000, "Poor")

Expected Result

For Store A with 12000 sales, the formula returns 'Excellent'.

3

Step 3: Copy the formula down the entire 'Performance Category' column for all stores.

Drag the fill handle from C2 down to C9.

Expected Result

Each store has a performance category assigned based on its sales.

Final Result

Store    Monthly Sales    Performance Category
---------------------------------------------
Store A  12000           Excellent
Store B  8500            Good
Store C  6700            Average
Store D  3900            Poor
Store E  15000           Excellent
Store F  7200            Good
Store G  3000            Poor
Store H  9800            Good

✓Stores with sales above 10000 are marked as Excellent.

✓Stores with sales between 7000 and 10000 are marked as Good.

✓Stores with sales between 4000 and 7000 are marked as Average.

✓Stores with sales below 4000 are marked as Poor.

Bonus Challenge

Add a new category 'Outstanding' for sales above 14000 and update the IFS formula accordingly.

Show Hint

Add the condition B2>14000, "Outstanding" as the first condition in the IFS formula.

Practice

1. What does the IFS function do in Google Sheets?

easy

A. Sorts data alphabetically.

B. Checks multiple conditions and returns the value for the first true condition.

C. Adds numbers in a range.

D. Counts the number of cells with numbers.

Solution

1. Step 1: Understand the purpose of IFS

   The IFS function evaluates multiple conditions one by one.

2. Step 2: Identify what IFS returns

   It returns the value corresponding to the first condition that is true.

3. Final Answer:

   Checks multiple conditions and returns the value for the first true condition. -> Option B

4. Quick Check:

   IFS returns first true condition's value [OK]

Hint: Remember: IFS stops at first true condition [OK]

Common Mistakes:

• Thinking IFS sums values
• Confusing IFS with sorting or counting
• Assuming IFS returns all true values

2. Which of the following is the correct syntax for the IFS function in Google Sheets?

easy

A. IFS(condition1: value1, condition2: value2)

B. IFS(value1, condition1, value2, condition2)

C. IFS(condition1; value1; condition2; value2)

D. IFS(condition1, value1, condition2, value2, ..., TRUE, default_value)

Solution

1. Step 1: Recall IFS syntax format

   IFS requires pairs of conditions and values separated by commas.

2. Step 2: Identify correct option

   IFS(condition1, value1, condition2, value2, ..., TRUE, default_value) correctly shows pairs and includes a default TRUE condition.

3. Final Answer:

   IFS(condition1, value1, condition2, value2, ..., TRUE, default_value) -> Option D

4. Quick Check:

   IFS syntax uses condition-value pairs separated by commas [OK]

Hint: Pairs of condition and value, separated by commas [OK]

Common Mistakes:

• Using semicolons instead of commas
• Swapping condition and value order
• Using colons instead of commas

3. Given the formula =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F") and cell A1 contains 85, what will be the result?

medium

A. "B"

B. "F"

C. "C"

D. "A"

Solution

1. Step 1: Evaluate conditions in order

   Check if A1>90: 85>90 is false, move to next.

2. Step 2: Check second condition

   Check if A1>80: 85>80 is true, so return "B".

3. Final Answer:

   "B" -> Option A

4. Quick Check:

   First true condition returns "B" [OK]

Hint: Check conditions top to bottom, stop at first true [OK]

Common Mistakes:

• Choosing the highest grade instead of first true
• Ignoring order of conditions
• Forgetting TRUE default case

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

medium

A. Conditions are reversed; should be A1<50 first.

B. Quotes around text values are incorrect.

C. Missing default TRUE condition for unmatched cases.

D. IFS cannot compare numbers.

Solution

1. Step 1: Analyze conditions coverage

   Conditions check if A1>50 or A1<50 but miss A1=50 case.

2. Step 2: Identify missing default case

   Without a TRUE default, formula returns #N/A if no condition matches.

3. Final Answer:

   Missing default TRUE condition for unmatched cases. -> Option C

4. Quick Check:

   Always add TRUE default to handle all cases [OK]

Hint: Add TRUE default condition to avoid errors [OK]

Common Mistakes:

• Not including a TRUE default condition
• Assuming all cases covered without equality
• Misunderstanding error messages

5. You want to assign a category based on sales in cell B2: "High" if sales > 1000, "Medium" if sales between 500 and 1000, "Low" if sales between 100 and 499, and "None" if sales below 100. Which IFS formula is correct?

hard

A. =IFS(B2>1000, "High", B2>=500, "Medium", B2>=100, "Low", TRUE, "None")

B. =IFS(B2>1000, "High", B2>500, "Medium", B2>100, "Low", TRUE, "None")

C. =IFS(B2>=1000, "High", B2>=500, "Medium", B2>=100, "Low", TRUE, "None")

D. =IFS(B2>1000, "High", B2>499, "Medium", B2>99, "Low", TRUE, "None")

Solution

1. Step 1: Check condition ranges carefully

   High is sales > 1000, Medium is sales between 500 and 1000 inclusive, Low is 100 to 499 inclusive.

2. Step 2: Verify conditions order and boundaries

   =IFS(B2>1000, "High", B2>=500, "Medium", B2>=100, "Low", TRUE, "None") correctly uses >1000, then >=500, then >=100, and finally TRUE for below 100.

3. Final Answer:

   =IFS(B2>1000, "High", B2>=500, "Medium", B2>=100, "Low", TRUE, "None") -> Option A

4. Quick Check:

   Use >= for inclusive ranges and TRUE for default [OK]

Hint: Use >= for lower bounds and TRUE for default catch-all [OK]

Common Mistakes:

• Using > instead of >= causing gaps
• Wrong order of conditions
• Missing TRUE default condition