OR function in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales assistant at a retail store.

📋 Request: Your manager wants to quickly identify which sales transactions qualify for a special discount. The discount applies if the sale amount is greater than $100 or if the customer is a member of the loyalty program.

📊 Data: You have a list of sales transactions with columns for Transaction ID, Sale Amount, and Loyalty Member status (Yes/No).

🎯 Deliverable: Create a new column that shows TRUE if the transaction qualifies for the discount and FALSE if it does not.

Sample Data

Transaction ID	Sale Amount	Loyalty Member
1001	120	Yes
1002	80	No
1003	150	No
1004	90	Yes
1005	50	No
1006	200	Yes
1007	70	No
1008	110	No

1

Step 1: Insert a new column next to the Loyalty Member column and name it 'Discount Eligible'.

Expected Result

A new empty column named 'Discount Eligible' is added.

2

Step 2: In the first cell under 'Discount Eligible' (D2), enter the formula to check if Sale Amount is greater than 100 OR Loyalty Member is 'Yes'.

=OR(B2>100, C2="Yes")

Expected Result

The formula returns TRUE for Transaction 1001 because Sale Amount is 120 and Loyalty Member is Yes.

3

Step 3: Copy the formula from D2 down to all rows in the 'Discount Eligible' column.

Drag the fill handle from D2 down to D9.

Expected Result

Each row shows TRUE if Sale Amount > 100 or Loyalty Member is Yes, otherwise FALSE.

Final Result

Transaction ID | Sale Amount | Loyalty Member | Discount Eligible
-------------------------------------------------------------
1001           | 120         | Yes            | TRUE
1002           | 80          | No             | FALSE
1003           | 150         | No             | TRUE
1004           | 90          | Yes            | TRUE
1005           | 50          | No             | FALSE
1006           | 200         | Yes            | TRUE
1007           | 70          | No             | FALSE
1008           | 110         | No             | TRUE

✓Transactions with Sale Amount over $100 qualify for the discount.

✓Transactions where the customer is a loyalty member also qualify.

✓The OR function helps combine these two conditions easily.

Bonus Challenge

Modify the formula to also qualify transactions for discount if the Sale Amount is exactly $100.

Show Hint

Change the condition B2>100 to B2>=100 inside the OR function.

Practice

1. What does the OR function do in Excel?

easy

A. Returns FALSE if any condition inside it is TRUE

B. Returns TRUE if any condition inside it is TRUE

C. Returns TRUE only if all conditions inside it are TRUE

D. Counts how many conditions are TRUE

Solution

1. Step 1: Understand the OR function purpose

   The OR function checks multiple conditions and returns TRUE if at least one condition is TRUE.

2. Step 2: Compare options with OR behavior

   Only Returns TRUE if any condition inside it is TRUE correctly states that OR returns TRUE if any condition is TRUE.

3. Final Answer:

   Returns TRUE if any condition inside it is TRUE -> Option B

4. Quick Check:

   OR = TRUE if any condition TRUE [OK]

Hint: OR returns TRUE if any condition is TRUE, else FALSE [OK]

Common Mistakes:

• Thinking OR requires all conditions TRUE
• Confusing OR with AND function
• Assuming OR counts TRUE conditions

2. Which of these is the correct syntax for the OR function in Excel?

easy

A. =OR(A1>10, B1<5)

B. =OR(A1>10; B1<5)

C. =OR{A1>10, B1<5}

D. =OR[A1>10, B1<5]

Solution

1. Step 1: Recall Excel function syntax

   Excel functions use parentheses () and commas , to separate arguments.

2. Step 2: Check each option's syntax

   =OR(A1>10, B1<5) uses correct parentheses and commas. Options A, B, and C use incorrect separators or brackets.

3. Final Answer:

   =OR(A1>10, B1<5) -> Option A

4. Quick Check:

   Excel functions use () and commas [OK]

Hint: Use parentheses and commas in Excel functions [OK]

Common Mistakes:

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

3. Given the formula =OR(A1>5, B1=10) with A1=3 and B1=10, what is the result?

medium

A. TRUE

B. FALSE

C. 3

D. 10

Solution

1. Step 1: Evaluate each condition

   A1>5 is 3>5 which is FALSE. B1=10 is 10=10 which is TRUE.

2. Step 2: Apply OR logic

   OR returns TRUE if any condition is TRUE. Since B1=10 is TRUE, OR returns TRUE.

3. Final Answer:

   TRUE -> Option A

4. Quick Check:

   OR(FALSE, TRUE) = TRUE [OK]

Hint: If any condition is TRUE, OR returns TRUE [OK]

Common Mistakes:

• Assuming both conditions must be TRUE
• Confusing OR with AND
• Returning a cell value instead of TRUE/FALSE

4. The formula =OR(A1>5 B1=10) returns an error. What is the fix?

medium

A. Remove parentheses

B. Replace OR with AND

C. Add a comma between conditions: =OR(A1>5, B1=10)

D. Change > to >= in first condition

Solution

1. Step 1: Identify syntax error

   The formula misses a comma between conditions inside OR, causing a syntax error.

2. Step 2: Correct the syntax

   Insert a comma between conditions: =OR(A1>5, B1=10) to fix the error.

3. Final Answer:

   Add a comma between conditions: =OR(A1>5, B1=10) -> Option C

4. Quick Check:

   OR arguments must be comma-separated [OK]

Hint: Separate OR conditions with commas [OK]

Common Mistakes:

• Forgetting commas between conditions
• Replacing OR with AND without reason
• Removing parentheses causing more errors

5. You want to mark a student as 'Pass' if their score in A1 is above 50 or if they have extra credit in B1 (marked TRUE). Which formula correctly does this?

hard

A. =IF(AND(A1>50, B1=TRUE), "Pass", "Fail")

B. =IF(A1>50 OR B1=TRUE, "Pass", "Fail")

C. =IF(OR(A1>50 B1=TRUE), "Pass", "Fail")

D. =IF(OR(A1>50, B1=TRUE), "Pass", "Fail")

Solution

1. Step 1: Understand the condition

   The student passes if score > 50 OR extra credit is TRUE.

2. Step 2: Check formula correctness

   =IF(OR(A1>50, B1=TRUE), "Pass", "Fail") uses IF with OR and correct syntax. =IF(AND(A1>50, B1=TRUE), "Pass", "Fail") uses AND which requires both true, wrong logic. Options C and D have syntax errors (missing commas or wrong structure).

3. Final Answer:

   =IF(OR(A1>50, B1=TRUE), "Pass", "Fail") -> Option D

4. Quick Check:

   Use IF with OR for either condition TRUE [OK]

Hint: Use IF with OR for either condition TRUE [OK]

Common Mistakes:

• Using AND instead of OR
• Missing commas inside OR
• Incorrect IF syntax