Apple IWork \'09 Formulas And Functions User Guide Manual I Work

2009-09-28

User Manual: Apple iWork \'09 iWork \'09 Formulas and Functions User Guide

Open the PDF directly: View PDF .
Page Count: 372 [warning: Documents this large are best viewed by clicking the View PDF Link!]

Formulas and Functions

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

Contents

4 Contents

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

Contents 5

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

6 Contents

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

Contents 7

172 COMBIN

173 EVEN

174 EXP

174 FACT

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

8 Contents

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 COVAR

252 CRITBINOM

253 DEVSQ

253 EXPONDIST

254 FDIST

255 FINV

256 FORECAST

257 FREQUENCY

Contents 9

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

10 Contents

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

Contents 11

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

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.

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

Preface

Welcome to iWork Formulas &

Functions

14 Preface Welcome to iWork Formulas & Functions

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.

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.

Using Formulas in Tables

=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

“Performing Instant Calculations in

Numbers” (page 17 )

Quickly add a formula that displays the sum,

average, minimum value, maximum value, count,

or product of values in selected cells

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

Use tools and techniques to create and modify

your formulas in Numbers

“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)

Use tools and techniques to create and modify

your formulas in Pages and Keynote

“Adding and Editing Formulas Using the Formula

Editor” (page 19)

Use the hundreds of iWork functions and review

examples illustrating ways to apply the functions

in nancial, engineering, statistical, and other

contexts

Help > “iWork Formulas and Functions Help”

Help > “iWork Formulas and Functions User

Guide”

Add cell references of dierent kinds to a formula

in Numbers

“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)

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

compute among table cells

“Copying or Moving Formulas and Their

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

Chapter 1 Using Formulas in Tables 17

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:

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.

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.

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.

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 mall 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

Chapter 1 Using Formulas in Tables 19

To use mall the values in a row, rst click the row’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 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

“Adding and Editing Formulas Using the Formula

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

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.

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 Â

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.

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.

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

Chapter 1 Using Formulas in Tables 21

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

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

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

function to a formula.

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.

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

Chapter 1 Using Formulas in Tables 23

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

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.

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.

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.

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

Chapter 1 Using Formulas in Tables 25

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

The reference tab letter followed

by the reference tab number for

the cell

C55 refers to the 55th row in the

third column.

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 rst

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-

number:row-number

1:1 refers to all the cells in the

rst 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 rst and

last row in the range

2:6 refers to all the cells in ve

rows.

All the cells in a range of

columns

A colon (:) between the column

letter or name of the rst 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

identier

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

identier

Sheet 2::Table 2::2006 Class

Enrollment refers to a cell in a

table named Table 2 on a sheet

named Sheet 2.

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

Chapter 1 Using Formulas in Tables 27

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.

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

– (minus sign) A2 – B2 returns 18.

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

Divide one value by another

value

/ (forward slash) A2 / B2 returns 10.

Raise one value to the power of

another value

^ (caret) A2 ^ B2 returns 400.

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

Chapter 1 Using Formulas in Tables 29

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

Use this comparison operator For example, if A2 contains 20

and B2 contains 2, the formula

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

> A2 > B2 returns TRUE.

The rst value is less than the

second value

<A2 < B2 returns FALSE.

The rst value is greater than or

equal to the second value

>= A2 >= B2 returns TRUE.

The rst value is less than or

equal to the second value

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

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

For example

Concatenate strings or the

contents of cells

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

“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

character

~“~?” will match the question

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

Chapter 1 Using Formulas in Tables 31

Viewing All Formulas in a Spreadsheet

In Numbers, to view a list of all the formulas in a spreadsheet, choose View > Show

Formula List or click on the formula list button in the toolbar.

Location: Identies the sheet and table in which the formula is located.

Results: Displays the current value computed by the formula.

Formula: Shows the formula.

Here are ways to use the formula list window:

To identify the cell containing a formula, click the formula. The table is shown above m

the formula list window with the formula cell selected.

To edit the formula, double-click it. m

To change the size of the formula list window, drag the selection handle in its upper m

right corner up or down.

To nd formulas that contain a particular element, type the element in the search eld m

and press Return.

Finding and Replacing Formula Elements

In Numbers, using the Find & Replace window, you can search through all of a

spreadsheet’s formulas to nd and optionally change elements.

Here are ways to open the Find & Replace window:

Choose Edit > Find > Show Search, and then click Find & Replace. m

Choose View > Show Formula List, and then click Find & Replace. m

Find: Type the formula element (cell reference, operator, function, and so on) you

want to nd.

In: Choose Formulas Only from this pop-up menu.

Match case: Select to nd only elements whose uppercase and lowercase letters

match exactly what’s in the Find eld.

Whole words: Select to nd only elements whose entire contents match what’s in

the Find eld.

Replace: Optionally type what you want to use to replace what’s in the Find eld.

Repeat search (loop): Select to continue looking for what’s in the Find eld even

after the entire spreadsheet has been searched.

Next or Previous: Click to search for the next or previous instance of what’s in the

Find eld. When an element is found, the Formula Editor opens and displays the

formula containing the instance of the element.

Replace All: Click to replace all instances of what’s in the Find eld with what’s in

the Replace eld.

Replace: Click to replace the current instance of what’s in the Find eld with what’s

in the Replace eld.

Replace & Find: Click to replace the current instance of what’s in the Find eld and

to locate the next instance.

32 Chapter 1 Using Formulas in Tables

This chapter introduces the functions available in iWork.

An Introduction to Functions

A function is a named operation that you can include in a formula to perform a

calculation or to manipulate data in a table cell.

iWork provides functions that do things such as perform mathematical or nancial

operations, retrieve cell values based on a search, manipulate strings of text, or get the

current date and time. Each function has a name followed by one or more arguments

enclosed in parentheses. You use arguments to provide the values that the function

needs to perform its work.

For example, the following formula contains a function named SUM with a single

argument (a range of cells) that adds the values in column A, rows 2 through 10:

=SUM(A2:A10)

The number and types of arguments vary for each function. The number and

description of the arguments are included with the function in the alphabetical

“Listing of Function Categories” on page 40. The descriptions also include additional

information and examples for each function.

Overview of the iWork Functions

Information About Functions

For further information on Go to

Syntax used in function denitions “Syntax Elements and Terms Used In Function

Denitions” on page 34

Types of arguments that are used by functions “Value Types” on page 36

Categories of functions, such as duration and

statistical

“Listing of Function Categories” on page 40.

Functions are listed alphabetically within each

category.

Arguments common to several nancial functions “Common Arguments Used in Financial

Functions” on page 341

Supplemental examples and topics “Additional Examples and Topics Included” on

page 340

Syntax Elements and Terms Used In Function Denitions

Functions are described using specic syntax elements and terms.

Term or symbol Meaning

uppercase text Function names are shown in all uppercase text.

However, a function name can be entered using

any combination of uppercase or lowercase

letters.

parentheses Function arguments are enclosed in parentheses.

Parentheses are required, although in limited

circumstances iWork can automatically insert the

nal closing parenthesis for you.

italic text Italic text indicates that you must replace the

argument name with a value the function will

use to calculate a result. Arguments have a value

type, such as “number,” “date/time,” or “string.”

Value types are discussed in “Value Types” on

page 36.

commas and semicolons The syntax descriptions for functions use commas

to separate arguments. If your Language and Text

preferences (Mac OS X version 10.6 or higher) or

International preferences (earlier versions of Max

OS X) are set up to use the comma as a decimal

separator, separate arguments using a semicolon

instead of a comma.

34 Chapter 2 Overview of the iWork Functions

Chapter 2 Overview of the iWork Functions 35

Term or symbol Meaning

ellipsis (…) An argument followed by an ellipsis can be

repeated as many times as necessary. Any

limitations are described in the argument

denition.

array An array is a sequence of values used by a

function, or returned by a function.

array constant An array constant is a set of values enclosed

within braces ({}) and is typed directly into the

function. For example, {1, 2, 5, 7} or {“12/31/2008”,

“3/15/2009”, “8/20/2010”}.

array function A small number of functions are described as

“array function,” meaning the function returns an

array of values rather than a single value. These

functions are commonly used to provide values

to another function.

Boolean expression A Boolean expression is an expression that

evaluates to the Boolean value TRUE or FALSE.

constant A constant is a value specied directly within

the formula that contains no function calls

or references. For example, in the formula

=CONCATENATE(”cat”, “s”), “cat” and “s” are

constants.

modal argument A modal argument is one that can have one of

several possible specied values. Usually, modal

arguments specify something about the type of

calculation the function should perform or about

the type of data the function should return.

If a modal argument has a default value, it is

specied in the argument description.

condition A condition is an expression that can include

comparison operators, constants, the ampersand

string operator, and references. The contents

of the condition must be such that the result

of comparing the condition to another value

results in the Boolean value TRUE or FALSE.

Further information and examples are included in

“Specifying Conditions and Using Wildcards” on

