92526 Chap.11 Excel Functions, Referencing and Pivot Tables
Excel Functions, Referencing and Pivot Tables
The tutorials of Weeks 2 to 4 turn the subject's organising principles into Excel skills, and this chapter gathers the ones that carry calculation. Functions start with an equals sign and point at cells, so results stay linked to the data; AVERAGE, MIN, MAX and SUM take a range, LARGE adds a position and COUNTIF a condition, and functions can be nested.
The chapter writes a body mass index formula from its equation and shows what filling a formula down does to relative and absolute references.
It then explains why averaging hand-picked blocks of a sorted sheet breaks as soon as the sheet is re-sorted, and how pivot tables solve that by summarising a long list through four field areas: filters, rows, columns and values.
It closes with filters, multi-level sorting and the ROUNDDOWN rule for grouping athletes by completed years of age.
What this chapter covers
- 01
Linked cells and built-in functions
- 02
LARGE, COUNTIF and nested functions
- 03
Writing a BMI formula in Excel syntax
- 04
Relative versus absolute references
- 05
Why manual block averages break on re-sort
- 06
Pivot table filters, rows, columns and values
- 07
Filters, multi-level sorts and ROUNDDOWN
Worked example · free
Fill a difference-from-mean formula down three rows
- 1Formula: =F2-summary!$C$3, with the dollar signs locking the column and row of the squad mean.
- 1Fill-down: the relative reference moves to F3 and F4, while the absolute reference $C$3 stays on the same cell.
- 1Results: 79.5 minus 84.0 = -4.5, 88.0 minus 84.0 = +4.0, and 84.5 minus 84.0 = +0.5 kg.
- 1Without the dollar signs, H3 would subtract C4 and H4 would subtract C5, which hold other values or nothing, so the results would be wrong.
Key terms
- Absolute reference
- A cell reference locked with dollar signs, such as $B$2, so it does not change when a formula is copied.
- COUNTIF
- An Excel function that counts the cells in a range meeting a stated condition.
- Nested function
- A function placed inside another, with Excel calculating the inner one first, as in ROUND around AVERAGE.
- Pivot table
- An Excel tool that summarises a long list into a compact table by chosen rows, columns, values and filters.
- ROUNDDOWN
- An Excel function that drops decimals towards zero, used to group ages by completed years.
Excel Functions, Referencing and Pivot Tables FAQ
What is the difference between a relative and an absolute reference?
A relative reference such as C2 changes as a formula is copied, so filled down it becomes C3, C4 and so on. An absolute reference such as $B$2 is locked by dollar signs and stays on the same cell. Use absolute references for a single fixed value, such as a squad maximum or target, that every row divides by.
How do you write a BMI formula in Excel?
Translate the equation, mass in kilograms divided by height in metres squared, into Excel syntax using cell references: with height in centimetres in one column and mass in another, divide the mass cell by the height cell over 100, all squared with ^2. Parentheses force the conversion to metres before squaring, then fill the formula down.
Why do manual averages break when data is re-sorted?
A formula refers to physical cell locations, not to the athletes who were in those cells when it was written. If you average a block of rows because they held the defenders, re-sorting the sheet puts different athletes in those rows and the averages silently change. Pivot tables or condition-based functions avoid this.
What do the four areas of a pivot table do?
Filters include or exclude records, such as one gender. Rows and Columns set the groupings, such as position down the side and injury status across the top. Values holds the number being summarised, which defaults to a sum and can be switched to an average or a count in Value Field Settings.
Why use ROUNDDOWN rather than ROUND for ages?
Ages stored with a decimal place do not group neatly, and hiding decimals does not help because Excel sorts on the stored value. ROUND would put someone aged 17.6 with the 18-year-olds, but they are still 17 until their next birthday, so ROUNDDOWN to zero decimal places groups athletes by completed years.
Exam move
Rebuild each tutorial workbook from a blank sheet rather than rereading it. Type the functions yourself, including LARGE and COUNTIF with their second arguments, write the BMI formula from the equation, and fill a formula down with and without an absolute reference to see the difference.
Recreate the manual average-by-position exercise, re-sort the sheet, and watch it break before building the same summary as a pivot table with Average in Values. Finally, practise explaining in one sentence what each formula does on the row below, which is where most spreadsheet errors appear.
Working through Excel Functions, Referencing and Pivot Tables in 92526? Sia is AskSia’s AI Information Technology tutor — ask any 92526 Excel Functions, Referencing and Pivot Tables question and get a clear, step-by-step explanation grounded in how 92526 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
