Skip to content
GridBelt
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign in
Start the free test
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign inCreate account
Start the free test
GridBelt

Practice Excel in your browser.

Excel Test

  • Level 1: Beginner
  • Level 2: Intermediate
  • Level 3: Expert
  • Excel test in 7 days
  • Leaderboard
  • Excel test for interview
  • Indeed Excel assessment

Practice

  • All exercises
  • Programs
  • For beginners

Guides

  • Excel formulas
  • Excel shortcuts

Company

  • About
  • Pricing
  • How we grade

© GridBelt. All rights reserved.

  • Privacy Policy
  • Terms of Use
  • Refund Policy

Excel is a trademark of Microsoft Corporation. Indeed, TestGorilla, SHL and other marks are the property of their owners. GridBelt is an independent practice site and is not affiliated with, endorsed by or sponsored by Microsoft, TestGorilla, SHL or Indeed.

  1. Practice
  2. /
  3. IF function

IF and OR practice: free-shipping flag for 80 orders

  • Beginner
  • ~6 min
  • IF function

An online store exports its October orders — 80 rows with the customer type (Member or Guest) and the order total. Shipping is free when the order total reaches the threshold stored in B1 ($75) or when the customer is a loyalty Member, whatever the basket size. Marketing changes the threshold for promotions, so the formula must read it from B1 rather than repeat the number.

What you need to do

  1. 1Click F3. The order total is in E3, the customer type in D3 and the threshold in B1.
  2. 2Build the test with OR: OR(E3>=B1,D3="Member") is TRUE when either comparison is true. Before pressing Enter, put the cursor on B1 in the formula and press F4 (Windows) or ⌘T (Mac) so it becomes $B$1 and stays fixed when you fill down.
  3. 3Wrap the test in IF with "Free" and "Standard" as the two results, press Enter, then fill F3 down to F82 (double-click the fill handle, or select F3:F82 and press Ctrl+D / ⌘D).
  4. 4Click F82 and read the formula bar: it must still say $B$1. Change B1 to 100 and watch the flags move, then put it back and click Check (Ctrl+Enter / ⌘+Return).

Answer cells

Feedback

Type your formula in the grid, press Enter, then Check. We grade the value in each answer cell and the formula behind it.

Tasks

  1. Not checkedIn F3, use IF and OR to show "Free" when the order total in E3 is at least the threshold in B1 or the customer type in D3 is "Member", otherwise "Standard"; lock B1 as $B$1 and fill the formula down to F82.

Desktop recommended for spreadsheet tasks. The sheet scrolls inside this box.

StartInsertFormulasDataView
A1fxPreparing your workbook…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Enter your formula in the sheet, then check.

About this exercise

A 81-row dataset on one sheet, one answer cell, about 6 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.

How to solve it

  • F3=IF(OR(E3>=$B$1,D3="Member"),"Free","Standard")

OR is the mirror image of AND: OR(E3>=$B$1,D3="Member") is TRUE when at least one of the two comparisons holds, and FALSE only when both fail. IF turns that into the Free / Standard label. The dollar signs in $B$1 matter because the formula is copied down: the row numbers in E3 and D3 should follow each order, but the threshold must keep pointing at B1 on every row.

Pitfall 1: =IF(OR(E3>=B1,…) without the $ signs looks right on row 3 and breaks from row 4 on, because B1 slides to B2 (a text caption) and then to empty cells — every comparison against them is TRUE or FALSE for the wrong reason. Pitfall 2: hard-coding the threshold (E3>=75) gives correct flags today and silently wrong ones after the next promotion; the grader changes B1 and expects the flags to follow. Pitfall 3: text comparisons are case-insensitive but not space-insensitive — "Member " with a trailing space from an export is not "Member"; TRIM the column first when the data is dirty. Alternatives: =IF((E3>=$B$1)+(D3="Member")>0,…) adds the booleans (any 1 means at least one TRUE); a lookup table of customer types with their own thresholds scales better when there are more than two tiers. Reading the result: COUNTIF(F3:F82,"Free") on top of this column is the first question marketing will ask — that is the next topic in this track.

Other ways that work

  • =IF(OR(D3="Member",E3>=$B$1),"Free","Standard") — same conditions in the other order
  • =IF(AND(E3<$B$1,D3<>"Member"),"Standard","Free") — the rule expressed through its only failing case
  • =IF(OR(E3>=B$1,D3="Member"),"Free","Standard") — a mixed reference (B$1) is enough when you only fill down

Common mistakes

Leaving B1 relative: after the fill, F4 compares against B2 and F5 against B3, so most rows read Standard. Lock the threshold as $B$1 (F4 on Windows, ⌘T on Mac) before filling down.

How we grade

Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.

Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.

In these programs

  • Excel for Administrative AssistantsDay 3
  • Excel for Sales and MarketingDay 2
  • Learn Excel From ScratchDay 5

Related exercises

  • Pass or Fail with IFBeginner, ~4 min
  • Bonus eligibility with IF and ANDBeginner, ~5 min
  • Total of orders above $500Beginner, ~4 min

Function reference

Official documentation

Next exercise

Flag rows with missing data