page 360.

Value Types

A function argument has a type, which species what type of information the

argument can contain. Functions also return a value of a particular type.

Value Type Description

any If an argument is specied as “any,” it can be a

Boolean value, date/time value, duration value,

number value, or string value.

Boolean A Boolean value is a logical TRUE (1) or FALSE

(0) value or a reference to a cell containing or

resulting in a logical TRUE or FALSE value. It is

generally the result of evaluating a Boolean

expression, but a Boolean value can be specied

directly as an argument to a function or as the

content of a cell. A common use of a Boolean

value is to determine which expression is to be

returned by the IF function.

collection An argument that is specied as a collection can

be a reference to a single table cell range, an

array constant, or an array returned by an array

function. An argument specied as collection will

have an additional attribute dening the type of

values it can contain.

date/time This is a date/time value or a reference to a

cell containing a date/time value in any of the

formats supported by iWork. If a date/time value

is typed into the function, it should be enclosed

in quotation marks. You can choose to display

only a date or time in a cell, but all date/time

values contain both a date and a time.

Although dates can usually be entered directly

as strings (for example, “12/31/2010”), using the

DATE function insures the date will be interpreted

consistently regardless of the date format

selected in System Preferences (search for “date

format” in the System Preferences window).

36 Chapter 2 Overview of the iWork Functions

Chapter 2 Overview of the iWork Functions 37

Value Type Description

duration A duration is a length of time or a reference

to a cell containing a length of time. Duration

values consist of weeks (w or weeks), days (d or

days), hours (h or hours), minutes (m or minutes),

seconds (s or seconds), and milliseconds (ms or

milliseconds). A duration value can be entered in

one of two formats.

The rst format consists of a number, followed

by a time period (such as h for hours), optionally

followed by a space, and is repeated for other

time periods. You can use either the abbreviation

for specifying the period, such as “h”, or the full

name, such as “hours.” For example, 12h 5d 3m

represents a duration of 12 hours, 5 days, and 3

minutes. TIme periods do not have to be entered

in order and spaces are not required. 5d 5h is the

same as 5h5d. If typed directly into a formula, the

string should be enclosed in quotation marks, as

in “12h 5d 3m”.

A duration can also be entered as a series of

numbers delimited by colons. If this format is

used, the seconds argument should be included

and end with a decimal followed by the number

of milliseconds, which can be 0, if the duration

value could be confused with a date/time

value. For example, 12:15:30.0 would represent a

duration of 12 hours, 15 minutes, and 30 seconds,

whereas 12:15:30 would be 12:15:30 a.m. 5:00.0

would represent a duration of exactly 5 minutes.

If typed directly into a function, the string

should be enclosed in quotation marks, as in

“12:15:30.0” or “5:00.0”. If the cell is formatted to a

particular duration display, the duration units are

applied relative to that duration display and the

milliseconds need not be specied.

Value Type Description

list A list is a comma-separated sequence of other

values. For example, =CHOOSE(3, “1st”, “second”,

7, “last”). In some cases, the list is enclosed in

an additional set of parentheses. For example,

=AREAS((B1:B5, C10:C12)).

modal A modal value is a single value, often a number,

representing a specic mode for a modal

argument. “Modal argument” is dened in

“Syntax Elements and Terms Used In Function

Denitions” on page 34.

number A number value is a number, a numeric

expression, or a reference to a cell containing a

numeric expression. If the acceptable values of

a number are limited (for example, the number

must be greater than 0), this is included within

the argument description.

range value A range value is a reference to a single range of

cells (can be a single cell). A range value will have

an additional attribute dening the type of values

it should contain. This will be included within the

argument description.

38 Chapter 2 Overview of the iWork Functions

Chapter 2 Overview of the iWork Functions 39

Value Type Description

reference This is a reference to a single cell or a range

of cells. If the range is more than one cell, the

starting and ending cell are separated by a single

colon. For example, =COUNT(A3:D7).

Unless the cell name is unique within all tables,

the reference must contain the name of the table

if the reference is to a cell on another table. For

example, =Table 2::B2. Note that the table name

and cell reference are separated by a double

colon (::).

If the table is on another sheet, the sheet name

must also be included, unless the cell name is

unique within all the the sheets. For example,

=SUM(Sheet 2::Table 1::C2:G2). The sheet name,

table name and cell reference are separated by

double colons.

Some functions that accept ranges can operate

on ranges that span multiple tables. Assume

that you have a le open that has one sheet

containing three tables (Table 1, Table 2, Table

3). Assume further that cell C2 in each table

contains the number 1. The table-spanning

formula =SUM(Table 1:Table 2 :: C2) would sum

cell C2 in all tables between Table 1 and Table 2.

So the result would be 2. If you drag Table 3 so

that it appears between Table 1 and Table 2 in

the sidebar, the function will return 3, since it is

now summing cell C2 in all three tables (Table 3

is between Table 1 and Table 2).

string A string is zero or more characters, or a reference

to a cell containing one or more characters. The

characters can consist of any printable characters,

including numbers. If a string value is typed into

the formula, it must be enclosed in quotation

marks. If the string value is somehow limited (for

example, the string must represent a date), this is

included within the argument description.

Listing of Function Categories

There are several categories of functions. For example, some functions perform

calculations on date/time values, logical functions give a Boolean (TRUE or FALSE)

result, and other functions perform nancial calculations. Each of the categories of

functions is discussed in a separate chapter.

“Listing of Date and Time Functions” on page 42

“Listing of Duration Functions” on page 64

“Listing of Engineering Functions” on page 72

“Listing of Financial Functions” on page 96

“Listing of Logical and Information Functions” on page 155

“Listing of Numeric Functions” on page 167

“Listing of Reference Functions” on page 206

“Listing of Statistical Functions” on page 225

“Listing of Text Functions” on page 306

“Listing of Trigonometric Functions” on page 326

40 Chapter 2 Overview of the iWork Functions

Chapter 2 Overview of the iWork Functions 41

Pasting from Examples in Help

Many of the examples in help can be copied and pasted directly into a table or,

in Numbers, onto a blank canvas. There are two groups of examples which can be

copied from help and pasted into a table. The rst are individual examples included

within help. All such examples begin with an equal sign (=). In the help for the HOUR

function, there are two such examples.

To use one of these examples, select the text beginning with the equal sign through

the end of the example.

Once this text is highlighted, you can copy it and then paste it into any cell in a table.

An alternative to copy and paste is to drag the selection from the example and drop it

onto any cell in a table.

The second kind of example that can be copied are example tables included within

help. This is the help example table for ACCRINT.

To use an example table, select all the cells in the example table, including the rst row.

Once this text is highlighted, you can copy it and then paste it into any cell in a table

or onto a blank canvas in a Numbers sheet. Drag and drop cannot be used for this

type of example.

The date and time functions help you work with dates and

times to solve problems such as nding the number of

working days between two dates or nding the name of the

day of the week a date will fall on.

Listing of Date and Time Functions

iWork includes these date and time functions for use with tables.

Function Description

“DATE” (page 44) The DATE function combines separate values for

year, month, and day and returns a date/time

value. Although dates can usually be entered

directly as strings (for example, “12/31/2010”),

using the DATE function ensures the date will be

interpreted consistently regardless of the date

format specied in System Preferences (search for

“date format” in the System Preferences window).

“DATEDIF” (page 45) The DATEDIF function returns the number of

days, months, or years between two dates.

“DATEVALUE” (page 47)The DATEVALUE function converts a date

text string and returns a date/time value. This

function is provided for compatibility with other

spreadsheet programs.

“DAY” (page 47) The DAY function returns the day of the month

for a given date/time value.

“DAYNAME” (page 48) The DAYNAME function returns the name of

the day of the week from a date/time value or a

number. Day 1 is Sunday.

“DAYS360” (page 49) The DAYS360 function returns the number of

days between two dates based on twelve 30-day

months and a 360-day year.

Date and Time Functions

Chapter 3 Date and Time Functions 43

Function Description

“EDATE” (page 50) The EDATE function returns a date that is some

number of months before or after a given date.

“EOMONTH” (page 51 ) The EOMONTH function returns a date that is the

last day of the month some number of months

before or after a given date.

“HOUR” (page 51 ) The HOUR function returns the hour for a given

date/time value.

“MINUTE” (page 52) The MINUTE function returns the minutes for a

given date/time value.

“MONTH” (page 53) The MONTH function returns the month for a

given date/time value.

“MONTHNAME” (page 54) The MONTHNAME function returns the name of

the month from a number. Month 1 is January.

“NETWORKDAYS” (page 54) The NETWORKDAYS function returns the number

of working days between two dates. Working

days exclude weekends and any other specied

dates.

“NOW” (page 55) The NOW function returns the current date/time

value from the system clock.

“SECOND” (page 56) The SECOND function returns the seconds for a

given date/time value.

“TIME” (page 56) The TIME function converts separate values for

hours, minutes, and seconds into a date/time

