functions

MODE returns the value that occurs most often, which is exactly "most common cohort size" — it's a statistical function built for precisely this question, as opposed to AVERAGE (the typical value) or MAX (the largest value), which answer different questions entirely. 1 mark for the function, 1 for the correct range. Including the heading or B2:B5 would give a wrong answer or a circular reference: the heading is text (not a cohort size at all, so it would corrupt a numeric calculation if MODE tried to include it), and B2:B5 is the answer area itself — a formula in B3 that includes B3's own range is the same self-reference problem explained in the spreadsheets notes.

the marks cover: using a formula (not typing a total), summing column G, multiplying by the rate, referring to B2 instead of typing 19.2 in the formula, and formatting as Rand currency — five genuinely distinct actions chained into one cell. Referring to B2 means that if the rate changes, the result updates: this is the core reason instruction 13 ("use formulas for every calculation") exists at all — a typed, hard-coded number is a frozen snapshot that silently goes stale the moment its inputs change, while a formula referencing B2 recalculates automatically. Alternative: =SUMPRODUCT(G8:G30,B2), which multiplies every value in the range by the rate and adds the results in one step — mathematically equivalent to SUM-then-multiply, just computed in a different order.

COUNTIF counts cells that meet one condition — it's built specifically to answer "how many rows match this criterion," as distinct from SUMIF/SUMIFS (which total a different column based on a condition) or plain COUNT (which only counts numeric cells, regardless of their value). Marks: correct function (1), correct range in column C (1), correct criterion "Platform" (1). Put the criterion in quotes, because Excel treats an unquoted word as a reference to a named cell or range rather than literal text to compare against — quoting it tells Excel explicitly "this is a text value, not a name." COUNT would be wrong because it counts numbers only, and "Platform" is text, so COUNT would return zero regardless of how many rows actually say Platform.

five marks: right lookup function, right lookup value, table on the Costs sheet, correct column index, exact match, with absolute references so it copies down — each is a separate, independently gradeable piece of the formula. When copied, the lookup value moves down one row each time (relative), but the Costs table must stay put (absolute): the whole point of a lookup table is that every row's formula searches the same reference table for its own key, so the key must shift with each row while the table itself must not. If the table was not locked, the range would slide down and eventually return #N/A, because a few rows down the table reference would have drifted past the real data entirely, searching an empty or unrelated range. If Costs is arranged across columns instead of down, use HLOOKUP or XLOOKUP — the horizontal-lookup equivalents of the same underlying search-and-return idea.

six marks: IF used, AND used, attendance test with >=, satisfaction test with >=, "Flag" as the true result and "" (blank) as the false result, plus the fill to J15 — six independently checkable pieces nested inside one formula. Use >=, not >, because "120 or more" includes 120: a strict greater-than would wrongly exclude a row where attendance is exactly 120, which the wording explicitly includes. The empty quotes "" make the cell appear blank, which is different from leaving the formula's false branch empty entirely (that would be a syntax error) — "" is Excel's way of writing "the text value containing zero characters," which displays as nothing while still being a valid, present formula result. Relative references (no $) let each row test its own values when copied down: with no dollar signs, E4 and F4 automatically become E5/F5, E6/F6 and so on as the formula fills downward, exactly like any ordinary relative reference.

SUMIFS adds values only where all conditions are true — it's built for exactly this "total matching two or more criteria at once" shape of question, as opposed to SUMIF (one condition only) or COUNTIFS (counts matches rather than summing a value). Its order is sum range first (Attendance), then each criteria range followed by its criterion, which is a different argument order from SUMIF specifically so that any number of extra range/criterion pairs can be added after the first without ambiguity. Marks: SUMIFS, sum range, venue range plus criterion, type range plus criterion. SUMIF would not do because it handles only one condition, and this question genuinely needs two (venue AND type) satisfied simultaneously. Match the text exactly as it appears in the data, since SUMIFS criteria compare text literally — "Pitch day" and "Pitch Day" would not be treated as the same value.

the paper says A1:F1 but lists only five names, and B2:F2 are the formula cells. So the five names naturally sit in B1:F1, directly above their results — read the cell ranges literally rather than assuming A1 must hold the first name, since the given layout (one blank-ish label cell in A1, five names from B1 onward) is what actually matches both the instruction and the five results expected in B2:F2. VLOOKUP's column index is 5 because Q4 is the fifth column of A:E (Sector, Q1, Q2, Q3, Q4) — VLOOKUP counts columns from the left edge of its own table argument, starting at 1, so the index has to match that table's layout exactly, not the worksheet's column letters. B$1 keeps the row fixed while the column moves when copied right, and the table is absolute so it does not shift: this is a mixed reference (row locked, column free) feeding the lookup value, paired with a fully absolute reference locking the lookup table — exactly the two reference types the spreadsheets notes describe, combined for a formula that's meant to be copied sideways rather than down. FALSE forces an exact match, the same safeguard against wrong approximate matches covered throughout the function notes. Marks: sheet, names, correct lookup, copy across.