Apple iWork User Manual

iWork Formulas and

Functions User Guide

KKApple Inc.

© 2009 Apple Inc. All rights reserved.

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 effort has been made to ensure that the information in this manual is accurate. Apple is not responsible for printing or clerical errors.

Apple

1 Infinite 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

Contents

13 Preface:  Welcome to iWork Formulas & Functions

15 Chapter 1:   Using Formulas in Tables

15 The Elements of Formulas

17Performing Instant Calculations in Numbers

18Using Predefined Quick Formulas

19Creating Your Own Formulas

19Adding and Editing Formulas Using the Formula Editor

20Adding and Editing Formulas Using the Formula Bar

21Adding Functions to Formulas

23Handling Errors and Warnings in Formulas

24Removing Formulas

24 Referring to Cells in Formulas

26Using the Keyboard and Mouse to Create and Edit Formulas

27Distinguishing Absolute and Relative Cell References

28Using Operators in Formulas

28The Arithmetic Operators

29The Comparison Operators

30The String Operator and the Wildcards

30Copying or Moving Formulas and Their Computed Values

31Viewing All Formulas in a Spreadsheet

32Finding and Replacing Formula Elements

33Chapter 2:   Overview of the iWork Functions

33An Introduction to Functions

34Information About Functions

34 Syntax Elements and Terms Used In Function Definitions

36 Value Types

40Listing of Function Categories

41Pasting from Examples in Help

42Chapter 3:   Date and Time Functions

42Listing of Date and Time Functions

44DATE

3

45 DATEDIF

47DATEVALUE

47DAY

48DAYNAME

49DAYS360

50EDATE

51EOMONTH

51HOUR

52MINUTE

53MONTH

54MONTHNAME

54NETWORKDAYS

55NOW

56SECOND

56TIME

57TIMEVALUE

58TODAY

59WEEKDAY

60WEEKNUM

61WORKDAY

62YEAR

63YEARFRAC

64Chapter 4:   Duration Functions

64Listing of Duration Functions

65DUR2DAYS

65DUR2HOURS

66DUR2MILLISECONDS

67DUR2MINUTES

68DUR2SECONDS

69DUR2WEEKS

70DURATION

71STRIPDURATION

72Chapter 5:   Engineering Functions

72Listing of Engineering Functions

73BASETONUM

74BESSELJ

75BESSELY

76BIN2DEC

77BIN2HEX

78BIN2OCT

79CONVERT

4	Contents

80 Supported Conversion Units

80 Weight and mass

80 Distance

80Duration

81Speed

81	Pressure
81	Force

81Energy

82Power

82	Magnetism
82	Temperature

82Liquid

83Metric prefixes

83DEC2BIN

84DEC2HEX

85DEC2OCT

86DELTA

87ERF

87ERFC

88GESTEP

89HEX2BIN

90HEX2DEC

91HEX2OCT

92NUMTOBASE

93OCT2BIN

94OCT2DEC

95OCT2HEX

96Chapter 6:   Financial Functions

96Listing of Financial Functions

99ACCRINT

101ACCRINTM

103BONDDURATION

104BONDMDURATION

105COUPDAYBS

107COUPDAYS

108COUPDAYSNC

109COUPNUM

110CUMIPMT

112CUMPRINC

114DB

116DDB

117DISC

Contents	5

119EFFECT

120FV

122INTRATE

123IPMT

125IRR

126ISPMT

128MIRR

129NOMINAL

130NPER

132NPV

134PMT

135PPMT

137PRICE

138PRICEDISC

140PRICEMAT

141PV

144RATE

146RECEIVED

147SLN

148SYD

149VDB

150YIELD

152YIELDDISC

153YIELDMAT

155 Chapter 7:   Logical and Information Functions

155Listing of Logical and Information Functions

156AND

157FALSE

158IF

159IFERROR

160ISBLANK

161ISERROR

162ISEVEN

163ISODD

164NOT

165OR

166TRUE

167Chapter 8:   Numeric Functions