value.

“TIMEVALUE” (page 57)The TIMEVALUE function returns the time as a

decimal fraction of a 24-hour day from a given

date/time value or from a text string.

“TODAY” (page 58) The TODAY function returns the current system

date. The time is set to 12:00 a.m.

Function Description

“WEEKDAY” (page 59) The WEEKDAY function returns a number that is

the day of the week for a given date.

“WEEKNUM” (page 60) The WEEKNUM function returns the number of

the week within the year for a given date.

“WORKDAY” (page 61) The WORKDAY function returns the date that is

the given number of working days before or after

a given date. Working days exclude weekends

and any other dates specically excluded.

“YEAR” (page 62) The YEAR function returns the year for a given

date/time value.

“YEARFRAC” (page 63)The YEARFRAC function nds the fraction of a

year represented by the number of whole days

between two dates.

DATE

The DATE function combines separate values for year, month, and day and returns a

date/time value. Although dates can usually be entered directly as strings (for example,

“12/31/2010”), using the DATE function ensures the date will be interpreted consistently

regardless of the date format specied in System Preferences (search for “date format”

in the System Preferences window).

DATE(year, month, day)

Âyear: The year to include in the value returned. year is a number value. The value

isn’t converted. If you specify 10, the year 10 is used, not the year 1910 or 2010.

Âmonth: The month to include in the value returned. month is a number and should

be in the range 1 to 12.

Âday: The day to include in the value returned. day is a number value and should be

in the range 1 to the number of days in month.

Examples

If A1 contains 2014, A2 contains 11, and A3 contains 10:

=DATE(A1, A2, A3) returns Nov 10, 2014, which is displayed according to the cell’s current format.

=DATE(A1, A3, A2) returns Oct 11, 2014.

=DATE(2012, 2, 14) returns Feb 14, 2012.

Related Topics

For related functions and additional information, see:

“DURATION” on page 70

44 Chapter 3 Date and Time Functions

Chapter 3 Date and Time Functions 45

“TIME” on page 56

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

DATEDIF

The DATEDIF function returns the number of days, months, or years between two

dates.

DATEDIF(start-date, end-date, calc-method)

Âstart-date: The starting date. start-date is a date/time value.

Âend-date: The ending date. end-date is a date/time value.

Âcalc-method: Species how to express the time dierence and how dates in

dierent years or months are handled.

“D”: Count the number of days between the start and end dates.

“M”: Count the number of months between the start and end dates.

“Y”: Count the number of years between the start and end dates.

“MD”: Count the days between the start and end dates, ignoring months and years.

The month in end-date is considered to be the month in start-date. If the starting

day is after the ending day, the count starts from the ending day as if it were in the

preceding month. The year of the end-date is used to check for a leap year.

“YM”: Count the number of whole months between the start and end dates,

ignoring the year. If the starting month/day is before the ending month/day, the

dates are treated as though they are in the same year. If the starting month/day is

after the ending month/day, the dates are treated as though they are in consecutive

years.

“YD”: Count the number of days between the start and end dates, ignoring the

year. If the starting month/day is before the ending month/day, the dates are treated

as though they are in the same year. If the starting month/day is after the ending

month/day, the dates are treated as though they are in consecutive years.

Examples

If A1 contains the date/time value 4/6/88 and A2 contains the date/time value 10/30/06:

=DATEDIF(A1, A2, “D”) returns 6781, the number of days between April 6, 1988, and October 30, 2006.

=DATEDIF(A1, A2, “M”) returns 222, the number of whole months between April 6, 1988, and October

30, 2006.

=DATEDIF(A1, A2, “Y”) returns 18, the number of whole years between April 6, 1988, and October 30,

2006.

=DATEDIF(A1, A2, “MD”) returns 24, the number of days between the sixth day of a month and the

thirtieth day of the same month.

=DATEDIF(A1, A2, “YM”) returns 6, the number of months between April and the following October in

any year.

=DATEDIF(A1, A2, “YD”) returns 207, the number of days between April 6 and the following October

30 in any year.

=DATEDIF(”04/06/1988”, NOW(), “Y”) & “ years, “ & DATEDIF(”04/06/1988”, NOW(), “YM”) & “ months, and

“ & DATEDIF(”04/06/1988”, NOW(), “MD”) & “ days” returns the current age of someone born on April 6,

1988.

Related Topics

For related functions and additional information, see:

“DAYS360” on page 49

“NETWORKDAYS” on page 54

“NOW” on page 55

“YEARFRAC” on page 63

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

46 Chapter 3 Date and Time Functions

Chapter 3 Date and Time Functions 47

DATEVALUE

The DATEVALUE function converts a date text string and returns a date/time value. This

function is provided for compatibility with other spreadsheet programs.

DATEVALUE(date-text)

Âdate-text: The date string to be converted. date-text is a string value. It must be a

date specied within quotations or a date/time value. If date-text is not a valid date,

an error is returned.

Examples

If cell B1 contains the date/time value August 2, 1979 06:30:00 and cell C1 contains the string

10/16/2008:

=DATEVALUE(B1) returns Aug 2, 1979, and is treated as a date value if referenced in other formulas.

The value returned is formatted according to the current cell format. A cell formatted as Automatic

uses the date format specied in System Preferences (search for “date format” in the System

Preferences window).

=DATEVALUE(C1) returns Oct 16, 2008.

=DATEVALUE(“12/29/1974”) returns Dec 29, 1979.

Related Topics

For related functions and additional information, see:

“DATE” on page 44

“TIME” on page 56

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

DAY

The DAY function returns the day of the month for a given date/time value.

DAY(date)

Âdate: The date the function should use. date is a date/time value. The time portion

is ignored by this function.

Examples

=DAY(”4/6/88 11:59:22 PM”) returns 6.

=DAY(“5/12/2009”) returns 12.

Related Topics

For related functions and additional information, see:

“DAYNAME” on page 48

“HOUR” on page 51

“MINUTE” on page 52

“MONTH” on page 53

“SECOND” on page 56

“YEAR” on page 62

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

DAYNAME

The DAYNAME function returns the name of the day of the week from a date/time

value or a number. Day 1 is Sunday.

DAYNAME(day-num)

Âday-num: The desired day of the week. day-num is a date/time value, or number

value in the range 1 to 7. If day-num has a decimal portion, it is ignored.

Examples

If B1 contains the date/time value August 2, 1979 06:30:00, C1 contains the string 10/16/2008, and D1

contains 6:

=DAYNAME(B1) returns Thursday.

=DAYNAME(C1) returns Thursday.

=DAYNAME(D1) returns Friday.

=DAYNAME(“12/29/1974”) returns Sunday.

48 Chapter 3 Date and Time Functions

Chapter 3 Date and Time Functions 49

Related Topics

For related functions and additional information, see:

“DAY” on page 47

“MONTHNAME” on page 54

“WEEKDAY” on page 59

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

DAYS360

The DAYS360 function returns the number of days between two dates based on

twelve 30-day months and a 360-day year.

DAYS360(start-date, end-date, use-euro-method)

Âstart-date: The starting date. start-date is a date/time value.

Âend-date: The ending date. end-date is a date/time value.

Âuse-euro-method: An optional value that species whether to use the NASD or

European method for dates falling on the 31st of a month.

NASD method (0, FALSE, or omitted): Use the NASD method for dates falling on

the 31st of a month.

EURO method (1 or TRUE): Use the European method for dates falling on the 31st

of a month.

Examples

=DAYS360(”12/20/2008”, “3/31/2009”) returns 101d.

=DAYS360(”2/27/2008”, “3/31/2009”,0) returns 394d.

=DAYS360(”2/27/2008”, “3/31/2009”,1) returns 393d, as the European calculation method is used.

Related Topics

For related functions and additional information, see:

“DATEDIF” on page 45

“NETWORKDAYS” on page 54

“YEARFRAC” on page 63

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

EDATE

The EDATE function returns a date that is some number of months before or after a

given date.

EDATE(start-date, month-oset)

Âstart-date: The starting date. start-date is a date/time value.

Âmonth-oset:The number of months before or after the starting date. month-oset

is a number value. A negative month-oset is used to specify a number of months

before the starting date and a positive month-oset is used to specify a number of

months after the starting date.

Examples

=EDATE(”1/15/2000”, 1) returns 2/15/2000, the date one month later.

=EDATE(”1/15/2000”, -24) returns 1/15/1998, the date 24 months earlier.

Related Topics

For related functions and additional information, see:

“EOMONTH” on page 51

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

50 Chapter 3 Date and Time Functions

Chapter 3 Date and Time Functions 51

EOMONTH

The EOMONTH function returns a date that is the last day of the month some number

of months before or after a given date.

EOMONTH(start-date, month-oset)

Âstart-date: The starting date. start-date is a date/time value.

Âmonth-oset:The number of months before or after the starting date. month-oset

is a number value. A negative month-oset is used to specify a number of months

before the starting date and a positive month-oset is used to specify a number of

