Apple iWork - Formulas and Functions User Guide

iWork

Formulas and Functions User Guide

Apple Inc. K

Under the copyright laws, this manual may not be copied, in whole or in part, without the written consent of Apple. Your rights to the software are governed by the accompanying software license agreement.

The Apple logo is a trademark of Apple Inc., registered in the U.S. and other countries. Use of the “keyboard” Apple logo (Option-Shift-K) for commercial purposes without the prior written consent of Apple may constitute trademark infringement and unfair competition in violation of federal and state laws.

Every eort has been made to ensure that the

information in this manual is accurate. Apple is not responsible for printing or clerical errors.

Apple

1 Innite Loop

Cupertino, CA 95014-2084 408-996-1010 www.apple.com

Apple, the Apple logo, iWork, Keynote, Mac, Mac OS, Numbers, and Pages are trademarks of Apple Inc., registered in the U.S. and other countries.

Adobe and Acrobat are trademarks or registered trademarks of Adobe Systems Incorporated in the U.S. and/or other countries.

Other company and product names mentioned herein are trademarks of their respective companies. Mention of third-party products is for informational purposes only and constitutes neither an endorsement nor a recommendation. Apple assumes no responsibility with regard to the performance or use of these products.

019-1588 09/2009

13 Preface: Welcome to iWork Formulas & Functions

15 Chapter 1: Using Formulas in Tables 15 The Elements of Formulas 17 Performing Instant Calculations in Numbers 18 Using Predened Quick Formulas 19 Creating Your Own Formulas 19 Adding and Editing Formulas Using the Formula Editor 20 Adding and Editing Formulas Using the Formula Bar 21 Adding Functions to Formulas 23 Handling Errors and Warnings in Formulas 24 Removing Formulas 24 Referring to Cells in Formulas 26 Using the Keyboard and Mouse to Create and Edit Formulas 27 Distinguishing Absolute and Relative Cell References 28 Using Operators in Formulas 28 The Arithmetic Operators 29 The Comparison Operators 30 The String Operator and the Wildcards 30 Copying or Moving Formulas and Their Computed Values 31 Viewing All Formulas in a Spreadsheet 32 Finding and Replacing Formula Elements

33 Chapter 2: Overview of the iWork Functions 33 An Introduction to Functions 34 Information About Functions 34 Syntax Elements and Terms Used In Function Denitions 36 Value Types 40 Listing of Function Categories 41 Pasting from Examples in Help

42 Chapter 3: Date and Time Functions 42 Listing of Date and Time Functions 44 DATE

45 DATEDIF 47 DATEVALUE 47 DAY 48 DAYNAME 49 DAYS360 50 EDATE 51 EOMONTH 51 HOUR 52 MINUTE 53 MONTH 54 MONTHNAME 54 NETWORKDAYS 55 NOW 56 SECOND 56 TIME 57 TIMEVALUE 58 TODAY 59 WEEKDAY 60 WEEKNUM 61 WORKDAY 62 YEAR 63 YEARFRAC

64 Chapter 4: Duration Functions 64 Listing of Duration Functions 65 DUR2DAYS 65 DUR2HOURS 66 DUR2MILLISECONDS 67 DUR2MINUTES 68 DUR2SECONDS 69 DUR2WEEKS 70 DURATION 71 STRIPDURATION

72 Chapter 5: Engineering Functions 72 Listing of Engineering Functions 73 BASETONUM 74 BESSELJ 75 BESSELY 76 BIN2DEC 77 BIN2HEX 78 BIN2OCT 79 CONVERT

4 Contents

80 Supported Conversion Units 80 Weight and mass 80 Distance 80 Duration 81 Speed 81 Pressure 81 Force 81 Energy 82 Power 82 Magnetism 82 Temperature 82 Liquid 83 Metric prexes 83 DEC2BIN 84 DEC2HEX 85 DEC2OCT 86 DELTA 87 ERF 87 ERFC 88 GESTEP 89 HEX2BIN 90 HEX2DEC 91 HEX2OCT 92 NUMTOBASE 93 OCT2BIN 94 OCT2DEC 95 OCT2HEX

96 Chapter 6: Financial Functions 96 Listing of Financial Functions 99 ACCRINT 101 ACCRINTM 103 BONDDURATION 104 BONDMDURATION 105 COUPDAYBS 107 COUPDAYS 108 COUPDAYSNC 109 COUPNUM 110 CUMIPMT 112 CUMPRINC 114 DB 116 DDB 117 DISC

Contents 5

119 EFFECT 120 FV 122 INTRATE 123 IPMT 125 IRR 126 ISPMT 128 MIRR 129 NOMINAL 130 NPER 132 NPV 134 PMT 135 PPMT 137 PRICE 138 PRICEDISC 140 PRICEMAT 141 PV 144 RATE 146 RECEIVED 147 SLN 148 SYD 149 VDB 150 YIELD 152 YIELDDISC 153 YIELDMAT

