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.