months after the starting date.

Examples

=EOMONTH(”5/15/2010”, 5) returns Oct 31, 2010, the last day of the month ve months after May 2010.

=EOMONTH(”5/15/2010”, -5) returns Dec 31, 2009, the last day of the month ve months before May

2010.

Related Topics

For related functions and additional information, see:

“EDATE” on page 50

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

HOUR

The HOUR function returns the hour for a given date/time value.

HOUR(time)

Âtime: The time the function should use. time is a date/time value. The date portion

is ignored by this function.

Usage Notes

The hour returned is in 24-hour format (0 is midnight, 23 is 11:00 p.m.). Â

Examples

=HOUR(NOW()) returns the current hour of the day.

=HOUR(”4/6/88 11:59:22 AM”) returns 11.

Related Topics

For related functions and additional information, see:

“DAY” on page 47

“MINUTE” on page 52

“MONTH” on page 53

“SECOND” on page 56

“YEAR” on page 62

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

MINUTE

The MINUTE function returns the minutes for a given date/time value.

MINUTE(time)

Âtime: The time the function should use. time is a date/time value. The date portion

is ignored by this function.

Example

=MINUTE(”4/6/88 11:59:22 AM”) returns 59.

Related Topics

For related functions and additional information, see:

“DAY” on page 47

“HOUR” on page 51

“MONTH” on page 53

“SECOND” on page 56

“YEAR” on page 62

“Listing of Date and Time Functions” on page 42

52 Chapter 3 Date and Time Functions

Chapter 3 Date and Time Functions 53

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

MONTH

The MONTH function returns the month for a given date/time value.

MONTH(date)

Âdate: The date the function should use. date is a date/time value. The time portion

is ignored by this function.

Example

=MONTH(”April 6, 1988 11:59:22 AM”) returns 4.

Related Topics

For related functions and additional information, see:

“DAY” on page 47

“HOUR” on page 51

“MINUTE” on page 52

“MONTHNAME” on page 54

“SECOND” on page 56

“YEAR” on page 62

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

MONTHNAME

The MONTHNAME function returns the name of the month from a number. Month 1 is

January.

MONTHNAME(month-num)

Âmonth-num: The desired month. month-num is a number value and must be in the

range 1 to 12. If month-num has a decimal portion, it is ignored.

Examples

=MONTHNAME(9) returns September.

=MONTHNAME(6) returns June.

Related Topics

For related functions and additional information, see:

“DAYNAME” on page 48

“MONTH” on page 53

“WEEKDAY” on page 59

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

NETWORKDAYS

The NETWORKDAYS function returns the number of working days between two dates.

Working days exclude weekends and any other specied dates.

NETWORKDAYS(start-date, end-date, exclude-dates)

Âstart-date: The starting date. start-date is a date/time value.

Âend-date: The ending date. end-date is a date/time value.

Âexclude-dates: An optional collection of dates that should be excluded from the

count. exclude-dates is a collection containing date/time values.

Example

=NETWORKDAYS(”11/01/2009”, “11/30/2009”, {”11/11/2009”,”11/26/2009”}) returns 19d, the number of

working days in November 2009 excluding weekends and the two holidays specically excluded.

54 Chapter 3 Date and Time Functions

Chapter 3 Date and Time Functions 55

Related Topics

For related functions and additional information, see:

“DATEDIF” on page 45

“DAYS360” on page 49

“WORKDAY” on page 61

“YEARFRAC” on page 63

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

NOW

The NOW function returns the current date/time value from the system clock.

NOW()

Usage Notes

The NOW function does not have any arguments. However, you must include the Â

parentheses: =NOW().

Example

=NOW() returns October 4, 2008 10:47 am, if your le is updated on October 4, 2008, at 10:47 a.m.

Related Topics

For related functions and additional information, see:

“TODAY” on page 58

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

SECOND

The SECOND function returns the seconds for a given date/time value.

SECOND(time)

Âtime: The time the function should use. time is a date/time value. The date portion

is ignored by this function.

Example

=SECOND(”4/6/88 11:59:22 am”) returns 22.

Related Topics

For related functions and additional information, see:

“DAY” on page 47

“HOUR” on page 51

“MINUTE” on page 52

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

TIME

The TIME function converts separate values for hours, minutes, and seconds into a

date/time value.

TIME(hours, minutes, seconds)

Âhours: The number of hours to include in the value returned. hours is a number

value. If hours has a decimal portion, it is ignored.

Âminutes: The number of minutes to include in the value returned. minutes is a

number value. If minutes has a decimal portion, it is ignored.

Âseconds: The number of seconds to include in the value returned. seconds is a

number value. If seconds has a decimal portion, it is ignored.

Usage Notes

You can specify hour, minute, and second values greater than 24, 60, and 60, Â

respectively. If the hours, minutes, and seconds add up to more than 24 hours, 24

hours are repeatedly subtracted until the value is less than 24 hours.

56 Chapter 3 Date and Time Functions

Chapter 3 Date and Time Functions 57

Examples

=TIME(12, 0, 0) returns 12:00 pm.

=TIME(16, 45, 30) returns 4:45 pm.

=TIME(0, 900, 0) returns 3:00 pm.

=TIME(60, 0, 0) returns 12:00 pm.

=TIME(4.25, 0, 0) returns 4:00 am.

Related Topics

For related functions and additional information, see:

“DATE” on page 44

“DATEVALUE” on page 47

“DURATION” on page 70

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

TIMEVALUE

The TIMEVALUE function returns the time as a decimal fraction of a 24-hour day from a

given date/time value or from a text string.

TIMEVALUE(time)

Âtime: The time the function should use. time is a date/time value. The date portion

is ignored by this function.

Examples

=TIMEVALUE(”4/6/88 12:00”) returns 0.5 (noon represents one-half of the day).

=TIMEVALUE(”12:00:59”) returns 0.5007 (rounded to four decimal places of accuracy).

=TIMEVALUE(”9:00 pm”) returns 0.875 (21 hours, or 9:00 p.m., divided by 24).

Related Topics

For related functions and additional information, see:

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

TODAY

The TODAY function returns the current system date. The time is set to 12:00 a.m.

TODAY()

Usage Notes

The TODAY function does not have any arguments. However, you must include the Â

parentheses: =TODAY().

The displayed date is updated every time you open or modify your le. Â

You can use the NOW function to get the current date and time and to format the Â

cell to display both.

Example

=TODAY() returns Apr 6, 2008, when calculated on April 6, 2008.

Related Topics

For related functions and additional information, see:

“NOW” on page 55

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

58 Chapter 3 Date and Time Functions

Chapter 3 Date and Time Functions 59

WEEKDAY

The WEEKDAY function returns a number that is the day of the week for a given date.

WEEKDAY(date, rst-day)

Âdate: The date the function should use. date is a date/time value. The time portion

is ignored by this function.

Ârst-day:An optional value that species how days are numbered.

Sunday is 1 (1 or omitted): Sunday is the rst day (day 1) of the week and Saturday

is day 7.

Monday is 1 (2): Monday is the rst day (day 1) of the week and Sunday is day 7.

Monday is 0 (3): Monday is the rst day (day 0) of the week and Sunday is day 6.

Examples

=WEEKDAY(”Apr 6, 1988”, 1) returns 4 (Wednesday, the fourth day if you start counting Sunday as day

1).

=WEEKDAY(”Apr 6, 1988”) returns the same value as the preceding example (numbering scheme 1 is

used if no number-scheme argument is specied).

=WEEKDAY(”Apr 6, 1988”, 2) returns 3 (Wednesday, the third day if you start counting Monday as day

1).

=WEEKDAY(”Apr 6, 1988”, 3) returns 2 (Wednesday, day number 2 if you start counting Monday as day

0).

Related Topics

For related functions and additional information, see:

“DAYNAME” on page 48

“MONTHNAME” on page 54

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

WEEKNUM

The WEEKNUM function returns the number of the week within the year for a given date.

WEEKNUM(date, rst-day)

Âdate: The date the function should use. date is a date/time value. The time portion

is ignored by this function.

Ârst-day:An optional value that species whether weeks should begin on Sunday

or Monday.

Sunday is 1 (1 or omitted): Sunday is the rst day (day 1) of the week and Saturday

is day 7.

Monday is 1 (2): Monday is the rst day (day 1) of the week and Sunday is day 7.

Example

=WEEKNUM(”7/12/2009”,1) returns 29.

=WEEKNUM(”7/12/2009”,2) returns 28.

Related Topics

For related functions and additional information, see:

“DAY” on page 47

“HOUR” on page 51

“MINUTE” on page 52

“MONTH” on page 53

“SECOND” on page 56

“YEAR” on page 62

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

60 Chapter 3 Date and Time Functions

Chapter 3 Date and Time Functions 61

WORKDAY

The WORKDAY function returns the date that is the given number of working days

before or after a given date. Working days exclude weekends and any other dates

specically excluded.

