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"))