Spreadsheets and Data Analysis
What the domain covers
Spreadsheets and Data Analysis tests cell references, formulas, functions, charts, sorting and filtering. Marks are lost when a formula is almost right but uses the wrong reference type or range.
Core rules
- A workbook contains worksheets; a cell address combines column letter and row number (
B4). A range is written with a colon (B2:B10). - Every formula starts with
=. Operators follow precedence: brackets, exponent, multiplication/division, addition/subtraction. - Relative references (
C2) change when filled; absolute references ($C$2) stay fixed; mixed references fix only row or column (C$2,$C2). - Functions:
=SUM(B2:B10),=AVERAGE(B2:B10),=COUNT(...)counts numbers,=COUNTA(...)counts non-empty cells,=IF(B2>=35,"Pass","Fail"). - Sort rearranges rows; filter hides rows that do not match. Charts should match the data story: column for comparison, line for trend, pie for parts of a whole.
Worked example
A fee sheet has the rate in B1 and quantities in C2:C6. In D2 write =$B$1*C2 and fill down. The rate stays fixed at B1 while the quantity moves to C3, C4 and so on. Without the $, D3 would multiply by the empty B2 and return 0.
Traps
- Numbers stored as text (usually left-aligned) are ignored by SUM.
#DIV/0!,#VALUE!and#REF!each have a different cause; do not label them all “wrong formula”.- A pie chart of negative values or of overlapping categories misleads.
- Deleting a row used by a formula can produce
#REF!.