155 Chapter 7: Logical and Information Functions 155 Listing of Logical and Information Functions 156 AND 157 FALSE 158 IF 159 IFERROR 160 ISBLANK 161 ISERROR 162 ISEVEN 163 ISODD 164 NOT 165 OR 166 TRUE

167 Chapter 8: Numeric Functions 167 Listing of Numeric Functions 170 ABS 170 CEILING

6 Contents

172 COMBIN 173 EVEN 174 EXP 174 FAC T 175 FACTDOUBLE 176 FLOOR 177 GCD 178 INT 179 LCM 179 LN 180 LOG 181 LOG10 182 MOD 183 MROUND 184 MULTINOMIAL 185 ODD 186 PI 186 POWER 187 PRODUCT 188 QUOTIENT 189 RAND 189 RANDBETWEEN 190 ROMAN 191 ROUND 192 ROUNDDOWN 193 ROUNDUP 195 SIGN 195 SQRT 196 SQRTPI 196 SUM 197 SUMIF 198 SUMIFS 200 SUMPRODUCT 201 SUMSQ 202 SUMX2MY2 203 SUMX2PY2 204 SUMXMY2 204 TRUNC

206 Chapter 9: Reference Functions 206 Listing of Reference Functions 207 ADDRESS 209 AREAS

Contents 7

209 CHOOSE 210 COLUMN 211 COLUMNS 211 HLOOKUP 213 HYPERLINK 214 INDEX 216 INDIRECT 217 LOOKUP 218 MATCH 219 OFFSET 221 ROW 221 ROWS 222 TRANSPOSE 223 VLOOKUP

225 Chapter 10: Statistical Functions 225 Listing of Statistical Functions 230 AVEDEV 231 AVERAGE 232 AVERAGEA 233 AVERAGEIF 234 AVERAGEIFS 236 BETADIST 237 BETAINV 238 BINOMDIST 239 CHIDIST 239 CHIINV 240 CHITEST 242 CONFIDENCE 242 CORREL 244 COUNT 245 COUNTA 246 COUNTBLANK 247 COUNTIF 248 COUNTIFS 250 CO VAR 252 CRITBINOM 253 DEVSQ 253 EXPONDIST 254 FDIST 255 FINV 256 FORECAST 257 FREQUENCY

8 Contents

259 GAMMADIST 260 GAMMAINV 260 GAMMALN 261 GEOMEAN 262 HARMEAN 262 INTERCEPT 264 LARGE 265 LINEST 267 Additional Statistics 268 LOGINV 269 LOGNORMDIST 270 MAX 270 MAXA 271 MEDIAN 272 MIN 273 MINA 274 MODE 275 NEGBINOMDIST 276 NORMDIST 277 NORMINV 277 NORMSDIST 278 NORMSINV 279 PERCENTILE 280 PERCENTRANK 281 PERMUT 282 POISSON 282 PROB 284 QUARTILE 285 RANK 287 SLOPE 288 SMALL 289 STANDARDIZE 290 STDEV 291 STDEVA 293 STDEVP 294 STDEVPA 296 TDIST 297 TINV 297 TTEST 298 VAR 300 VARA 302 VARP 303 VARPA

Contents 9

305 ZTEST

306 Chapter 11: Text Functions 306 Listing of Text Functions 308 CHAR 308 CLEAN 309 CODE 310 CONCATENATE 311 DOLLAR 312 EXACT 312 FIND 313 FIXED 314 LEFT 315 LEN 316 LOWER 316 MID 317 PROPER 318 REPLACE 319 REPT 319 RIGHT 320 SEARCH 322 SUBSTITUTE 323 T 323 TRIM 324 UPPER 325 VALUE

326 Chapter 12: Trigonometric Functions 326 Listing of Trigonometric Functions 327 ACOS 328 ACOSH 329 ASIN 329 ASINH 330 ATAN 331 ATAN2 332 ATANH 333 COS 334 COSH 334 DEGREES 335 RADIANS 336 SIN 337 SINH 338 TAN

10 Contents

339 TANH

340 Chapter 13: Additional Examples and Topics 340 Additional Examples and Topics Included 341 Common Arguments Used in Financial Functions 348 Choosing Which Time Value of Money Function to Use 348 Regular Cash Flows and Time Intervals 350 Irregular Cash Flows and Time Intervals 351 Which Function Should You Use to Solve Common Financial Questions? 353 Example of a Loan Amortization Table 355 More on Rounding 358 Using Logical and Information Functions Together 358 Adding Comments Based on Cell Contents 360 Trapping Division by Zero 360 Specifying Conditions and Using Wildcards 362 Survey Results Example

