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 PDF.
Page Count: 372 [warning: Documents this large are best viewed by clicking the View PDF Link!]

iWork
Formulas and
Functions User Guide
Apple Inc. K
© 2009 Apple Inc. All rights reserved.
Under the copyright laws, this manual may not be
copied, in whole or in part, without the written consent
of Apple. Your rights to the software are governed by
the accompanying software license agreement.
The Apple logo is a trademark of Apple Inc., registered
in the U.S. and other countries. Use of the “keyboard”
Apple logo (Option-Shift-K) for commercial purposes
without the prior written consent of Apple may
constitute trademark infringement and unfair
competition in violation of federal and state laws.
Every 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
3
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
13
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.
15
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 theyre 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.
1
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 columns 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
33
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.
2
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.
42
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.
3
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
64
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.
4
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
72
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.
5
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 twos 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 twos 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 “e1E+01
deci “d1E-01
centi “c1E-02
milli “m” 1E-03
micro “u or “µ” 1E-06
nano “n” 1E-09
pico “p” 1E-12
femto “f1E-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 twos 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
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.
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
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 twos 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 twos 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