WORKDAY(date, work-days, exclude-dates)

Âdate: The date the function should use. date is a date/time value. The time portion

is ignored by this function.

Âwork-days: The number of working days before or after the given date. work-days

is a number value. It is positive if the desired date is after date and negative if the

desired date is before date.

Âexclude-dates: An optional collection of dates that should be excluded from the

count. exclude-dates is a collection containing date/time values.

Example

=WORKDAY(”11/01/2009”, 20, {”11/11/2009”,”11/26/2009”}) returns Dec 1, 2009, the work day 20 days

after 11/01/2009 excluding weekends and the two holidays specically excluded.

Related Topics

For related functions and additional information, see:

“NETWORKDAYS” on page 54

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

YEAR

The YEAR function returns the year for a given date/time value.

YEAR(date)

Âdate: The date the function should use. date is a date/time value. The time portion

is ignored by this function.

Examples

=YEAR(”April 6, 2008”) returns 2008.

=YEAR(NOW()) returns 2009 when evaluated on June 4, 2009.

Related Topics

For related functions and additional information, see:

“DAY” on page 47

“HOUR” on page 51

“MINUTE” on page 52

“MONTH” on page 53

“SECOND” on page 56

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

62 Chapter 3 Date and Time Functions

Chapter 3 Date and Time Functions 63

YEARFRAC

The YEARFRAC function nds the fraction of a year represented by the number of

whole days between two dates.

YEARFRAC(start-date, end-date, days-basis)

Âstart-date: The starting date. start-date is a date/time value.

Âend-date: The ending date. end-date is a date/time value.

Âdays-basis: An optional argument specifying the number of days per month and

days per year used in the calculations.

30/360 (0 or omitted): 30 days in a month, 360 days in a year, using the NASD

method for dates falling on the 31st of a month.

actual/actual (1): Actual days in each month, actual days in each year.

actual/360 (2): Actual days in each month, 360 days in a year.

actual/365 (3): Actual days in each month, 365 days in a year.

30E/360 (4): 30 days in a month, 360 days in a year, using the European method for

dates falling on the 31st of a month (European 30/360).

Examples

=YEARFRAC(”12/15/2009”, “6/30/2010”,0) returns 0.541666667.

=YEARFRAC(”12/15/2009”, “6/30/2010”,1) returns 0.539726027.

=YEARFRAC(”12/15/2009”, “6/30/2010”,2) returns 0.547222222.

=YEARFRAC(”12/15/2009”, “6/30/2010”,3) returns 0.539726027.

=YEARFRAC(”12/15/2009”, “6/30/2010”,4) returns 0.541666667.

Related Topics

For related functions and additional information, see:

“DATEDIF” on page 45

“DAYS360” on page 49

“NETWORKDAYS” on page 54

“Listing of Date and Time Functions” on page 42

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

The duration functions help you work with periods of time

(durations) by converting between dierent time periods,

such as hours, days, and weeks.

Listing of Duration Functions

iWork provides these duration functions for use with tables.

Function Description

“DUR2DAYS” (page 65) The DUR2DAYS function converts a duration

value to a number of days.

“DUR2HOURS” (page 65) The DUR2HOURS function converts a duration

value to a number of hours.

“DUR2MILLISECONDS” (page 66) The DUR2MILLISECONDS function converts a

duration value to a number of milliseconds.

“DUR2MINUTES” (page 67) The DUR2MINUTES function converts a duration

value to a number of minutes.

“DUR2SECONDS” (page 68) The DUR2SECONDS function converts a duration

value to a number of seconds.

“DUR2WEEKS” (page 69) The DUR2WEEKS function converts a duration

value to a number of weeks.

“DURATION” (page 70) The DURATION function combines separate

values for weeks, days, hours, minutes, seconds,

and milliseconds and returns a duration value.

“STRIPDURATION” (page 71) The STRIPDURATION function evaluates a given

value and returns either the number of days

represented, if a duration value, or the given

value. This function is included for compatibility

with other spreadsheet applications.

Duration Functions

Chapter 4 Duration Functions 65

DUR2DAYS

The DUR2DAYS function converts a duration value to a number of days.

DUR2DAYS(duration)

Âduration: The length of time to be converted. duration is a duration value.

Examples

=DUR2DAYS(”2w 3d 2h 10m 0s 5ms”) returns 17.09027784.

=DUR2DAYS(”10:0:13:00:05.500”) returns 70.5417302.

Related Topics

For related functions and additional information, see:

“DUR2HOURS” on page 65

“DUR2MILLISECONDS” on page 66

“DUR2MINUTES” on page 67

“DUR2SECONDS” on page 68

“DUR2WEEKS” on page 69

“Listing of Duration Functions” on page 64

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

DUR2HOURS

The DUR2HOURS function converts a duration value to a number of hours.

DUR2HOURS(duration)

Âduration: The length of time to be converted. duration is a duration value.

Examples

=DUR2HOURS(”2w 3d 2h 10m 0s 5ms”) returns 410.1666681.

=DUR2HOURS(”10:0:13:00:05.500”) returns 1693.001528.

Related Topics

For related functions and additional information, see:

“DUR2DAYS” on page 65

“DUR2MILLISECONDS” on page 66

“DUR2MINUTES” on page 67

“DUR2SECONDS” on page 68

“DUR2WEEKS” on page 69

“Listing of Duration Functions” on page 64

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

DUR2MILLISECONDS

The DUR2MILLISECONDS function converts a duration value to a number of milliseconds.

DUR2MILLISECONDS(duration)

Âduration: The length of time to be converted. duration is a duration value.

Examples

=DUR2MILLISECONDS(”2w 3d 2h 10m 0s 5ms”) returns 1476600005.

=DUR2MILLISECONDS(”10:0:13:00:05.500”) returns 6094805500.

Related Topics

For related functions and additional information, see:

“DUR2DAYS” on page 65

“DUR2HOURS” on page 65

“DUR2MINUTES” on page 67

“DUR2SECONDS” on page 68

“DUR2WEEKS” on page 69

“Listing of Duration Functions” on page 64

66 Chapter 4 Duration Functions

Chapter 4 Duration Functions 67

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

DUR2MINUTES

The DUR2MINUTES function converts a duration value to a number of minutes.

DUR2MINUTES(duration)

Âduration: The length of time to be converted. duration is a duration value.

Examples

=DUR2MINUTES(”2w 3d 2h 10m 0s 5ms”) returns 24610.0000833333.

=DUR2MINUTES(”10:0:13:00:05.500”) returns 101580.091666667.

Related Topics

For related functions and additional information, see:

“DUR2DAYS” on page 65

“DUR2HOURS” on page 65

“DUR2MILLISECONDS” on page 66

“DUR2SECONDS” on page 68

“DUR2WEEKS” on page 69

“Listing of Duration Functions” on page 64

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

DUR2SECONDS

The DUR2SECONDS function converts a duration value to a number of seconds.

DUR2SECONDS(duration)

Âduration: The length of time to be converted. duration is a duration value.

Examples

=DUR2SECONDS(”2w 3d 2h 10m 0s 5ms”) returns 1476600.005.

=DUR2SECONDS(”10:0:13:00:05.500”) returns 6094805.5.

Related Topics

For related functions and additional information, see:

“DUR2DAYS” on page 65

“DUR2HOURS” on page 65

“DUR2MILLISECONDS” on page 66

“DUR2MINUTES” on page 67

“DUR2WEEKS” on page 69

“Listing of Duration Functions” on page 64

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

68 Chapter 4 Duration Functions

Chapter 4 Duration Functions 69

DUR2WEEKS

The DUR2WEEKS function converts a duration value to a number of weeks.

DUR2WEEKS(duration)

Âduration: The length of time to be converted. duration is a duration value.

Examples

=DUR2WEEKS(”2w 3d 2h 10m 0s 5ms”) returns 2.44146826223545.

=DUR2WEEKS(”10:0:13:00:05.500”) returns 10.0773900462963.

Related Topics

For related functions and additional information, see:

“DUR2DAYS” on page 65

“DUR2HOURS” on page 65

“DUR2MILLISECONDS” on page 66

“DUR2MINUTES” on page 67

“DUR2SECONDS” on page 68

“Listing of Duration Functions” on page 64

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

DURATION

The DURATION function combines separate values for weeks, days, hours, minutes,

seconds, and milliseconds and returns a duration value.

DURATION(weeks, days, hours, minutes, seconds, milliseconds)

Âweeks: A value representing the number of weeks. weeks is a number value.

Âdays: An optional value representing the number of days. days is a number value.

Âhours: An optional value representing the number of hours. hours is a number value.

Âminutes: An optional value representing the number of minutes. minutes is a

number value.

Âseconds: An optional value representing the number of seconds. seconds is a

number value.

Âmilliseconds: An optional value representing the number of milliseconds.

milliseconds is a number value.

Usage Notes

An argument that is 0 can be omitted, but the comma must be included if later Â

