Spreadsheets (AS Level) flashcards

Topic 8: Spreadsheets

Relative, absolute and mixed cell references

B4 is relative: both parts move when copied. $B$4 is absolute: nothing moves. $B4 fixes the column; B$4 fixes the row. A two-way table needs mixed references.

COUNT vs COUNTA vs COUNTBLANK

COUNT counts cells with numbers only. COUNTA counts any non-empty cell. COUNTBLANK counts empty cells.

INT vs ROUND. What are INT(-2.3) and ROUND(14.678, 1)?

INT rounds down to a whole number, so INT(-2.3) = -3. ROUND goes to the nearest value at the stated decimal places, so ROUND(14.678, 1) = 14.7.

How are the arguments of SUMIF and SUMIFS ordered?

SUMIF(range, criterion, sum_range) puts the sum range last. SUMIFS(sum_range, range1, criterion1, ...) puts it first.

How do you write an exact-match VLOOKUP?

VLOOKUP(lookup value, table, column number, FALSE). The table is an absolute range with the key in its first column; the column is counted from the table's first column; FALSE gives an exact match.

How do you build nested IF bands?

List the bands from highest to lowest; test the highest first with >=; put the next IF in each false branch; the last false value is the lowest band; one closing bracket per IF.

IFERROR vs ISERROR

IFERROR(formula, message) replaces an error with a value or message. ISERROR returns TRUE or FALSE.

Pivot table vs subtotals

A pivot table builds a separate summary and needs no sorting. Subtotals insert totals into the list itself and need the data sorted by the grouping field first.

Normal, extreme and abnormal test data for a percentage from 0 to 100

Normal: e.g. 57. Extreme: 0 and 100 (valid, on the limits). Abnormal: e.g. -1, 101 or "pass" (must be rejected).

Input message vs error message in spreadsheet validation

An input message appears when the cell is selected and tells the user what to enter. An error message appears when the rule is broken and says what went wrong and what is allowed.