I85PREP
⌘ K
Computer Specialist in Spreadsheet & Database Level 1 study materials
Computer Specialist in Spreadsheet & Database Level 1 study resources

Computer Specialist in Spreadsheet & Database Level 1: Formulas, Functions, and References

Computer Specialist in Spreadsheet & Database Level 1 · Work through your exam, one topic at a time.

All guide sections

Formulas, Functions, and References

This domain tests formula construction: operator precedence, reference types, function selection, error diagnosis, and how a formula behaves when copied. Expect questions that ask what a copied formula returns or which function measures a specific thing.

References and precedence

  • Relative references (A1) shift when copied. Absolute references ($A$1) do not. Mixed references lock one part: $A1 locks the column, A$1 locks the row. F4 cycles through the four styles while editing.
  • Precedence follows the standard order: parentheses, negation, percent, exponent, multiplication and division, addition and subtraction, then comparison operators. Text concatenation with & sits between arithmetic and comparison.

Function choices that are commonly confused

  • COUNT counts numeric cells only; COUNTA counts any non-empty cell; COUNTBLANK counts empty results including empty strings returned by formulas.
  • SUMIF adds values meeting one criterion; SUMIFS adds values meeting several; the sum range comes first in SUMIFS but last in SUMIF.
  • IF returns one of two outcomes; nested IFs or IFS handle multiple branches; IFERROR replaces any error with a chosen value.
  • Lookup functions differ in failure behaviour: VLOOKUP defaults to approximate matching, INDEX and MATCH together avoid column-order limits, and XLOOKUP searches in either direction.

Error values and what they mean

#DIV/0! divides by zero or a blank; #VALUE! receives the wrong data type; #REF! points at a deleted cell or sheet; #NAME? uses an unrecognised function or undefined name; #N/A means a lookup found nothing.

Worked example

Cell C1 holds =A1*$B$2. Copying C1 to C2 gives =A2*$B$2. Reasoning: A1 is fully relative so the row advances from 1 to 2; $B$2 is fully absolute so it stays fixed, which is exactly the behaviour wanted when a single rate cell drives a whole column.

Traps that cost marks

  • Using VLOOKUP with approximate match on an unsorted table and accepting a wrong result.
  • Counting text-formatted numbers with COUNT and reporting zero.
  • Creating a circular reference by including the formula cell in its own range.