values are included. For example, =DURATION(, ,12, 3) would return a duration value

of 12h 3m (12 hours and 3 minutes).

Negative values are permitted. For example, =DURATION(0, 2, -24) would return a Â

duration of 1 day (2 days minus 24 hours).

Examples

=DURATION(1) returns 1w (1 week).

=DURATION(,,1) returns 1h (1 hour).

=DURATION(1.5) returns 1w 3d 12h (1 week, 3 days, 12 hours or 1.5 weeks).

=DURATION(3, 2, 7, 10, 15.3505) returns 3w 2d 7h 10m 15s 350ms (3 weeks, 2 days, 7 hours, 10 minutes,

15 seconds, 350 milliseconds).

Related Topics

For related functions and additional information, see:

“DATE” on page 44

“TIME” on page 56

“Listing of Duration Functions” on page 64

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

70 Chapter 4 Duration Functions

Chapter 4 Duration Functions 71

STRIPDURATION

The STRIPDURATION function evaluates a given value and returns either the number

of days represented, if a duration value, or the given value. This function is included for

compatibility with other spreadsheet applications.

STRIPDURATION(any-value)

Âany-value: A value. any-value can contain any value type.

Usage Notes

If Âany-value is a duration value, the result is the same as for DUR2DAYS; otherwise

any-value is returned.

This function may be automatically inserted when a Numbers ’08 document is Â

upgraded, or an Excel or Appleworks document is imported. It is removed in any

copy of the le saved as a Numbers ’08 or Excel document.

Examples

=STRIPDURATION(”1w”) returns 7, the equivalent of one week in days.

=STRIPDURATION(12) returns 12; since it was not a duration value it is returned.

=STRIPDURATION (”abc”) returns “abc”.

Related Topics

For related functions and additional information, see:

“Listing of Duration Functions” on page 64

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

The engineering functions help you calculate some common

engineering values and convert between dierent numeric

bases.

Listing of Engineering Functions

iWork provides these engineering functions for use with tables.

Function Description

“BASETONUM” (page 73) The BASETONUM function converts a number of

the specied base into a number in base 10.

“BESSELJ” (page 74)The BESSELJ function returns the integer Bessel

function Jn(x).

“BESSELY” (page 75)The BESSELY function returns the integer Bessel

function Yn(x).

“BIN2DEC” (page 76) The BIN2DEC function converts a binary number

to the corresponding decimal number.

“BIN2HEX” (page 77) The BIN2HEX function converts a binary number

to the corresponding hexadecimal number.

“BIN2OCT” (page 78) The BIN2OCT function converts a binary number

to the corresponding octal number.

“CONVERT” (page 79) The CONVERT function converts a number from

one measurement system to its corresponding

value in another measurement system.

“DEC2BIN” (page 83) The DEC2BIN function converts a decimal

number to the corresponding binary number.

“DEC2HEX” (page 84) The DEC2HEX function converts a decimal

number to the corresponding hexadecimal

number.

Engineering Functions

Chapter 5 Engineering Functions 73

Function Description

“DEC2OCT” (page 85) The DEC2OCT function converts a decimal

number to the corresponding octal number.

“DELTA” (page 86) The DELTA function determines whether two

values are exactly equal.

“ERF” (page 87) The ERF function returns the error function

integrated between two values.

“ERFC” (page 87) The ERFC function returns the complementary

ERF function integrated between a given lower

bound and innity.

“GESTEP” (page 88) The GESTEP function determines if one value is

greater than or exactly equal to another value.

“HEX2BIN” (page 89) The HEX2BIN function converts a hexadecimal

number to the corresponding binary number.

“HEX2DEC” (page 90) The HEX2DEC function converts a hexadecimal

number to the corresponding decimal number.

“HEX2OCT” (page 91) The HEX2OCT function converts a hexadecimal

number to the corresponding octal number.

“NUMTOBASE” (page 92) The NUMTOBASE function converts a number

from base 10 into a number in the specied base.

“OCT2BIN” (page 93) The OCT2BIN function converts an octal number

to the corresponding binary number.

“OCT2DEC” (page 94) The OCT2DEC function converts an octal number

to the corresponding decimal number.

“OCT2HEX” (page 95) The OCT2HEX function converts an octal number

to the corresponding hexadecimal number.

BASETONUM

The BASETONUM function converts a number of the specied base into a number

in base 10.

BASETONUM(convert-string, base)

Âconvert-string: The string representing the number to be converted. convert-string

is a string value. It must contain only numbers and letters that apply in the base of

the number being converted.

Âbase: The current base of the number to be converted. base is a number value and

must be in the range 1 to 36.

Usage Notes

This function returns a number value and can properly be used in a formula Â

containing other number values. Some other spreadsheet applications return

a string value.

Examples

=BASETONUM(”3f”, 16) returns 63.

=BASETONUM(1000100, 2) returns 68.

=BASETONUM(”7279”, 8) returns an error, since the digit “9” is not valid in base 8.

Related Topics

For related functions and additional information, see:

“BIN2DEC” on page 76

“HEX2DEC” on page 90

“NUMTOBASE” on page 92

“OCT2DEC” on page 94

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

BESSELJ

The BESSELJ function returns the integer Bessel function Jn(x).

BESSELJ(any-x-value, n-value)

Âany-x-value: The x value at which you want to evaluate the function. any-x-value is

a number value.

Ân-value: The order of the function. n-value is a number value and must be greater

than or equal to 0. If n-value has a decimal portion, it is ignored.

Examples

=BESSELJ(25, 3) returns 0.108343081061509.

=BESSELJ(25, 3.9) also returns 0.108343081061509, since any decimal portion of n-value is ignored.

=BESSELJ(-25, 3) returns -0.108343081061509.

74 Chapter 5 Engineering Functions

Chapter 5 Engineering Functions 75

Related Topics

For related functions and additional information, see:

“BESSELY” on page 75

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

BESSELY

The BESSELY function returns the integer Bessel function Yn(x).

BESSELY(pos-x-value, n-value)

Âpos-x-value: The positive x value at which you want to evaluate the function.

pos-x-value is a number value and must be greater than 0.

Ân-value: The order of the function. n-value is a number value and must be greater

than or equal to 0. If n-value has a decimal portion, it is ignored.

Usage Notes

This form of the Bessel function is also known as the Neumann function. Â

Examples

=BESSELY(25, 3) returns 0.117924850396893.

=BESSELY(25, 3.9) also returns 0.117924850396893, since any decimal portion of n-value is ignored.

=BESSELY(-25, 3) returns an error, since negative or zero values are not permitted.

Related Topics

For related functions and additional information, see:

“BESSELJ” on page 74

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

BIN2DEC

The BIN2DEC function converts a binary number to the corresponding decimal

number.

BIN2DEC(binary-string, convert-length)

Âbinary-string: The string representing the number to be converted. binary-string is

a string value. It must contain only 0s and 1s.

Âconvert-length: An optional value specifying minimum length of the number

returned. convert-length is a number value and must be in the range 1 to 32. If

omitted, it is assumed to be 1. If included, convert-string is padded with leading

zeros, if necessary, so that it is at least the length specied by convert-length.

Examples

=BIN2DEC(”1001”) returns 9.

=BIN2DEC(”100111”, 3) returns 039.

=BIN2DEC(101101) returns 45.

Related Topics

For related functions and additional information, see:

“BIN2HEX” on page 77

“BIN2OCT” on page 78

“DEC2BIN” on page 83

“HEX2DEC” on page 90

“OCT2DEC” on page 94

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

76 Chapter 5 Engineering Functions

Chapter 5 Engineering Functions 77

BIN2HEX

The BIN2HEX function converts a binary number to the corresponding hexadecimal

number.

BIN2HEX(binary-string, convert-length)

Âbinary-string: The string representing the number to be converted. binary-string is

a string value. It must contain only 0s and 1s.

Âconvert-length: An optional value specifying minimum length of the number

returned. convert-length is a number value and must be in the range 1 to 32. If

omitted, it is assumed to be 1. If included, convert-string is padded with leading

zeros, if necessary, so that it is at least the length specied by convert-length.

Usage Notes

This function uses two’s complement notation, based on 32 bits. Therefore, negative Â

numbers will always be 8 digits in length.

Examples

=BIN2HEX(”100101”) returns 25.

=BIN2HEX(”100111”, 3) returns 027.

=BIN2HEX(101101) returns 2D.

Related Topics

For related functions and additional information, see:

“BIN2DEC” on page 76

“BIN2OCT” on page 78

“DEC2HEX” on page 84

“HEX2BIN” on page 89

“OCT2HEX” on page 95

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

BIN2OCT

The BIN2OCT function converts a binary number to the corresponding octal number.

BIN2OCT(binary-string, convert-length)

Âbinary-string: The string representing the number to be converted. binary-string is

a string value. It must contain only 0s and 1s.

Âconvert-length: An optional value specifying minimum length of the number

returned. convert-length is a number value and must be in the range 1 to 32. If

