Spreadsheets and Data Analysis
Tested here: cell referencing, formulas and functions, error values, sorting and filtering, and charts built from the correct range.
Cells and references
A cell address is column letter + row number (B7); a range uses a colon (B2:B11). Relative references (B2) shift when copied; absolute ($B$2) stay fixed; mixed ($B2, B$2) lock one part. Every formula begins with =. Operators follow BODMAS: ^ before * and /, before + and −, with comparisons last.
Core functions
SUM, AVERAGE, MAX, MIN, COUNT (numeric cells only), COUNTA (non-empty cells), COUNTIF(range,criteria), ROUND(number,digits), IF(test,true_value,false_value), VLOOKUP(value,table,col_index,FALSE).
Worked example
Marks are in B2:D2 and the grade must appear in F2. Formula: =IF(AVERAGE(B2:D2)>=60,"First",IF(AVERAGE(B2:D2)>=45,"Second","Fail")) Reasoning: Excel tests the first condition and evaluates the inner IF only when it is false, so the highest band must be tested first; reversing the order would send a 70-mark student to "Second". If the pass mark sits in a fixed cell such as $H$1, write >=$H$1 so it does not shift when the formula is filled down.
Error values
#DIV/0! division by zero or an empty divisor; #REF! a referenced cell was deleted; #NAME? a misspelt function name; #VALUE! text used in arithmetic; ##### the column is too narrow.
Traps
- Typing the answer 250 instead of the formula where formula marks are awarded.
- Copying =B2*C2 down and expecting the second factor to stay fixed - use $F$1.
- Confusing COUNT (numbers only) with COUNTA (all non-empty cells).
- Charts that include the total row or the label column as a series.