Free Shipping and Review Flags with AND and OR
Your task: In E2:E5, show Free or Standard shipping; in F2:F5, show Review or OK for each order.
Full instructions, scenario and goal
Scenario
An online shop applies two rules to every order. Shipping is free for members whose order is at least 60. An order is sent for manual review if its total is over 500 or if its payment is still pending. The shop wants both decisions in the order list so staff do not apply the rules by eye.
Your task
- In E2:E5, show Free when the order total is at least 60 and the customer is a member. Otherwise show Standard.
- In F2:F5, show Review when the order total is over 500 or the payment is Pending. Otherwise show OK.
- Write the formula for the first order, check it, then adapt it for the others.
- Select Check Answer when every order has both results.
You are done when
- Free shipping needs both conditions at once.
- Review is triggered by either condition.
- The boundaries are right: exactly 60 ships free, exactly 500 is not reviewed.
What you will practise
Test two conditions in one IF, and decide whether both must be true (AND) or at least one must be true (OR).
The idea in 30 seconds
IF checks one condition. Real rules often have two: "the order is large and the customer is a member", or "the order is large or the payment is pending". AND returns TRUE only when every condition is true. OR returns TRUE when at least one condition is true. You put AND or OR inside the first argument of IF, and IF then picks the result as usual.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell E2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
IF checks one condition. Real rules often have two: "the order is large and the customer is a member", or "the order is large or the payment is pending". AND returns TRUE only when every condition is true. OR returns TRUE when at least one condition is true. You put AND or OR inside the first argument of IF, and IF then picks the result as usual.
Getting AND and OR the wrong way round is the most common logic slip, and the formula will not show an error. Say the rule out loud: if the words are "and" or "both", use AND; if they are "or" or "either", use OR. Also check the boundaries. "At least 60" includes 60 itself, so the comparison is >=; "over 500" excludes 500, so it is >. Text in a condition goes between double quotes, exactly as it appears in the cell.
Skills you will practise
- IF
- AND
- OR
- Multiple conditions
Topic guide: Excel IF Function Practice
Complete first: Has the Sales Target Been Met?
Common mistakes to avoid
- Using OR where both conditions are needed
- With OR, a small order from a member would ship free. Free shipping needs the member AND a big enough total.
- Using AND where either condition is enough
- With AND, an order is reviewed only when it is both large and pending, so a big paid order slips through.