AND function in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to identify which sales transactions meet two conditions: the sale amount is greater than $500 and the customer rating is 4 or higher.

📊 Data: You have a table with sales transactions including columns for Transaction ID, Sale Amount, and Customer Rating.

🎯 Deliverable: Create a new column that uses the AND function to mark TRUE if both conditions are met, otherwise FALSE.

Sample Data

Transaction ID	Sale Amount	Customer Rating
TXN001	450	5
TXN002	600	3
TXN003	700	4
TXN004	300	4
TXN005	800	5
TXN006	550	2
TXN007	400	4
TXN008	900	5

1

Step 1: Add a new column header named 'Meets Conditions' next to the 'Customer Rating' column.

Expected Result

A new column header 'Meets Conditions' appears in the table.

2

Step 2: In the first data row under 'Meets Conditions', enter the formula to check if Sale Amount is greater than 500 AND Customer Rating is 4 or higher.

=AND(B2>500, C2>=4)

Expected Result

Formula entered in cell D2.

3

Step 3: Copy the formula down the 'Meets Conditions' column for all rows.

Drag the fill handle from D2 down to D9.

Expected Result

Each row shows TRUE if both conditions are met, otherwise FALSE.

Final Result

Transaction ID | Sale Amount | Customer Rating | Meets Conditions
-------------------------------------------------------------
TXN001         | 450         | 5               | FALSE
TXN002         | 600         | 3               | FALSE
TXN003         | 700         | 4               | TRUE
TXN004         | 300         | 4               | FALSE
TXN005         | 800         | 5               | TRUE
TXN006         | 550         | 2               | FALSE
TXN007         | 400         | 4               | FALSE
TXN008         | 900         | 5               | TRUE

✓Only transactions TXN003, TXN005, and TXN008 meet both conditions.

✓Sales above $500 with customer rating 4 or higher are marked TRUE.

✓This helps quickly identify high-value, well-rated sales.

Bonus Challenge

Modify the formula to also check that the sale was made in the current year, assuming a 'Sale Date' column exists.

Show Hint

Use the AND function with an additional condition like YEAR(DATE_COLUMN)=YEAR(TODAY()).