167	Listing of Numeric Functions
170	ABS
170	CEILING

6	Contents

172COMBIN

173EVEN

174EXP

174FACT

175FACTDOUBLE

176FLOOR

177GCD

178INT

179LCM

179LN

180LOG

181LOG10

182MOD

183MROUND

184MULTINOMIAL

185ODD

186PI

186POWER

187PRODUCT

188QUOTIENT

189RAND

189RANDBETWEEN

190ROMAN

191ROUND

192ROUNDDOWN

193ROUNDUP

195SIGN

195SQRT

196SQRTPI

196SUM

197SUMIF

198SUMIFS

200SUMPRODUCT

201SUMSQ

202SUMX2MY2

203SUMX2PY2

204SUMXMY2

204TRUNC

206 Chapter 9:   Reference Functions

206Listing of Reference Functions

207ADDRESS

209 AREAS

Contents	7

209CHOOSE

210COLUMN

211COLUMNS

211HLOOKUP

213HYPERLINK

214INDEX

216INDIRECT

217LOOKUP

218MATCH

219OFFSET

221ROW

221ROWS

222TRANSPOSE

223VLOOKUP

225 Chapter 10:   Statistical Functions

225 Listing of Statistical Functions

230AVEDEV

231AVERAGE

232AVERAGEA

233AVERAGEIF

234AVERAGEIFS

236BETADIST

237BETAINV

238BINOMDIST

239CHIDIST

239CHIINV

240CHITEST

242CONFIDENCE

242CORREL

244COUNT

245COUNTA

246COUNTBLANK

247COUNTIF

248COUNTIFS

250COVAR

252CRITBINOM

253DEVSQ

253EXPONDIST

254FDIST

255FINV

256FORECAST

257FREQUENCY

8	Contents

259GAMMADIST

260GAMMAINV

260GAMMALN

261GEOMEAN

262HARMEAN

262INTERCEPT

264LARGE

265LINEST

267Additional Statistics

268LOGINV

269LOGNORMDIST

270MAX

270MAXA

271MEDIAN

272MIN

273MINA

274MODE

275NEGBINOMDIST

276NORMDIST

277NORMINV

277NORMSDIST

278NORMSINV

279PERCENTILE

280PERCENTRANK

281PERMUT

282POISSON

282PROB

284QUARTILE

285RANK

287SLOPE

288SMALL

289STANDARDIZE

290STDEV

291STDEVA

293STDEVP

294STDEVPA

296TDIST

297TINV

297TTEST

298VAR

300VARA

302VARP

303VARPA

Contents	9

305ZTEST

306Chapter 11:  Text Functions

306Listing of Text Functions

308CHAR

308CLEAN

309CODE

310CONCATENATE

311DOLLAR

312EXACT

312FIND

313FIXED

314LEFT

315LEN

316LOWER

316MID

317PROPER

318REPLACE

319REPT

319RIGHT

320SEARCH

322SUBSTITUTE

323T

323TRIM

324UPPER

325VALUE

326 Chapter 12:  Trigonometric Functions

326Listing of Trigonometric Functions

327ACOS

328ACOSH

329ASIN

329ASINH

330ATAN

331ATAN2

332ATANH

333COS

334COSH

334DEGREES

335RADIANS

336SIN

337SINH

338TAN

10	Contents

339TANH

340Chapter 13:   Additional Examples and Topics

340Additional Examples and Topics Included

341Common Arguments Used in Financial Functions

348 Choosing Which Time Value of Money Function to Use 348 Regular Cash Flows and Time Intervals

350Irregular Cash Flows and Time Intervals

351Which Function Should You Use to Solve Common Financial Questions?

353Example of a Loan Amortization Table

355More on Rounding

358Using Logical and Information Functions Together

358Adding Comments Based on Cell Contents

360Trapping Division by Zero

360Specifying Conditions and Using Wildcards

362Survey Results Example

365 Index

Contents	11

Welcome to iWork Formulas & Functions

Preface