omitted, it is assumed to be 1. If included, convert-string is padded with leading

zeros, if necessary, so that it is at least the length specied by convert-length.

Usage Notes

This function uses two’s complement notation, based on 32 bits. Therefore, negative Â

numbers will always be 11 digits in length.

Examples

=BIN2OCT(”10011”) returns 23.

=BIN2OCT(”100111”, 3) returns 047.

Related Topics

For related functions and additional information, see:

“BIN2HEX” on page 77

“DEC2OCT” on page 85

“HEX2OCT” on page 91

“OCT2BIN” on page 93

“BIN2DEC” on page 76

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

78 Chapter 5 Engineering Functions

Chapter 5 Engineering Functions 79

CONVERT

The CONVERT function converts a number from one measurement system to its

corresponding value in another measurement system.

CONVERT(convert-num, from-unit, to-unit)

Âconvert-num: The number to be converted. convert-num is a number value.

Âfrom-unit: The current unit of the number to be converted. from-unit is a string

value. It must be one of the specied constants.

Âto-unit: The new unit of the number to be converted. to-unit is a string value.

It must be one of the specied constants.

Usage Notes

The possible values for Âfrom-unit and to-unit are contained in tables that follow

the examples (“Supported Conversion Units” on page 80). The tables are organized

by category. If the value is entered into a referenced cell, instead of being typed

directly into the function, the quotes included in the tables are not required. Case is

important and must be strictly followed.

Examples

=CONVERT(9, “lbm”, “kg”) returns 4.08233133 (9 pounds is approximately 4.08 kilograms).

=CONVERT (26.2, “mi”, “m”) returns 42164.8128 (26.2 miles is approximately 42,164.8 meters).

=CONVERT(1, “tsp”, “ml”) returns 4.92892159375 (1 teaspoon is approximately 4.9 milliliters).

Related Topics

For related functions and additional information, see:

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

Supported Conversion Units

Weight and mass

Measure Constant

Gram “g” (can be used with metric prexes)

Slug “sg”

Pound mass (avoirdupois) “lbm”

U (atomic mass unit) “u” (can be used with metric prexes)

Ounce mass (avoirdupois) “ozm”

Distance

Measure Constant

Meter “m” (can be used with metric prexes)

Statute mile “mi”

Nautical mile “Nmi”

Inch “in”

Foot “ft”

Yard “yd”

Angstrom “ang” (can be used with metric prexes)

Pica (1/6 in., Postscript Pica) “Pica”

Duration

Measure Constant

Year “yr”

Week “wk”

Day “day”

Hour “hr”

Minute “mn”

Second “sec” (can be used with metric prexes)

80 Chapter 5 Engineering Functions

Chapter 5 Engineering Functions 81

Speed

Measure Constant

Miles per hour “mi/h”

Miles per minute “mi/mn”

Meters per hour “m/h” (can be used with metric prexes)

Meters per minute “m/mn” (can be used with metric prexes)

Meters per second “m/s” (can be used with metric prexes)

Feet per minute “ft/mn”

Feet per second “ft/s”

Knot “kt”

Pressure

Measure Constant

Pascal “Pa” (can be used with metric prexes)

Atmosphere “atm” (can be used with metric prexes)

Millimeters of mercury “mmHg” (can be used with metric prexes)

Force

Measure Constant

Newton “N” (can be used with metric prexes)

Dyne “dyn” (can be used with metric prexes)

Pound force “lbf”

Energy

Measure Constant

Joule “J” (can be used with metric prexes)

Erg “e” (can be used with metric prexes)

Thermodynamic calorie “c” (can be used with metric prexes)

IT calorie “cal” (can be used with metric prexes)

Electron volt “eV” (can be used with metric prexes)

Horsepower-hour “HPh”

Measure Constant

Watt-hour “Wh” (can be used with metric prexes)

Foot-pound “b”

BTU “BTU”

Power

Measure Constant

Horsepower “HP”

Watt “W” (can be used with metric prexes)

Magnetism

Measure Constant

Tesla “T” (can be used with metric prexes)

Gauss “ga” (can be used with metric prexes)

Temperature

Measure Constant

Degrees Celsius “C”

Degrees Fahrenheit “F”

Kelvins “K” (can be used with metric prexes)

Liquid

Measure Constant

Teaspoon “tsp”

Tablespoon “tbs”

Fluid ounce “oz”

Cup “cup”

U.S. pint “pt”

U.K. pint “uk_pt”

Quart “qt”

Gallon “gal”

Liter “l” (can be used with metric prexes)

82 Chapter 5 Engineering Functions

Chapter 5 Engineering Functions 83

Metricprexes

Measure Constant Multiplier

exa “E” 1E+18

peta “P” 1E+15

tera “T” 1E+12

giga “G” 1E+09

mega “M” 1E+06

kilo “k” 1E+03

hecto “h” 1E+02

deca “e” 1E+01

deci “d” 1E-01

centi “c” 1E-02

milli “m” 1E-03

micro “u” or “µ” 1E-06

nano “n” 1E-09

pico “p” 1E-12

femto “f” 1E-15

atto “a” 1E-18

Usage Notes

These prexes can only be used with the metric constants “g”, “u”, “m”, “ang”, “sec”, “m/h”, Â

“m/mn”, “m/s”, “Pa”, “atm”, “mmHg”, “N”, “dyn”, “J”, “e”, “c”, “cal”, “eV”, “Wh”, “W”, “T”, “ga”, “K”, and “l”.

DEC2BIN

The DEC2BIN function converts a decimal number to the corresponding binary

number.

DEC2BIN(decimal-string, convert-length)

Âdecimal-string: The string representing the number to be converted. decimal-string

is a string value. It must contain only the numbers 0 through 9.

Âconvert-length: An optional value specifying minimum length of the number

returned. convert-length is a number value and must be in the range 1 to 32. If

omitted, it is assumed to be 1. If included, convert-string is padded with leading

zeros, if necessary, so that it is at least the length specied by convert-length.

Examples

=DEC2BIN(100) returns 01100100.

=DEC2BIN(”1001”, 12) returns 001111101001.

Related Topics

For related functions and additional information, see:

“BIN2DEC” on page 76

“DEC2HEX” on page 84

“DEC2OCT” on page 85

“HEX2BIN” on page 89

“OCT2BIN” on page 93

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

DEC2HEX

The DEC2HEX function converts a decimal number to the corresponding hexadecimal

number.

DEC2HEX(decimal-string, convert-length)

Âdecimal-string: The string representing the number to be converted. decimal-string

is a string value. It must contain only the numbers 0 through 9.

Âconvert-length: An optional value specifying minimum length of the number

returned. convert-length is a number value and must be in the range 1 to 32. If

omitted, it is assumed to be 1. If included, convert-string is padded with leading

zeros, if necessary, so that it is at least the length specied by convert-length.

Examples

=DEC2HEX(100) returns 64.

=DEC2HEX(”1001”, 4) returns 03E9.

84 Chapter 5 Engineering Functions

Chapter 5 Engineering Functions 85

Related Topics

For related functions and additional information, see:

“BIN2HEX” on page 77

“DEC2BIN” on page 83

“DEC2OCT” on page 85

“HEX2DEC” on page 90

“OCT2HEX” on page 95

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

DEC2OCT

The DEC2OCT function converts a decimal number to the corresponding octal number.

DEC2OCT(decimal-string, convert-length)

Âdecimal-string: The string representing the number to be converted. decimal-string

is a string value. It must contain only the numbers 0 through 9.

Âconvert-length: An optional value specifying minimum length of the number

returned. convert-length is a number value and must be in the range 1 to 32. If

omitted, it is assumed to be 1. If included, convert-string is padded with leading

zeros, if necessary, so that it is at least the length specied by convert-length.

Examples

=DEC2OCT(100) returns 144.

=DEC2OCT(”1001”, 4) returns 1751.

Related Topics

For related functions and additional information, see:

“BIN2OCT” on page 78

“DEC2BIN” on page 83

“DEC2HEX” on page 84

“HEX2OCT” on page 91

“OCT2DEC” on page 94

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

DELTA

The DELTA function determines whether two values are exactly equal. This function

uses exact equality. By comparison, the = operator uses string-based equality.

DELTA(compare-from, compare-to)

Âcompare-from: A number. compare-from is a number value.

Âcompare-to: A number. compare-to is a number value.

Usage Notes

DELTA returns 1 (TRUE) if Âcompare-from is exactly equal to compare-to; otherwise

0 (FALSE) is returned.

Examples

=DELTA(5, 5) returns 1 (TRUE).

=DELTA(5, -5) returns 0 (FALSE).

=DELTA(5, 5.000) returns 1 (TRUE).

Related Topics

For related functions and additional information, see:

“GESTEP” on page 88

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

86 Chapter 5 Engineering Functions

Chapter 5 Engineering Functions 87

ERF

The ERF function returns the error function integrated between two values.

ERF(lower, upper)

