Apple Numbers functions

Get to grips with the most useful spreadsheet functions in Numbers

Apple Numbers is a great spreadsheet application that has a wide range of uses for both personal and work projects. Whether you are planning to renovate your house, track ebook sales or analyse data, you are going to need functions. The good news is that Numbers has lots of them, but some are used more often than others.

Here we take a look at some of the most useful and most used in all types of spreadsheets. We start off with some of the simplest functions like SUM and progress to the more complicated VLOOKUP and functions within a function. Many of the functions covered have related ones that are similar, like SUM and SUMIF, COUNT and COUNTIF. Master one and you are halfway to mastering the variations, too

1 SUM

The simplest and probably the most used Numbers function is SUM, which basically just adds columns or rows of numbers or money. Click a cell and type =SUM and press Enter. Click and drag down a column or across a row to select the cells to sum.

2 MAX

We often want to know the biggest value in a collection of numbers or money and for this we use MAX. Click any empty cell and enter =MAX, then click and drag down a column or across a row of cells.

3 MIN

The opposite of MAX is MIN and this function finds the minimum or lowest value of a set of numbers or money. Click an empty cell and enter =MIN, then click and drag across a row or down a column of cells to select them.

4 AVERAGE

We often need to know the average value for a collection of numbers. Enter =AVERAGE into a cell and then click and drag over the numbers to calculate the average. It can be a row, a column or a rectangular block of numbers, as shown here.

5 COUNT

Sometimes we need to count the number of items.

It is obvious in this sheet, but isn’t when there are hundreds of rows and missing items. Enter =COUNT and click and drag over rows, cells or blocks of cells with numbers, expressions or dates to count them.

6 CONCATENATE

Concatenate means to join things together and in this example enter =CONCATENATE and click the First Name cell. Enter a comma, space in quotes, comma, then click the last Name cell. The result is the full name, made by concatenating the first and last names.

7 IF

In this example we test cells in the Age column and use IF D7<40, “Young”, “Old”. This means if the value of D7 is less then 40 then the result is ‘Young’, but if not then it is ‘Old’. It is basically IF (something is true), value1 ELSE value2.

8 SUMIF

SUMIF is like SUM but only if something is true. For example, IF name=“bob” THEN SUM values in the Price column to total his sales. Click a cell and enter =SUMIF. Click and drag down the Name column. Enter “Bob”, then click and drag down the Price column.

9 VLOOKUP

Type =VLOOKUP and click a cell containing what to find, like a ID number. Click and drag over the whole table to show where to look. Then enter the table column number to return (first column is 1, then 2…). An exact or close match can be selected.

10 AND / OR

Earlier we used IF (something is true) THEN value1, value2. A function can be inside a function, so create an IF then click the if-expression and type AND. You can then enter something like A2=“bob”, C2=1 and both must be true. OR can be used instead of AND.