Why logical functions enable decision-making in Excel - 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 bonus based on specific criteria.

📊 Data: You have a table of sales transactions including columns for Salesperson, Region, Sales Amount, and Number of Items Sold.

🎯 Deliverable: Create a new column that uses logical functions to mark transactions as 'Bonus' or 'No Bonus' based on the criteria: sales amount greater than $500 and items sold more than 5.

Sample Data

Salesperson	Region	Sales Amount	Items Sold
Alice	East	600	7
Bob	West	450	10
Charlie	East	700	4
Diana	North	550	6
Eva	South	300	8
Frank	West	800	9
Grace	North	400	3
Hank	South	650	7

1

Step 1: Add a new column header named 'Bonus Status' next to 'Items Sold'.

Expected Result

A new column is ready for formulas.

2

Step 2: In the first cell under 'Bonus Status' (e.g., E2), enter a formula to check if Sales Amount is greater than 500 AND Items Sold is greater than 5.

=IF(AND(C2>500,D2>5),"Bonus","No Bonus")

Expected Result

For Alice's row, the formula returns 'Bonus' because 600 > 500 and 7 > 5.

3

Step 3: Copy the formula down the 'Bonus Status' column for all rows.

Drag the fill handle from E2 down to E9.

Expected Result

Each row shows 'Bonus' or 'No Bonus' based on the criteria.

4

Step 4: Review the results to identify which sales transactions qualify for the bonus.

Expected Result

Rows for Alice, Diana, Frank, and Hank show 'Bonus'; others show 'No Bonus'.

Final Result

Salesperson | Region | Sales Amount | Items Sold | Bonus Status
-------------------------------------------------------------
Alice       | East   | 600          | 7          | Bonus
Bob         | West   | 450          | 10         | No Bonus
Charlie     | East   | 700          | 4          | No Bonus
Diana       | North  | 550          | 6          | Bonus
Eva         | South  | 300          | 8          | No Bonus
Frank       | West   | 800          | 9          | Bonus
Grace       | North  | 400          | 3          | No Bonus
Hank        | South  | 650          | 7          | Bonus

✓Logical functions like IF and AND help decide which sales qualify for bonuses.

✓Only sales with amount over $500 and more than 5 items sold get a bonus.

✓This method quickly highlights qualifying transactions for decision-making.

Bonus Challenge

Modify the formula to also give a 'Super Bonus' if the sales amount is over $700 and items sold are more than 8.

Show Hint

Use nested IF functions or the IFS function to check multiple conditions.