Âlower: The lower limit or bound. lower is a number value.

Âupper: An optional argument specifying the upper limit or bound. upper is a

number value. If upper is omitted it is assumed to be 0.

Usage Notes

This function is also known as the Gauss error function. Â

Examples

=ERF(0, 1) returns 0.842700792949715.

=ERF(-1, 1) returns 1.68540158589943.

=ERF(1, 8) returns 0.157299207050285.

Related Topics

For related functions and additional information, see:

“ERFC” on page 87

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

ERFC

The ERFC function returns the complementary ERF function integrated between a

given lower bound and innity.

ERFC(lower)

Âlower: The lower limit or bound. lower is a number value.

Examples

=ERFC(-1) returns 1.84270079294971.

=ERFC(1) returns 0.157299207050285.

=ERFC(12) returns 1.3562611692059E-64.

Related Topics

For related functions and additional information, see:

“ERF” on page 87

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

GESTEP

The GESTEP function determines if one value is greater than or exactly equal to

another value. This function uses exact equality. By comparison, the = operator uses

string-based equality.

GESTEP(compare-num, step-number)

Âcompare-num: The number to compare. compare-num is a number value.

Âstep-number: The size of the step. step-number is a number value.

Usage Notes

GESTEP returns 1 (TRUE) if Âcompare-num is greater than or exactly equal to step-

number; otherwise 0 (FALSE) is returned.

Examples

=GESTEP(-4, -5) returns 1 (TRUE), since -4 is greater than -5.

=GESTEP(4, 5) returns 0 (FALSE), since 4 is less than 5.

=GESTEP(5, 4) returns 1 (TRUE), since 5 is greater than 4.

=GESTEP(20, 20) returns 1 (TRUE), since 20 is exactly equal to 20.

Related Topics

For related functions and additional information, see:

“DELTA” on page 86

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

88 Chapter 5 Engineering Functions

Chapter 5 Engineering Functions 89

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

“Pasting from Examples in Help” on page 41

HEX2BIN

The HEX2BIN function converts a hexadecimal number to the corresponding binary

number.

HEX2BIN(hex-string, convert-length)

Âhex-string: The string representing the number to be converted. hex-string is a string

value. It must contain only the numbers 0 through 9 and the letters A through F.

Âconvert-length: An optional value specifying minimum length of the number

returned. convert-length is a number value and must be in the range 1 to 32. If

omitted, it is assumed to be 1. If included, convert-string is padded with leading

zeros, if necessary, so that it is at least the length specied by convert-length.

Usage Notes

This function uses two’s complement notation, based on 32 bits. Therefore, negative Â

numbers will always be 32 digits in length.

Examples

=HEX2BIN(”F”, 8) returns 00001111.

=HEX2BIN(“3F”) returns 0111111.

Related Topics

For related functions and additional information, see:

“BIN2HEX” on page 77

“HEX2DEC” on page 90

“HEX2OCT” on page 91

“OCT2BIN” on page 93

“DEC2BIN” on page 83

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

HEX2DEC

The HEX2DEC function converts a hexadecimal number to the corresponding decimal

number.

HEX2DEC(hex-string, convert-length)

Âhex-string: The string representing the number to be converted. hex-string is a

string value. It must contain only the numbers 0 through 9 and the letters A through

Âconvert-length: An optional value specifying minimum length of the number

returned. convert-length is a number value and must be in the range 1 to 32. If

omitted, it is assumed to be 1. If included, convert-string is padded with leading

zeros, if necessary, so that it is at least the length specied by convert-length.

Examples

=HEX2DEC(”F”, 3) returns 015.

=HEX2DEC(“3F”) returns 63.

Related Topics

For related functions and additional information, see:

“BIN2DEC” on page 76

“DEC2HEX” on page 84

“HEX2BIN” on page 89

“HEX2OCT” on page 91

“OCT2DEC” on page 94

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

90 Chapter 5 Engineering Functions

Chapter 5 Engineering Functions 91

HEX2OCT

The HEX2OCT function converts a hexadecimal number to the corresponding octal

number.

HEX2OCT(hex-string, convert-length)

Âhex-string: The string representing the number to be converted. hex-string is a

string value. It must contain only the numbers 0 through 9 and the letters A through

Âconvert-length: An optional value specifying minimum length of the number

returned. convert-length is a number value and must be in the range 1 to 32. If

omitted, it is assumed to be 1. If included, convert-string is padded with leading

zeros, if necessary, so that it is at least the length specied by convert-length.

Usage Notes

This function uses two’s complement notation, based on 32 bits. Therefore, negative Â

numbers will always be 11 digits in length.

Examples

=HEX2OCT(”F”, 3) returns 017.

=HEX2OCT(“4E”) returns 116.

Related Topics

For related functions and additional information, see:

“BIN2OCT” on page 78

“DEC2OCT” on page 85

“HEX2BIN” on page 89

“HEX2DEC” on page 90

“OCT2HEX” on page 95

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

NUMTOBASE

The NUMTOBASE function converts a number from base 10 into a number in the

specied base.

NUMTOBASE(decimal-string, base, convert-length)

Âdecimal-string: The string representing the number to be converted. decimal-string

is a string value. It must contain only the numbers 0 through 9.

Âbase: The new base of the number to be converted. base is a number value and

must be in the range 1 to 36.

Âconvert-length: An optional value specifying minimum length of the number

returned. convert-length is a number value and must be in the range 1 to 32. If

omitted, it is assumed to be 1. If included, convert-string is padded with leading

zeros, if necessary, so that it is at least the length specied by convert-length.

Examples

=NUMTOBASE(16, 16) returns 10.

=NUMTOBASE(100, 32, 4) returns 0034.

=NUMTOBASE(100,2) returns 1100100.

Related Topics

For related functions and additional information, see:

“BASETONUM” on page 73

“DEC2BIN” on page 83

“DEC2HEX” on page 84

“DEC2OCT” on page 85

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

92 Chapter 5 Engineering Functions

Chapter 5 Engineering Functions 93

OCT2BIN

The OCT2BIN function converts an octal number to the corresponding binary number.

OCT2BIN(octal-string, convert-length)

Âoctal-string: The string representing the number to be converted. octal-string is a

string value. It must contain only the numbers 0 through 7.

Âconvert-length: An optional value specifying minimum length of the number

returned. convert-length is a number value and must be in the range 1 to 32. If

omitted, it is assumed to be 1. If included, convert-string is padded with leading

zeros, if necessary, so that it is at least the length specied by convert-length.

Usage Notes

This function uses two’s complement notation, based on 32 bits. Therefore, negative Â

numbers will always be 32 digits in length.

Examples

=OCT2BIN(127,8) returns 01010111.

=OCT2BIN(15) returns 01101.

Related Topics

For related functions and additional information, see:

“BIN2OCT” on page 78

“DEC2BIN” on page 83

“HEX2BIN” on page 89

“OCT2DEC” on page 94

“OCT2HEX” on page 95

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

OCT2DEC

The OCT2DEC function converts an octal number to the corresponding decimal

number.

OCT2DEC(octal-string, convert-length)

Âoctal-string: The string representing the number to be converted. octal-string is a

string value. It must contain only the numbers 0 through 7.

Âconvert-length: An optional value specifying minimum length of the number

returned. convert-length is a number value and must be in the range 1 to 32. If

omitted, it is assumed to be 1. If included, convert-string is padded with leading

zeros, if necessary, so that it is at least the length specied by convert-length.

Examples

=OCT2DEC(127,4) returns 0087.

=OCT2DEC(15) returns 13.

Related Topics

For related functions and additional information, see:

“BIN2DEC” on page 76

“DEC2OCT” on page 85

“OCT2BIN” on page 93

“OCT2HEX” on page 95

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41

94 Chapter 5 Engineering Functions

Chapter 5 Engineering Functions 95

OCT2HEX

The OCT2HEX function converts an octal number to the corresponding hexadecimal

number.

OCT2HEX(octal-string, convert-length)

Âoctal-string: The string representing the number to be converted. octal-string is a

string value. It must contain only the numbers 0 through 7.

Âconvert-length: An optional value specifying minimum length of the number

returned. convert-length is a number value and must be in the range 1 to 32. If

omitted, it is assumed to be 1. If included, convert-string is padded with leading

zeros, if necessary, so that it is at least the length specied by convert-length.

Usage Notes

This function uses two’s complement notation, based on 32 bits. Therefore, negative Â

numbers will always be 8 digits in length.

Examples

=OCT2HEX(127,4) returns 0057.

=OCT2HEX(15) returns 0D.

Related Topics

For related functions and additional information, see:

“BIN2HEX” on page 77

“DEC2HEX” on page 84

“HEX2OCT” on page 91

“OCT2BIN” on page 93

“OCT2DEC” on page 94

“Listing of Engineering Functions” on page 72

“Value Types” on page 36

“The Elements of Formulas” on page 15

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

“Pasting from Examples in Help” on page 41