Master formulas in Numbers
Discover how to easily power up your spreadsheets with formulas
Formulas play a key role in every Numbers spreadsheet. They allow you to perform both simple (‘2+2’) and more complex calculations, such as one based on the content of specific cells that meet a certain condition. Either way, they transform Numbers into something very powerful and versatile.
The key rule to entering formulas in any spreadsheet is to make sure you precede it with an equals (=) sign, so Numbers knows to interpret it as such. Tap the grey = button in the entry box and Numbers will reveal the formulas and functions keyboard, split into three sections: operators, numeric keypad and advanced options.
Auto updates
The step-by-step guide opposite walks you through the fundamental basics of creating and using simple formulas. It’s worth noting that formulas based on the content of specific cells automatically update when you make changes to any of the cells they reference. If you copy and paste a formula to a different cell, it’ll update its cell references to reflect its new position too. You can change this by instructing Numbers to make part or all of the cell reference absolute, by placing a $ sign in front of the relevant reference (column, row or both).
To add these, tap the ^ button next to the cell or cell range in question in
Numbers also supports advanced mathematical functions – these are predefined formulas
the input box, then use the Preserve Row and Preserve Column sliders to choose which references should be absolute. Flick either or both switches to see the cell reference update – for example, A1 becomes $A1, A$1 or $A$1.
Numbers also supports advanced mathematical functions. These are basically predefined formulas, and Numbers has more than 250. You encountered your first function when inserting SUM, which adds up consecutive cells in a row or column. Similar functions include AVERAGE (returns an average of the selected cells) and FREQUENCY (used to display the number of times a specific value occurs in a range of cells).
To get started with functions, click the functions button in the calculator. You’ll see a list of categories, from Date and Time to Statistical – tap one to explore its contents. Tap the ‘i’ button next to a function for a definition, breakdown of its component parts (syntax) and an example of it in use.
Tap a function to select it, and it’ll appear in the formula bar with the required syntax in place, with the first component highlighted in blue. You then simply replace this with the required data – cell references perhaps, or maybe a specific value – and move on to the next part of the syntax. Once done, and if entered correctly, you should see the result appear.
Conditional functions
We’ll finish by looking at two incredibly useful conditional functions, which take into account the content of the cells you select before performing a calculation. First up is SUMIF, which returns the sum of a selected group of numbers that meet a specific condition. For example, =SUMIF(A1:A5, ">100") will only adds up those cells that contain a number greater than 100, so if your cells contain 75, 96, 101, 147 and 269 it will ignore the first two cells to return 517.
A related function is IF, which displays one of two values depending on whether or not the condition is met – for example, =IF(E2="Doctor Who", "Correct", "False"). It’s a key building block should you ever want to design, say, a quiz in Numbers. Nick Peers