Use common built-in functions, including SUM, AVERAGE, MAX, MIN, COUNT and IF, with cell ranges to summarise and test data

What this dot point is asking

This outcome is about using built-in functions, which are ready-made calculations the spreadsheet provides. You should be able to use SUM, AVERAGE, MAX, MIN and COUNT on a range of cells, and use IF to display one of two results depending on a test. You should also know why functions are better than long manual formulas and how to write a cell range. In the written paper you write functions and explain what they do; in the practical you apply them to real data.

The answer

What a function is

A function is a named, built-in calculation. You give it some inputs, called arguments, usually a range of cells, and it returns a result. A function still starts with an equals sign, then the function name, then brackets holding the arguments, for example =SUM(B2:B11).

Cell ranges

A range is a block of cells written as the first cell, a colon, then the last cell. B2:B11 means all cells from B2 down to B11. Using a range lets one function work on many cells at once, instead of listing each cell.

The summary functions

• SUM adds all the numbers in the range, for example =SUM(B2:B11) totals ten scores.
• AVERAGE finds the mean (the total divided by how many), for example =AVERAGE(B2:B11).
• MAX returns the largest value, for example =MAX(B2:B11).
• MIN returns the smallest value, for example =MIN(B2:B11).
• COUNT counts how many cells in the range hold a number, for example =COUNT(B2:B11) returns 10 if all ten cells have a score.

Why functions beat manual formulas

• Faster. One short function replaces a long chain such as =B2+B3+B4+B5.
• Fewer mistakes. A long manual sum is easy to mistype or to miss a cell.
• Updates automatically. Add a row inside the range or change a value, and the result recalculates.

The IF function

IF makes a decision. It has three parts: a test, the value to show if the test is true, and the value to show if it is false. For example =IF(B2>=50, "Pass", "Fail") checks whether B2 is at least 50; if so it shows "Pass", otherwise "Fail". The comparison operators are useful here: greater than, less than, and greater than or equal to. Text results go in quotation marks.

Examples in context

Example 1. A sales summary. A shop keeps daily sales in a column and uses =SUM(...) for the week's total, =AVERAGE(...) for the daily average, and =MAX(...) to spot the best day. Because the figures live in functions, the summary updates the moment a day's sales are entered.

Example 2. Stock reorder flags. A store uses =IF(B2<10, "Reorder", "OK") so column C shows "Reorder" whenever the stock in column B drops below ten. Copied down the list, it flags every low item at a glance without checking each number by hand.

Try this

• Cue. Write a function to find the average of the values in cells D2 to D20. (=AVERAGE(D2:D20), where the colon makes D2:D20 a range covering all those cells.)

• Cue. Explain the three parts of the IF function in order. (First the test or condition, then the value to show if it is true, then the value to show if it is false.)

• Cue. Give two reasons to use SUM rather than adding the cells one by one. (It is faster as one short formula and less error-prone than a long chain, and it updates automatically if a value changes or a row is added inside the range.)