Using spreadsheet functions, formulae and features at Higher level, including absolute and relative cell references, logical and lookup functions (IF, nested IF, COUNTIF, SUMIF, VLOOKUP), statistical functions, named ranges and conditional formatting.

What this key area is asking

Spreadsheets are a core IT application in Higher Administration and IT, and the SQA expects you to use a range of functions, formulae and features, some of them complex, to process and analyse data accurately. This dot point covers the calculation tools: cell references (relative and absolute), logical functions (IF, nested IF), conditional functions (COUNTIF, SUMIF), lookup functions (VLOOKUP), statistical functions, named ranges and conditional formatting. These appear both in the assignment (where you build a working spreadsheet) and in the question paper (where you explain what a function does and when to use it).

Cell references: relative and absolute

If =B2*$E$1 is copied down a column, the B2 part becomes B3, B4, and so on (relative), while $E$1 stays the same in every row (absolute). Getting references wrong is a common cause of incorrect results, so the SQA tests understanding of both.

Logical and conditional functions

IF and nested IF

The IF function tests a condition and returns one value if it is true and another if it is false:

For example =IF(C2>=50,"Pass","Fail"). A nested IF puts an IF inside another to test several conditions, for example assigning grades A, B or C: =IF(C2>=70,"A",IF(C2>=60,"B","C")).

COUNTIF and SUMIF

• COUNTIF counts the cells in a range that meet a condition: =COUNTIF(D2:D50,"Pass") counts the passes.
• SUMIF adds the cells that meet a condition: =SUMIF(B2:B50,"North",C2:C50) totals the sales for the North region.

These are used to summarise data, for example counting items below a reorder level or totalling sales by category.

Lookup, statistical and other features

• VLOOKUP: look up a value in a table and return matching data (prices, names, rates).
• Statistical functions: SUM (total), AVERAGE (mean), MAX/MIN (largest/smallest), COUNT (how many numbers), ROUND (round to set decimals).
• Named ranges: clearer, less error-prone formulae (=Quantity*Price).
• Conditional formatting: highlight cells meeting a rule, so important values stand out automatically.

Why the right function matters

Choosing the correct function and reference type makes a spreadsheet accurate, efficient and easy to update. A well-built spreadsheet recalculates automatically when data changes, reduces manual work and errors, and presents results clearly. The SQA rewards both building correct formulae (in the assignment) and explaining what a function does and when to use it (in the question paper).

Examples in context

Example 1. A reorder report. A stock spreadsheet uses =IF(C2<D2,"Reorder","OK") to flag items below their reorder level, =COUNTIF(E2:E100,"Reorder") to count how many need reordering, and conditional formatting to highlight them in red. The administrator can see at a glance what to order, showing logical functions and formatting in use.

Example 2. Pricing with VLOOKUP. An order sheet uses =VLOOKUP(A2,Products,3,FALSE) to pull each item's price from a product list by its code, then =B2*C2 to find the line total and =SUM(...) for the order total. When prices change in the product list, the order sheet updates automatically, illustrating VLOOKUP and statistical functions.

Try this

Q1. Write an IF formula that displays "Pass" if cell C2 is 50 or more and "Fail" otherwise. [2 marks]

• Cue. =IF(C2>=50,"Pass","Fail"). The IF function returns the first value if the test is true and the second if it is false.

Q2. Explain why an absolute cell reference would be used for a VAT rate in a spreadsheet. [2 marks]

• Cue. The VAT rate is in one fixed cell; an absolute reference (for example $E$1) stops that reference changing when the formula is copied down, so every row uses the same rate and the results stay correct.