iWork comes with more than 250 functions you can use to simplify statistical, financial, 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.

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.”

13

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 first 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	1

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 specific 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 first column.

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

16: A numeric constant.

ÂÂ Functions are predefined, 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.

15

=SUM(A2:A10): A formula that uses the function SUM to add the values in a range of cells (nine cells in the first 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	“Performing Instant Calculations in
	value, maximum value, and count of values in	Numbers” (page 17)
	selected cells and optionally save the formula
	used to derive these values in Numbers
	Quickly add a formula that displays the sum,	“Using Predefined Quick Formulas” (page 18)
	average, minimum value, maximum value, count,
	or product of values in selected cells
	Use tools and techniques to create and modify	“Adding and Editing Formulas Using the Formula
	your formulas in Numbers	Editor” (page 19)
		“Adding and Editing Formulas Using the Formula
		Bar” (page 20)
		“Adding Functions to Formulas” (page 21)
		“Removing Formulas” (page 24)
	Use tools and techniques to create and modify	“Adding and Editing Formulas Using the Formula
	your formulas in Pages and Keynote	Editor” (page 19)
	Use the hundreds of iWork functions and review	Help > “iWork Formulas and Functions Help”
	examples illustrating ways to apply the functions	Help > “iWork Formulas and Functions User
	in financial, engineering, statistical, and other
		Guide”
	contexts
	Add cell references of different kinds to a formula	“Referring to Cells in Formulas” (page 24)
	in Numbers	“Using the Keyboard and Mouse to Create and
		Edit Formulas” (page 26)
		“Distinguishing Absolute and Relative Cell
		References” (page 27)
	Use operators in formulas	“The Arithmetic Operators” (page 28)
		“The Comparison Operators” (page 29)
		“The String Operator and the Wildcards” (page 30)
	Copy or move formulas or the value they	“Copying or Moving Formulas and Their
	compute among table cells	Computed Values” (page 30)
	Find formulas and formula elements in Numbers	“Viewing All Formulas in a Spreadsheet” (page 31)
		“Finding and Replacing Formula
		Elements” (page 32)

16	Chapter 1    Using Formulas in Tables

Performing Instant Calculations in Numbers

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:

1Select 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.

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

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.

2To perform another set of instant calculations, select different cells.

If you find 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 Predefined 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:

mm To use selected values in a column or a row, select the cells. 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.

If the cells are in the same column, the result is placed in the first 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 first 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.

mm To use all the values in a column’s body cells, first 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

mm To use all the values in a row, first click the row’s header cell or reference tab.Then, 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 Predefined 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	“Adding and Editing Formulas Using the Formula
formula in Numbers	Bar” (page 20)

Use the Function Browser to quickly add functions to formulas when using the Formula Editor or the formula bar

“Adding Functions to Formulas” (page 21)

Detect an erroneous formula	“Handling Errors and Warnings in
	Formulas” (page 23)

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 field that holds your formula. As you add cell references, operators, functions, or constants to a formula, they look like this in the Formula Editor.

A reference to a							The Subtraction		References to cells
range of three cells.							operator.		using their names.
All formulas must begin
with the equal sign.
					The Sum function.

Here are ways to work with the Formula Editor:

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

ÂÂ 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.

Chapter 1    Using Formulas in Tables	19

ÂÂ In Numbers only, select a table cell and then choose Insert > Function > Formula 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.

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

mm To build your formula, do the following:

ÂÂ To add an operator or a constant to the text field, place the insertion point and type. You can use the arrow keys to move the insertion point around in the text field. 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 different operator if needed.

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

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

mm To remove an element from the text field, select the element and press Delete.

mm To accept changes, press Return, press Enter, or click the Accept button in the Formula 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.

										References to cells
All formulas must begin					The Sum function.					using their names.
with the equal sign.
								The Subtraction operator.
						A reference to a
						range of three cells.

Here are ways to work with the formula bar:

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

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

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 different 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.

mm To increase or decrease the display size of formula elements in the formula bar, choose 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-fit the formula.

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

mm To save changes, press Return, press Enter, or click the Accept button above the 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 predefined, 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 financial 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 field of the Formula Editor or into the formula bar (Numbers only), the Function Browser offers a convenient way to add a function to a formula.

Select a category
to view functions in
that category.	Search for a function.

Select a function to view information about it.

Insert the selected function.

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:

1In 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 different operator if needed.

22	Chapter 1    Using Formulas in Tables

2In Pages or Keynote, choose Insert > Function > Show Function Browser to open 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.

3Select a function category.

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

5In the Formula Editor or formula bar (Numbers only), replace each argument placeholder in the inserted function with a value.

Click to see a list of valid values.

Help for the “issue” argument		Placeholders for optional
appears when the pointer is over		arguments are light gray.
the placeholder.

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: mm Click the icon.

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:

1Select the cell.

2Press 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 different sheet.

24	Chapter 1    Using Formulas in Tables

Cell references have different 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

Any cell in the table containing the formula

Use this format	Example
The reference tab letter followed	C55 refers to the 55th row in the
by the reference tab number for	third column.
the cell

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

The column name followed by the row name

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

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

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

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

A range of cells	A colon (:) between the first
	and last cell in the range, using
	reference tab notation to
	identify the cells

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

All the cells in a row	The row name or row-	1:1 refers to all the cells in the
	number:row-number	first row.
All the cells in a column	The column letter or name	C refers to all the cells in the
		third column.

All the cells in a range of rows	A colon (:) between the row
	number or name of the first and
	last row in the range

2:6 refers to all the cells in five rows.

All the cells in a range of columns

A colon (:) between the column letter or name of the first and last column in the range

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

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

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 identifier

Table 2::B5 refers to cell B5 in a table named Table 2. Table

2::2006 Class Enrollment refers to a cell by name.

In Numbers, a cell in a table on another sheet

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 identifier

Sheet 2::Table 2::2006 Class Enrollment refers to a cell in a table named Table 2 on a sheet named Sheet 2.

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:

mm To use a keyboard shortcut to enter a cell reference, place the insertion point in the 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 first 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 different 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.

mm To use the mouse to enter a cell reference, place the insertion point in the Formula 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 different table on the same or a different 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.

mm To type a cell reference, place the insertion point in the Formula Editor or the formula 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: mm Type the cell reference using one of the conventions described above.

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

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

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	– (minus sign)	A2	– B2 returns 18.
value
Multiply two values	* (asterisk)	A2	* B2 returns 40.
Divide one value by another	/ (forward slash)	A2	/ B2 returns 10.
value
Raise one value to the power of	^ (caret)	A2	^ B2 returns 400.
another value
Calculate a percentage	% (percent sign)	A2% returns 0.2, formatted for
		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 Definitions” on page 34

When you want to determine	Use this comparison operator	For example, if A2 contains 20
whether		and B2 contains 2, the formula
Two values are equal	=	A2	= B2 returns FALSE.
Two values aren’t equal	<>	A2	<> B2 returns TRUE.
The first value is greater than	>	A2	> B2 returns TRUE.
the second value
The first value is less than the	<	A2	< B2 returns FALSE.
second value
The first value is greater than or	>=	A2	>= B2 returns TRUE.
equal to the second value
The first value is less than or	<=	A2	<= B2 returns FALSE.
equal to the second value

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	For example
	wildcard
Concatenate strings or the	&	“abc”&”def” returns “abcdef”
contents of cells		“abc”&A1 returns “abc2” if cell A1
		contains 2.
		A1&A2 returns “12” if cell A1
		contains 1 and cell A2 contains 2.
Match a single character	?	“ea?” will match any string
		beginning with “ea” and
		containing exactly one
		additional character.
Match any number of characters	*	“*ed” will match a string of any
		length ending with “ed”.
Literally match a wildcard	~	“~?”will match the question
character		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:

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

mm To copy or move a formula cell or a cell that a formula refers to, follow the instructions 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