365 Index

Contents 11

Welcome to iWork Formulas & Functions

iWork comes with more than 250 functions you can use

to simplify statistical, nancial, engineering, and other computations. The built-in Function Browser gives you a quick way to learn about functions and add them to a

formula.

To get started, just type the equal sign in an empty table cell to open the Formula

Editor. Then choose Insert > Function > Show Function Browser.

Preface

This user guide provides detailed instructions to help you write formulas and use functions. In addition to this book, other resources are available to help you.

Onscreen help

Onscreen help contains all of the information in this book in an easy-to-search format that’s always available on your computer. You can open iWork Formulas & Functions Help from the Help menu in any iWork application. With Numbers, Pages, or Keynote open, choose Help > “iWork Formulas & Functions Help.”

iWork website

Read the latest news and information about iWork at www.apple.com/iwork.

Support website

Find detailed information about solving problems at www.apple.com/support/iwork.

Help tags

iWork applications provide help tags—brief text descriptions—for most onscreen items. To see a help tag, hold the pointer over an item for a few seconds.

Online video tutorials

Online video tutorials at www.apple.com/iwork/tutorials provide how-to videos about

performing common tasks in Keynote, Numbers, and Pages. The rst time you open

an iWork application, a message appears with a link to these tutorials on the web. You can view these video tutorials anytime by choosing Help > Video Tutorials in Keynote, Numbers, and Pages.

14 Preface Welcome to iWork Formulas & Functions

Using Formulas in Tables

This chapter explains how to perform calculations in table

cells by using formulas.

The Elements of Formulas

A formula performs a calculation and displays the result in the cell where you place the formula. A cell containing a formula is referred to as a formula cell.

For example, in the bottom cell of a column you can insert a formula that sums the numbers in all the cells above it. If any of the values in the cells above the formula cell change, the sum displayed in the formula cell updates automatically.

A formula performs calculations using specic values you provide. The values can

be numbers or text (constants) you type into the formula. Or they can be values that reside in table cells you identify in the formula by using cell references. Formulas use operators and functions to perform calculations using the values you provide:

Â Operators are symbols that initiate arithmetic, comparison, or string operations. You

use the symbols in formulas to indicate the operation you want to use. For example, the symbol + adds values, and the symbol = compares two values to determine

whether they’re equal.

=A2 + 16: A formula that uses an operator to add two values.

=: Always precedes a formula.

A2: A cell reference. A2 refers to the second cell in the rst column.

+: An arithmetic operator that adds the value that precedes it with the value that

follows it.

16: A numeric constant.

Â Functions are predened, named operations, such as SUM and AVERAGE. To use a

function, you enter its name and, in parentheses following the name, you provide the arguments the function needs. Arguments specify the values the function will use when it performs its operations.

=SUM(A2:A10): A formula that uses the function SUM to add the values in a range

of cells (nine cells in the rst column).

A2:A10: A cell reference that refers to the values in cells A2 through A10.

To learn how to Go to

Instantly display the sum, average, minimum value, maximum value, and count of values in selected cells and optionally save the formula used to derive these values in Numbers

Quickly add a formula that displays the sum,

average, minimum value, maximum value, count, or product of values in selected cells

Use tools and techniques to create and modify

your formulas in Numbers

Use tools and techniques to create and modify

your formulas in Pages and Keynote

Use the hundreds of iWork functions and review examples illustrating ways to apply the functions

in nancial, engineering, statistical, and other

contexts

Add cell references of dierent kinds to a formula

in Numbers

Use operators in formulas “The Arithmetic Operators” (page 28)

Copy or move formulas or the value they compute among table cells

Find formulas and formula elements in Numbers “Viewing All Formulas in a Spreadsheet” (page 31)

“Performing Instant Calculations in Numbers” (page 17 )

“Using Predened Quick Formulas” (page 18 )

“Adding and Editing Formulas Using the Formula Editor” (page 19 )

“Adding and Editing Formulas Using the Formula Bar” (page 20)

“Adding Functions to Formulas” (page 21 )

“Removing Formulas” (page 24)

“Adding and Editing Formulas Using the Formula Editor” (page 19 )

Help > “iWork Formulas and Functions Help”

Help > “iWork Formulas and Functions User Guide”

“Referring to Cells in Formulas” (page 24)

“Using the Keyboard and Mouse to Create and Edit Formulas” (page 26)

“Distinguishing Absolute and Relative Cell References” (page 27)

“The Comparison Operators” (page 29)

“The String Operator and the Wildcards” (page 30)

“Copying or Moving Formulas and Their Computed Values” (page 30)

