Master for­mu­las in Num­bers

Dis­cover how to eas­ily power up your spread­sheets with for­mu­las

Mac Format - - IMPROVE | IOS -

For­mu­las play a key role in ev­ery Num­bers spread­sheet. They al­low you to per­form both sim­ple (‘2+2’) and more com­plex cal­cu­la­tions, such as one based on the con­tent of spe­cific cells that meet a cer­tain con­di­tion. Ei­ther way, they trans­form Num­bers into some­thing very pow­er­ful and ver­sa­tile.

The key rule to en­ter­ing for­mu­las in any spread­sheet is to make sure you pre­cede it with an equals (=) sign, so Num­bers knows to in­ter­pret it as such. Tap the grey = but­ton in the en­try box and Num­bers will re­veal the for­mu­las and func­tions key­board, split into three sec­tions: op­er­a­tors, nu­meric key­pad and ad­vanced op­tions.

Auto up­dates

The step-by-step guide op­po­site walks you through the fun­da­men­tal ba­sics of cre­at­ing and us­ing sim­ple for­mu­las. It’s worth not­ing that for­mu­las based on the con­tent of spe­cific cells au­to­mat­i­cally up­date when you make changes to any of the cells they ref­er­ence. If you copy and paste a for­mula to a dif­fer­ent cell, it’ll up­date its cell ref­er­ences to re­flect its new po­si­tion too. You can change this by in­struct­ing Num­bers to make part or all of the cell ref­er­ence ab­so­lute, by plac­ing a $ sign in front of the rel­e­vant ref­er­ence (col­umn, row or both).

To add th­ese, tap the ^ but­ton next to the cell or cell range in ques­tion in

Num­bers also sup­ports ad­vanced math­e­mat­i­cal func­tions – th­ese are pre­de­fined for­mu­las

the in­put box, then use the Pre­serve Row and Pre­serve Col­umn slid­ers to choose which ref­er­ences should be ab­so­lute. Flick ei­ther or both switches to see the cell ref­er­ence up­date – for ex­am­ple, A1 be­comes $A1, A$1 or $A$1.

Num­bers also sup­ports ad­vanced math­e­mat­i­cal func­tions. Th­ese are ba­si­cally pre­de­fined for­mu­las, and Num­bers has more than 250. You en­coun­tered your first func­tion when in­sert­ing SUM, which adds up con­sec­u­tive cells in a row or col­umn. Sim­i­lar func­tions in­clude AV­ER­AGE (re­turns an av­er­age of the se­lected cells) and FRE­QUENCY (used to dis­play the num­ber of times a spe­cific value oc­curs in a range of cells).

To get started with func­tions, click the func­tions but­ton in the cal­cu­la­tor. You’ll see a list of cat­e­gories, from Date and Time to Sta­tis­ti­cal – tap one to ex­plore its con­tents. Tap the ‘i’ but­ton next to a func­tion for a def­i­ni­tion, break­down of its com­po­nent parts (syn­tax) and an ex­am­ple of it in use.

Tap a func­tion to se­lect it, and it’ll ap­pear in the for­mula bar with the re­quired syn­tax in place, with the first com­po­nent high­lighted in blue. You then sim­ply re­place this with the re­quired data – cell ref­er­ences per­haps, or maybe a spe­cific value – and move on to the next part of the syn­tax. Once done, and if en­tered cor­rectly, you should see the re­sult ap­pear.

Con­di­tional func­tions

We’ll fin­ish by look­ing at two in­cred­i­bly use­ful con­di­tional func­tions, which take into ac­count the con­tent of the cells you se­lect be­fore per­form­ing a cal­cu­la­tion. First up is SUMIF, which re­turns the sum of a se­lected group of num­bers that meet a spe­cific con­di­tion. For ex­am­ple, =SUMIF(A1:A5, ">100") will only adds up those cells that con­tain a num­ber greater than 100, so if your cells con­tain 75, 96, 101, 147 and 269 it will ig­nore the first two cells to re­turn 517.

A re­lated func­tion is IF, which dis­plays one of two val­ues de­pend­ing on whether or not the con­di­tion is met – for ex­am­ple, =IF(E2="Doc­tor Who", "Cor­rect", "False"). It’s a key build­ing block should you ever want to de­sign, say, a quiz in Num­bers. Nick Peers

By de­fault, all cell ref­er­ences are rel­a­tive – use the $ character to make part or all of them ab­so­lute.

Here we’ve used the IF func­tion to tell peo­ple if the an­swer they en­ter in a quiz is cor­rect or not.

Newspapers in English

Newspapers from Australia

© PressReader. All rights reserved.