Why logical functions handle conditions in Google Sheets - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants you to identify which sales transactions qualify for a special discount based on certain conditions.

📊 Data: You have a list of sales transactions with columns for Transaction ID, Sales Amount, and Customer Type.

🎯 Deliverable: Create a new column that uses logical functions to mark transactions as 'Discount' or 'No Discount' based on the conditions.

Sample Data

Transaction ID	Sales Amount	Customer Type
1001	120	Regular
1002	250	VIP
1003	80	Regular
1004	300	VIP
1005	150	Regular
1006	90	VIP
1007	200	Regular
1008	400	VIP

1

Step 1: Add a new column header named 'Discount Status' next to the 'Customer Type' column.

Expected Result

A new column header 'Discount Status' appears in the sheet.

2

Step 2: In the first cell under 'Discount Status' (e.g., D2), enter a formula that checks if the Sales Amount is greater than or equal to 200 OR the Customer Type is 'VIP'. If either condition is true, mark 'Discount', otherwise 'No Discount'.

=IF(OR(B2>=200, C2="VIP"), "Discount", "No Discount")

Expected Result

For Transaction ID 1001, the result is 'No Discount' because 120 < 200 and Customer Type is 'Regular'.

3

Step 3: Copy the formula down the 'Discount Status' column for all transactions.

Drag the fill handle from D2 down to D9.

Expected Result

Each transaction row shows 'Discount' or 'No Discount' based on the conditions.

4

Step 4: Verify that transactions with Sales Amount >= 200 or Customer Type 'VIP' are marked as 'Discount'.

Expected Result

Transactions 1002, 1004, 1006, 1007, and 1008 show 'Discount'; others show 'No Discount'.

Final Result

Transaction ID | Sales Amount | Customer Type | Discount Status
-------------------------------------------------------------
1001           | 120          | Regular       | No Discount
1002           | 250          | VIP           | Discount
1003           | 80           | Regular       | No Discount
1004           | 300          | VIP           | Discount
1005           | 150          | Regular       | No Discount
1006           | 90           | VIP           | Discount
1007           | 200          | Regular       | Discount
1008           | 400          | VIP           | Discount

✓Logical functions like IF and OR help check multiple conditions easily.

✓Transactions with high sales or VIP customers get discounts.

✓This method saves time by automatically marking discounts.

Bonus Challenge

Modify the formula to give a 'Super Discount' if the Sales Amount is over 300 and the Customer Type is 'VIP', otherwise use the previous discount logic.

Show Hint

Use nested IF with AND and OR functions: =IF(AND(B2>300, C2="VIP"), "Super Discount", IF(OR(B2>=200, C2="VIP"), "Discount", "No Discount"))

Practice

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

easy

A. It checks a condition and returns one value if true, another if false.

B. It adds numbers in a range.

C. It sorts data alphabetically.

D. It counts the number of cells with numbers.

Solution

1. Step 1: Understand the purpose of IF

   The IF function tests a condition and chooses between two results based on true or false.

2. Step 2: Compare options with IF's behavior

   Only It checks a condition and returns one value if true, another if false. describes this behavior correctly; others describe different functions.

3. Final Answer:

   It checks a condition and returns one value if true, another if false. -> Option A

4. Quick Check:

   IF function = condition check [OK]

Hint: IF means choose between two results based on true or false [OK]

Common Mistakes:

• Confusing IF with SUM or COUNT functions
• Thinking IF sorts data
• Assuming IF counts cells

2. Which of these is the correct syntax for the IF function in Google Sheets?

easy

A. =IF{condition, value_if_true, value_if_false}

B. =IF(condition, value_if_true, value_if_false)

C. =IF(condition; value_if_true; value_if_false)

D. =IF[condition, value_if_true, value_if_false]

Solution

1. Step 1: Recall Google Sheets formula syntax

   Google Sheets uses commas to separate arguments in functions.

2. Step 2: Identify correct IF syntax

   =IF(condition, value_if_true, value_if_false) uses commas and parentheses correctly; others use wrong separators or brackets.

3. Final Answer:

   =IF(condition, value_if_true, value_if_false) -> Option B

4. Quick Check:

   Correct IF syntax uses commas and parentheses [OK]

Hint: Use commas inside parentheses for IF arguments [OK]

Common Mistakes:

• Using semicolons instead of commas
• Using curly braces or square brackets
• Missing parentheses

3. Given the formula =IF(A1>10, "High", "Low") and cell A1 contains 8, what will the formula return?

medium

A. TRUE

B. "High"

C. 8

D. "Low"

Solution

1. Step 1: Evaluate the condition A1>10

   Since A1 is 8, 8>10 is FALSE.

2. Step 2: Determine IF result based on condition

   Because condition is FALSE, IF returns the value_if_false, which is "Low".

3. Final Answer:

   "Low" -> Option D

4. Quick Check:

   8>10 is false, so result = "Low" [OK]

Hint: Check condition first, then pick true or false result [OK]

Common Mistakes:

• Assuming condition is true without checking
• Returning the cell value instead of IF result
• Confusing TRUE/FALSE with text results

4. The formula =IF(A1=5, "Yes", "No" is entered in a cell. What is wrong with this formula?

medium

A. Text values should not be in quotes.

B. Using = instead of == for comparison.

C. Missing closing parenthesis at the end.

D. IF function cannot compare numbers.

Solution

1. Step 1: Check formula syntax

   The formula has an opening parenthesis after IF but no closing parenthesis at the end.

2. Step 2: Verify other parts

   Using = for comparison is correct in Sheets, text in quotes is correct, and IF can compare numbers.

3. Final Answer:

   Missing closing parenthesis at the end. -> Option C

4. Quick Check:

   All functions need matching parentheses [OK]

Hint: Count parentheses to avoid missing one [OK]

Common Mistakes:

• Using double equals (==) like in programming
• Not putting text in quotes
• Thinking IF can't compare numbers

5. You want to create a formula that returns "Pass" if a student's score in cell B2 is 50 or more, and "Fail" otherwise. Which formula correctly uses logical functions to handle this condition?

hard

A. =IF(B2>=50, "Pass", "Fail")

B. =IF(B2>50, "Pass", "Fail")

C. =IF(B2=50, "Pass", "Fail")

D. =IF(B2<50, "Pass", "Fail")

Solution

1. Step 1: Understand the condition for passing

   Passing means score is 50 or more, so condition is B2 >= 50.

2. Step 2: Check each formula against the condition

   =IF(B2>=50, "Pass", "Fail") uses B2 >= 50 correctly. =IF(B2>50, "Pass", "Fail") excludes 50, =IF(B2=50, "Pass", "Fail") only checks exactly 50, =IF(B2<50, "Pass", "Fail") reverses logic.

3. Final Answer:

   =IF(B2>=50, "Pass", "Fail") -> Option A

4. Quick Check:

   Use >= for 50 or more condition [OK]

Hint: Use >= to include the boundary value [OK]

Common Mistakes:

• Using > instead of >= and missing exact 50
• Checking only equality (=50)
• Reversing true/false results