“Finding and Replacing Formula Elements” (page 32)

16 Chapter 1 Using Formulas in Tables

Performing Instant Calculations in Numbers

The results in the lower left are based on values in these two selected cells.

In the lower left of the Numbers window, you can view the results of common calculations using values in two or more selected table cells.

To perform instant calculations:

1 Select two or more cells in a table. They don’t have to be adjacent.

The results of calculations using the values in those cells are instantly displayed in the lower left corner of the window.

sum: Shows the sum of numeric values in selected cells.

avg: Shows the average of numeric values in selected cells.

min: Shows the smallest numeric value in selected cells.

max: Shows the largest numeric value in selected cells.

count: Shows the number of numeric values and date/time values in selected cells.

Empty cells and cells that contain types of values not listed above aren’t used in the calculations.

2 To perform another set of instant calculations, select dierent cells.

If you nd a particular calculation very useful and you want to incorporate it into a

table, you can add it as a formula to an empty table cell. Simply drag sum, avg, or one of the other items in the lower left to an empty cell. The cell doesn’t have to be in the same table as the cells used in the calculations.

Chapter 1 Using Formulas in Tables 17

Using Predened Quick Formulas

An easy way to perform a basic calculation using values in a range of adjacent

table cells is to select the cells and then add a quick formula. In Numbers, this is

accomplished using the Function pop-up menu in the toolbar. In Keynote and Pages, use the Function pop-up menu in the Format pane of the Table inspector.

Sum: Calculates the sum of numeric values in selected cells.

Average: Calculates the average of numeric values in selected cells.

Minimum: Determines the smallest numeric value in selected cells.

Maximum: Determines the largest numeric value in selected cells

Count: Determines the number of numeric values and date/time values in selected cells.

Product: Multiplies all the numeric values in selected cells.

You can also choose Insert > Function and use the submenu that appears.

Empty cells and cells containing types of values not listed are ignored.

Here are ways to add a quick formula:

To use selected values in a column or a row, select the cells. In Numbers, click Function m in the toolbar, and choose a calculation from the pop-up menu. In Keynote or Pages, choose Insert > Function and use the submenu that appears.

If the cells are in the same column, the result is placed in the rst empty cell beneath

the selected cells. If there is no empty cell, a row is added to hold the result. Clicking on the cell will display the formula.

If the cells are in the same row, the result is placed in the rst empty cell to the right

of the selected cells. If there is no empty cell, a column is added to hold the result. Clicking on the cell will display the formula.

To use m all the values in a column’s body cells, rst click the column’s header cell or reference tab. Then, in Numbers, click Function in the toolbar, and choose a calculation from the pop-up menu. In Keynote or Pages, choose Insert > Function and use the submenu that appears.

The result is placed in a footer row. If a footer row doesn’t exist, one is added. Clicking on the cell will display the formula.

18 Chapter 1 Using Formulas in Tables

To use m all the values in a row, rst click the row’s header cell or reference tab. Then,

All formulas must begin

with the equal sign.

The Sum function.

References to cells using their names.

A reference to a

range of three cells.

The Subtraction operator.

in Numbers, click Function in the toolbar, and choose a calculation from the popup menu. In Keynote or Pages, choose Insert > Function and use the submenu that appears.

The result is placed in a new column. Clicking on the cell will display the formula.

Creating Your Own Formulas

Although you can use several shortcut techniques to add formulas that perform

simple calculations (see “Performing Instant Calculations in Numbers” on page 17 and “Using Predened Quick Formulas” on page 18 ), when you want more control you use the formula tools to add formulas.

To learn how to Go to

Use the Formula Editor to work with a formula “Adding and Editing Formulas Using the Formula

Editor” (page 19 )

Use the resizable formula bar to work with a

formula in Numbers

Use the Function Browser to quickly add

functions to formulas when using the Formula Editor or the formula bar

Detect an erroneous formula “Handling Errors and Warnings in

“Adding and Editing Formulas Using the Formula Bar” (page 20)

“Adding Functions to Formulas” (page 21 )

Formulas” (page 23)

Chapter 1 Using Formulas in Tables 19

Adding and Editing Formulas Using the Formula Editor

The Formula Editor may be used as an alternative to editing a formula directly in the formula bar (see “Adding and Editing Formulas Using the Formula Bar” on page 20).

The Formula Editor has a text eld that holds your formula. As you add cell references,

operators, functions, or constants to a formula, they look like this in the Formula Editor.

Here are ways to work with the Formula Editor:

To open the Formula Editor, do one of the following: m

Select a table cell and then type the equal sign (=). Â

In Numbers, double-click a table cell that contains a formula. In Keynote and Pages, Â select the table, and then double-click a table cell that contains a formula.

