A full past-paper spreadsheet task

VLOOKUP: exact match

VLOOKUP (vertical lookup) searches down the first column of a table for a value. Then it returns a value from the same row.

It has four arguments. The table shows each one.

Copied down column E, it finds the right meal price for every pupil.

VLOOKUP: approximate match

With TRUE, or with the 4th argument left out, VLOOKUP does an approximate match. It finds the largest value that is less than or equal to the lookup value.

The first column must be sorted in ascending order (smallest first). If not, the result can be wrong with no error shown.

Use it for bands: ticket prices by age, grades by mark, discounts by quantity.

HLOOKUP

HLOOKUP (horizontal lookup) is VLOOKUP turned on its side. It searches across the top row of a table, then goes down.

Argument 1, lookup_value: what to find (B4, the form 10B).

Argument 2, table_array: the table, with codes in its top row ($H$12:$J$13).

Argument 3, row_index_num: which row of the table to return, counting from 1 at the top (2). Argument 4, range_lookup: FALSE for an exact match.

LOOKUP

LOOKUP searches one column (or row) and returns the value in the same position from another column (or row).

Argument 1, lookup_value: what to find. Argument 2, lookup_vector: the single column to search. Argument 3, result_vector: the column of answers, the same size.

LOOKUP always does an approximate match. The lookup vector must be sorted in ascending order. With no exact match, it uses the largest value that is smaller.

XLOOKUP

XLOOKUP is the newest lookup. It searches one column (or row) and returns from another. Unlike LOOKUP, it does an exact match by default.

Argument 1, lookup_value. Argument 2, lookup_array: where to search. Argument 3, return_array: where the answers are.

Argument 4, if_not_found, is optional: what to show when there is no match. Optional arguments 5 and 6 set the match mode and the search direction.

The answer column can be to the left of the search column. VLOOKUP cannot do that.

IF

IF makes a decision. It checks a condition and returns one of two results.

Argument 1, logical_test: a comparison that is TRUE or FALSE, like F3=0. Argument 2, value_if_true. Argument 3, value_if_false.

Text results go in double quotes. Numbers and cell references do not.

Nested IF and other nested functions

A nested IF puts a second IF inside the first, usually in the value_if_false part. Two IFs give three possible results.

The spreadsheet checks the tests from left to right and stops at the first TRUE one. So the order of the tests matters.

Every IF opens a bracket. Two IFs need two closing brackets at the end.

Any function can be nested. The inner one is worked out first and its result is passed to the outer one.

Error values and what they mean

An error value means the formula cannot give an answer. Each one points to a different problem.

In a printout, an error loses the mark for that formula, and often for cells that use it.

Show formulae, not only values

Normally cells show values: the results. Formulae view shows what is typed in each cell.

Most spreadsheets have a show formulas option, often on the formulas tab or in the view settings.

In formulae view, widen columns until every formula is fully visible. A cut-off formula cannot be marked.

Adjust row heights and column widths, and wrap text in long headings, so nothing is hidden. Hide or show rows and columns only when the task says.

E3   =VLOOKUP(D3,$H$3:$I$5,2,FALSE)
E4   =VLOOKUP(D4,$H$3:$I$5,2,FALSE)
...  (same pattern down to E8)
F3   =$I$7+E3-C3
F4   =$I$7+E4-C4
...  (same pattern down to F8)
G3   =IF(F3=0,"Paid",IF(F3<=10,"Nearly","Chase"))
...  (same pattern down to G8)
C9   =SUM(C3:C8)
E9   =SUM(E3:E8)
F9   =SUM(F3:F8)
C10  =AVERAGE(C3:C8)

Format cells: text, shading, borders, alignment

Typical tasks: make headings bold or italic, add shading (a fill colour), add borders, and set alignment.

Align text to the left and numbers to the right, so the decimal points line up.

Merge and centre a title across the top of the data, for example A1:G1.

Apply exactly what is asked, to exactly the cells named.

Number formats

A number format changes how a value looks. It does not change the value.

Decimal places: 31.8333 shown to 2 decimal places is 31.83. Currency adds a symbol: €31.83, or 31,83 € in Spanish style.

Percentage: the value is shown multiplied by 100, with a % sign. 0.25 shows as 25%.

Use the symbol asked for: euro (€), pound (£) or dollar ($). The $ in a currency format has nothing to do with the $ in an absolute reference.

Conditional formatting

Conditional formatting changes a cell's format automatically when its value meets a rule.

Select the range, choose the rule (for example "greater than"), type the value, then pick the format.

Because it is a rule, the format updates when the data changes. Colour added by hand does not.

Page layout for printing

Set the orientation: portrait (tall) or landscape (wide). Formulae printouts are wide, so landscape usually fits better.

Use fit to page to scale the printout to 1 page wide, or to the number of pages the task says. Set a print area if only part of the sheet must print.

For formulae printouts, switch on gridlines and row and column headings. Then the examiner can see which cell holds each formula.

Add a header or footer with your name, centre number and candidate number. Check the print preview before you print.

How spreadsheet marks are lost

Most lost marks come from the evidence, not from wrong thinking.

Before each printout, ask: is everything visible, is it on the right number of pages, and is my name on it?