In Numbers only, select a table cell, click Function in the toolbar, and then choose Â Formula Editor from the pop-up menu.

In Numbers only, select a table cell and then choose Insert > Function > Formula Â

The Subtraction operator.

References to cells using their names.

The Sum function.

All formulas must begin

with the equal sign.

A reference to a range of three cells.

Editor. In Keynote and Pages, choose Formula Editor from the Function pop-up menu in the Format pane of the Table inspector.

Select a cell that contains a formula, and then press Option-Return. Â

The Formula Editor opens over the selected cell, but you can move it.

To move the Formula Editor, hold the pointer over the left side of the Formula Editor m until it changes into a hand, and then drag.

To build your formula, do the following: m

To add an operator or a constant to the text eld, place the insertion point and type. Â You can use the arrow keys to move the insertion point around in the text eld. See

“Using Operators in Formulas” on page 28 to learn about operators you can use.

Note: When your formula requires an operator and you haven’t added one, the + operator is inserted automatically. Select the + operator and type a dierent

operator if needed.

To add cell references to the text eld, place the insertion point and follow the Â

instructions in “Referring to Cells in Formulas” on page 24.

To add functions to the text eld, place the insertion point and follow the Â

instructions in “Adding Functions to Formulas” on page 21.

To remove an element from the text eld, select the element and press Delete. m

To accept changes, press Return, press Enter, or click the Accept button in the Formula m Editor. You can also click outside the table.

To close the Formula Editor and not accept any changes you made, press Esc or click the Cancel button in the Formula Editor.

Adding and Editing Formulas Using the Formula Bar

In Numbers, the formula bar, located beneath the format bar, lets you create and modify formulas for a selected cell. As you add cell references, operators, functions, or constants to a formula, they appear like this.

Here are ways to work with the formula bar:

To add or edit a formula, select the cell and add or change formula elements in the m formula bar.

To add elements to your formula, do the following: m

20 Chapter 1 Using Formulas in Tables

To add an operator or a constant, place the insertion point in the formula bar and Â type. You can use the arrow keys to move the insertion point around. See “Using Operators in Formulas” on page 28 to learn about operators you can use.

When your formula requires an operator and you haven’t added one, the + operator is inserted automatically. Select the + operator and type a dierent operator if needed.

To add cell references to the formula, place the insertion point and follow the Â instructions in “Referring to Cells in Formulas” on page 24.

To add functions to the formula, place the insertion point and follow the Â instructions in “Adding Functions to Formulas” on page 21.

To increase or decrease the display size of formula elements in the formula bar, choose m an option from the Formula Text Size pop-up menu above the formula bar.

To increase or decrease the height of the formula bar, drag the resize control at the far right of the formula bar down or up, or double-click the resize control to auto-t

the formula.

To remove an element from the formula, select the element and press Delete. m

To save changes, press Return, press Enter, or click the Accept button above the m formula bar. You can also click outside the formula bar.

To avoid saving any changes you made, click the Cancel button above the formula bar.

Adding Functions to Formulas

A function is a predened, named operation (such as SUM and AVERAGE) that you can

use to perform a calculation. A function can be one of several elements in a formula, or it can be the only element in a formula.

There are several categories of functions, ranging from nancial functions that

calculate interest rates, investment values, and other information to statistical functions that calculate averages, probabilities, standard deviations, and so on. To learn about all the iWork function categories and their functions, and to review numerous examples that illustrate how to use them, choose Help > “iWork Formulas and Functions Help” or Help > “iWork Formulas and Functions User Guide”.

Chapter 1 Using Formulas in Tables 21

Although you can type a function into the text eld of the Formula Editor or into the

Select a function to view information about it.

Search for a function.

Insert the selected function.

Select a category to view functions in that category.

formula bar (Numbers only), the Function Browser oers a convenient way to add a

function to a formula.

Left pane: Lists categories of functions. Select a category to view functions in that

category. Most categories represent families of related functions. The All category lists all the functions in alphabetical order. The Recent category lists the ten functions most recently inserted using the Function Browser.

Right pane: Lists individual functions. Select a function to view information about it

and to optionally add it to a formula.

Lower pane: Displays detailed information about the selected function.

To use the Function Browser to add a function:

1 In the Formula Editor or the formula bar (Numbers only), place the insertion point

where you want the function added.

Note: When your formula requires an operator before or after a function and you

haven’t added one, the + operator is inserted automatically. Select the + operator and

type a dierent operator if needed.

22 Chapter 1 Using Formulas in Tables

2 In Pages or Keynote, choose Insert > Function > Show Function Browser to open

Help for the “issue” argument appears when the pointer is over the placeholder.

Placeholders for optional arguments are light gray.

Click to see a list of valid values.

the Function Browser. In Numbers, open the Function Browser by doing one of the following:

Click the Function Browser button in the formula bar. Â

Click the Function button in the toolbar and choose Show Function Browser. Â

Choose Insert > Function > Show Function Browser. Â

Choose View > Show Function Browser. Â

3 Select a function category.

4 Choose a function by double-clicking it or by selecting it and clicking Insert Function.

5 In the Formula Editor or formula bar (Numbers only), replace each argument

placeholder in the inserted function with a value.

To review a brief description of an argument’s value: Hold the pointer over the argument placeholder. You can also refer to information about the argument in the Function Browser window.

To specify a value to replace any argument placeholder: Click the argument placeholder and type a constant or insert a cell reference (see “Referring to Cells in Formulas” on page 24 for instructions). If the argument placeholder is light gray, providing a value is optional.

To specify a value to replace an argument placeholder that has a disclosure triangle: Click the disclosure triangle and then choose a value from the pop-up menu.

To review information about a value in the pop-up menu, hold the pointer over the value. To review help for the function, select Function Help.

Handling Errors and Warnings in Formulas

When a formula in a table cell is incomplete, contains invalid cell references, or is otherwise incorrect, or when an import operation creates an error condition in a cell, Number or Pages displays an icon in the cell. A blue triangle in the upper left of a cell indicates one or more warnings. A red triangle in the middle of a cell means that a formula error occurred.

Chapter 1 Using Formulas in Tables 23

To view error and warning messages:

Click the icon. m

A message window summarizes each error and warning condition associated with

"the cell.

To have Numbers issue a warning when a cell referenced in a formula is empty, choose Numbers > Preferences and in the General pane select “Show warnings when formulas reference empty cells.” This option is not available in Keynote or Pages.

Removing Formulas

If you no longer want to use a formula that’s associated with a cell, you can quickly

remove the formula.

To remove a formula from a cell:

1 Select the cell.

2 Press the Delete key.

In Numbers, if you need to review formulas in a spreadsheet before deciding what to

delete, choose View > Show Formula List.

Referring to Cells in Formulas

All tables have reference tabs. These are the row numbers and column headings. In Numbers, the reference tabs are visible anytime the table has focus; for example, a cell in the table is currently selected. In Keynote and Pages, reference tabs appear only when a formula within a table cell is selected. In Numbers, the reference tabs look like this:

The reference tabs are the gray box at the top of each column or at the left of each row containing the column letters (for example, “A”) or row numbers (for example, “3”). The look of the reference tabs in Keynote and Pages is similar to the look in Numbers.

You use cell references to identify cells whose values you want to use in formulas. In Numbers, the cells can be in the same table as the formula cell, or they can be in

another table on the same or a dierent sheet.

24 Chapter 1 Using Formulas in Tables

Cell references have dierent formats, depending on such factors as whether the cell’s

table has headers, whether you want to refer to a single cell or a range of cells, and so on. Here’s a summary of the formats that you can use for cell references.

To refer to Use this format Example

Any cell in the table containing the formula

A cell in a table that has a header row and a header column

A cell in a table that has multiple header rows or columns

A range of cells A colon (:) between the rst

All the cells in a row The row name or row-

All the cells in a column The column letter or name C refers to all the cells in the

All the cells in a range of rows A colon (:) between the row

All the cells in a range of columns

In Numbers, a cell in another table on the same sheet

In Numbers, a cell in a table on another sheet

The reference tab letter followed by the reference tab number for the cell

The column name followed by the row name

The name of the header whose columns or rows you want to refer to

and last cell in the range, using reference tab notation to identify the cells

number:row-number

number or name of the rst and

last row in the range

A colon (:) between the column

letter or name of the rst and

last column in the range

If the cell name is unique in the

spreadsheet then only the cell

name is required; otherwise,

the table name followed by two colons (::) and then the cell

identier

If the cell name is unique in the

spreadsheet then only the cell

name is required; otherwise,

the sheet name followed by two colons (::), the table name, two more colons, then the cell

identier

C55 refers to the 55 third column.

2006 Revenue refers to a cell whose header row contains 2006 and header column contains Revenue.

If 2006 is a header that spans two columns (Revenue and Expenses), 2006 refers to all the cells in the Revenue and Expenses columns.

B2:B5 refers to four cells in the second column.

1:1 refers to all the cells in the

rst row.

third column.

2:6 refers to all the cells in ve

rows.

B:C refers to all the cells in the second and third columns.

Table 2::B5 refers to cell B5 in a table named Table 2. Table 2::2006 Class Enrollment refers to a cell by name.

Sheet 2::Table 2::2006 Class Enrollment refers to a cell in a

table named Table 2 on a sheet named Sheet 2.

row in the

Chapter 1 Using Formulas in Tables 25

In Numbers, you can omit a table or sheet name if the cell or cells referenced have

names unique in the spreadsheet.

In Numbers, when you reference a cell in a multirow or multicolumn header, you’ll notice the following behavior:

The name in the header cell closest to the cell referring to it is used. For example, if Â a table has two header rows, and B1 contains “Dog” and B2 contains “Cat,” when you save a formula that uses “Dog,” “Cat” is saved instead.

However, if “Cat” appears in another header cell in the spreadsheet, “Dog” is retained. Â

To learn how to insert cell references into a formula, see “Using the Keyboard and Mouse to Create and Edit Formulas” below. See “Distinguishing Absolute and Relative Cell References” on page 27 to learn about absolute and relative forms of cell references, which are important when you need to copy or move a formula.

Using the Keyboard and Mouse to Create and Edit Formulas

You can type cell references into a formula, or you can insert cell references using mouse or keyboard shortcuts.

Here are ways to insert cell references:

To use a keyboard shortcut to enter a cell reference, place the insertion point in the m Formula Editor or formula bar (Numbers only) and do one of the following:

To refer to a single cell, press Option and then use the arrow keys to select the cell. Â

To refer to a range of cells, press and hold Shift-Option after selecting the rst cell in Â

the range until the last cell in the range is selected.

In Numbers, to refer to cells in another table on the same or a dierent sheet, select Â

the table by pressing Option-Command–Page Down to move downward through tables or Option-Command–Page Up to move upward through tables. Once the desired table is selected, continue holding down Option, but release Command, and use the arrow keys to select the desired cell or range (using Shift-Option) of cells.

To specify absolute and relative attributes of a cell reference after inserting one, Â click the inserted reference and press Command-K to cycle through the options. See “Distinguishing Absolute and Relative Cell References” on page 27 for more information.

To use the mouse to enter a cell reference, place the insertion point in the Formula m Editor or the formula bar (Numbers only) and do one of the following in the same

table as the formula cell or, for Numbers only, in a dierent table on the same or a dierent sheet:

To refer to a single cell, click the cell. Â

To refer to all the cells in a column or a row, click the reference tab for the column Â or row.

26 Chapter 1 Using Formulas in Tables

To refer to a range of cells, click a cell in the range and drag up, down, left, or right Â

to select or resize the cell range.

To specify absolute and relative attributes of a cell reference, click the disclosure Â triangle of the inserted reference and choose an option from the pop-up menu. See “Distinguishing Absolute and Relative Cell References” on page 27 for more information.

In Numbers, the cell reference inserted uses names instead of reference tab notation unless the “Use header cell names as references” is deselected in the General pane of Numbers preferences. In Keynote and Pages, the cell reference inserted uses names instead of reference tab notation if referenced cells have headers.

To type a cell reference, place the insertion point in the Formula Editor or the formula m bar (Numbers only), and enter the cell reference using one of the formats listed in “Referring to Cells in Formulas” on page 24.

When you type a cell reference that includes the name of a header cell (all applications), table (Numbers only), or sheet (Numbers only), after typing 3 characters a list of suggestions pops up if the characters you typed match one or more names in your spreadsheet. You can select from the list or continue typing. To disable name suggestions in Numbers, choose Numbers > Preferences and deselect “Use header cell names as references” in the General pane.

Distinguishing Absolute and Relative Cell References

Use absolute and relative forms of a cell reference to indicate the cell to which you want the reference to point if you copy or move its formula.

If a cell reference is relative (A1): When its formula moves, it stays the same. However, when the formula is cut or copied and then pasted, the cell reference changes so that it retains the same position relative to the formula cell. For example, if a formula containing A1 appears in C4 and you copy the formula and paste it in C5, the cell reference in C5 becomes A2.

If the row and column components of a cell reference are absolute ($A$1): When its formula is copied, the cell reference doesn’t change. You use the dollar sign ($) to designate a row or column component absolute. For example, if a formula containing $A$1 appears in C4 and you copy the formula and paste it in C5 or in D5, the cell reference in C5 or D5 remains $A$1.

If the row component of a cell reference is absolute (A$1): The column component is relative and may change to retain its position relative to the formula cell. For example, if a formula containing A$1 appears in C4 and you copy the formula and paste it in D5, the cell reference in D5 becomes B$1.

Chapter 1 Using Formulas in Tables 27

If the column component of a cell reference is absolute ($A1): The row component

is relative and may change to retain its position relative to the formula cell. For example, if a formula containing $A1 appears in C4 and you copy the formula and paste it in C5 or in D5, the cell reference in C5 and D5 becomes $A2.

Here are ways to specify the absoluteness of cell reference components:

Type the cell reference using one of the conventions described above. m

Click the disclosure triangle of a cell reference and choose an option from the pop-up m menu.

Select a cell reference and press Command-K to cycle through options. m

Using Operators in Formulas

Use operators in formulas to perform arithmetic operations and to compare values:

Â Arithmetic operators perform arithmetic operations, such as addition and subtraction,

and return numerical results. See “The Arithmetic Operators” on page 28 to learn more.

Â Comparison operators compare two values and return TRUE or FALSE. See “The

Comparison Operators” on page 29 to learn more.

The Arithmetic Operators

You can use arithmetic operators to perform arithmetic operations in formulas.

When you want to Use this arithmetic operator For example, if A2 contains 20

and B2 contains 2, the formula

Add two values + (plus sign) A2 + B2 returns 22.

Subtract one value from another value

Multiply two values * (asterisk) A2 * B2 returns 40.

Divide one value by another value

Raise one value to the power of another value

Calculate a percentage % (percent sign) A2% returns 0.2, formatted for

– (minus sign) A2 – B2 returns 18.

/ (forward slash) A2 / B2 returns 10.

^ (caret) A2 ^ B2 returns 400.

display as 20%.

Using a string with an arithmetic operator returns an error. For example, 3 + “hello” is not a correct arithmetic operation.

28 Chapter 1 Using Formulas in Tables

The Comparison Operators

You can use comparison operators to compare two values in formulas. Comparison

operations always return the values TRUE or FALSE. Comparison operators can also

used to build the conditions used by some functions. See “condition” in the table “Syntax Elements and Terms Used In Function Denitions” on page 34

When you want to determine whether

Two values are equal = A2 = B2 returns FALSE.

Two values aren’t equal <> A2 <> B2 returns TRUE.

The rst value is greater than

the second value

The rst value is less than the

second value

The rst value is greater than or equal to the second value

The rst value is less than or equal to the second value

Use this comparison operator For example, if A2 contains 20

and B2 contains 2, the formula

> A2 > B2 returns TRUE.

< A2 < B2 returns FALSE.

>= A2 >= B2 returns TRUE.

<= A2 <= B2 returns FALSE.

Strings are larger than numbers. For example, “hello” > 5 returns TRUE.

TRUE and FALSE can be compared with each other, but not with numbers or strings. TRUE > FALSE, and FALSE < TRUE, because TRUE is interpreted as 1 and FALSE is interpreted as 0. TRUE = 1 returns FALSE, and TRUE = “SomeText” returns FALSE.

Comparison operations are used primarily in functions, such as IF, which compare two values and then perform other operations depending on whether the comparison

returns TRUE or FALSE. For more information about this topic, choose Help > “iWork

Formulas and Functions Help” or Help > “iWork Formulas and Functions User Guide.”

Chapter 1 Using Formulas in Tables 29

The String Operator and the Wildcards

The string operator can be used in formulas and wildcards can be used in conditions.

When you want to Use this string operator or

wildcard

Concatenate strings or the contents of cells

Match a single character ? “ea?” will match any string

Match any number of characters * “*ed” will match a string of any

Literally match a wildcard

character

& “abc”&”def” returns “abcdef”

~ “~?” will match the question

For example

“abc”&A1 returns “abc2” if cell A1 contains 2.

A1&A2 returns “12” if cell A1 contains 1 and cell A2 contains 2.

beginning with “ea” and containing exactly one additional character.

length ending with “ed”.

mark, instead of using the

question mark to match any

single character.

For more information on the use of wildcards in conditions, see “Specifying Conditions and Using Wildcards” on page 360.

Copying or Moving Formulas and Their Computed Values

Here are techniques for copying and moving cells related to a formula:

To copy the computed value in a formula cell but not the formula, select the cell, m choose Edit > Copy, select the cell you want to hold the value, and then choose Edit > Paste Values.

To copy or move a formula cell or a cell that a formula refers to, follow the instructions m in “Copying and Moving Cells” in Numbers Help or the Numbers User Guide.

In Numbers, if the table is large and you want to move the formula to a cell that’s out of view, select the cell, choose Edit > “Mark for Move,” select the other cell, and then choose Edit > Move. For example, if the formula =A1 is in cell D1 and you want to move the same formula to cell X1, select D1, choose Edit > “Mark for Move,” select X1, and then choose Edit > Move. The formula =A1 appears in cell X1.

If you copy or move a formula cell: Change cell references as “Distinguishing Absolute and Relative Cell References” on page 27 describes if needed.

If you move a cell that a formula refers to: The cell reference in the formula is automatically updated. For example, if a reference to A1 appears in a formula and you move A1 to D95, the cell reference in the formula becomes D95.

30 Chapter 1 Using Formulas in Tables

+ 342 hidden pages