iWork Formulas and Functions User Guide
KKApple Inc. © 2009 Apple Inc. All rights reserved. Under the copyright laws, this manual may not be copied, in whole or in part, without the written consent of Apple. Your rights to the software are governed by the accompanying software license agreement. 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.
Contents 13 Preface: Welcome to iWork Formulas & Functions 15 15 17 18 19 19 20 21 23 24 24 26 27 28 28 29 30 30 31 32 Chapter 1: Using Formulas in Tables 33 33 34 34 36 40 41 42 42 44 The Elements of Formulas Performing Instant Calculations in Numbers Using Predefined Quick Formulas Creating Your Own Formulas Adding and Editing Formulas Using the Formula Editor Adding and Editing Formulas Using the Formula Bar Adding Functions to Formulas Handling Errors and Warnings in Formulas Removing Formulas
5 47 47 48 49 50 51 51 52 53 54 54 55 56 56 57 58 59 60 61 62 63 DATEDIF DATEVALUE DAY DAYNAME DAYS360 EDATE EOMONTH HOUR MINUTE MONTH MONTHNAME NETWORKDAYS NOW SECOND TIME TIMEVALUE TODAY WEEKDAY WEEKNUM WORKDAY YEAR YEARFRAC 64 64 65 65 66 67 68 69 70 71 Chapter 4: Duration Functions 72 72 73 74 75 76 77 78 79 Chapter 5: Engineering Functions 4 Listing of Duration Functions DUR2DAYS DUR2HOURS DUR2MILLISECONDS DUR2MINUTES DUR2SECONDS DUR2WEEKS DURATION STRIPDURATION Listing of Engineering Funct
80 80 80 80 81 81 81 81 82 82 82 82 83 83 84 85 86 87 87 88 89 90 91 92 93 94 95 Supported Conversion Units Weight and mass Distance Duration Speed Pressure Force Energy Power Magnetism Temperature Liquid Metric prefixes DEC2BIN DEC2HEX DEC2OCT DELTA ERF ERFC GESTEP HEX2BIN HEX2DEC HEX2OCT NUMTOBASE OCT2BIN OCT2DEC OCT2HEX 96 96 99 101 103 104 105 107 108 109 110 112 114 116 117 Chapter 6: Financial Functions Listing of Financial Functions ACCRINT ACCRINTM BONDDURATION BONDMDURATION COUPDAYBS COUPDAYS C
119 120 122 123 125 126 128 129 130 132 134 135 137 138 140 141 144 146 147 148 149 150 152 153 EFFECT FV INTRATE IPMT IRR ISPMT MIRR NOMINAL NPER NPV PMT PPMT PRICE PRICEDISC PRICEMAT PV RATE RECEIVED SLN SYD VDB YIELD YIELDDISC YIELDMAT 155 155 156 157 158 159 160 161 162 163 164 165 166 Chapter 7: Logical and Information Functions Listing of Logical and Information Functions AND FALSE IF IFERROR ISBLANK ISERROR ISEVEN ISODD NOT OR TRUE 167 Chapter 8: Numeric Functions 167 Listing of Numeric Functio
172 173 174 174 175 176 177 178 179 179 180 181 182 183 184 185 186 186 187 188 189 189 190 191 192 193 195 195 196 196 197 198 200 201 202 203 204 204 COMBIN EVEN EXP FACT FACTDOUBLE FLOOR GCD INT LCM LN LOG LOG10 MOD MROUND MULTINOMIAL ODD PI POWER PRODUCT QUOTIENT RAND RANDBETWEEN ROMAN ROUND ROUNDDOWN ROUNDUP SIGN SQRT SQRTPI SUM SUMIF SUMIFS SUMPRODUCT SUMSQ SUMX2MY2 SUMX2PY2 SUMXMY2 TRUNC 206 Chapter 9: Reference Functions 206 Listing of Reference Functions 207 ADDRESS 209 AREAS Contents 7
209 210 211 211 213 214 216 217 218 219 221 221 222 223 CHOOSE COLUMN COLUMNS HLOOKUP HYPERLINK INDEX INDIRECT LOOKUP MATCH OFFSET ROW ROWS TRANSPOSE VLOOKUP 225 225 230 231 232 233 234 236 237 238 239 239 240 242 242 244 245 246 247 248 250 252 253 253 254 255 256 257 Chapter 10: Statistical Functions 8 Listing of Statistical Functions AVEDEV AVERAGE AVERAGEA AVERAGEIF AVERAGEIFS BETADIST BETAINV BINOMDIST CHIDIST CHIINV CHITEST CONFIDENCE CORREL COUNT COUNTA COUNTBLANK COUNTIF COUNTIFS COVAR CRITB
259 260 260 261 262 262 264 265 267 268 269 270 270 271 272 273 274 275 276 277 277 278 279 280 281 282 282 284 285 287 288 289 290 291 293 294 296 297 297 298 300 302 303 GAMMADIST GAMMAINV GAMMALN GEOMEAN HARMEAN INTERCEPT LARGE LINEST Additional Statistics LOGINV LOGNORMDIST MAX MAXA MEDIAN MIN MINA MODE NEGBINOMDIST NORMDIST NORMINV NORMSDIST NORMSINV PERCENTILE PERCENTRANK PERMUT POISSON PROB QUARTILE RANK SLOPE SMALL STANDARDIZE STDEV STDEVA STDEVP STDEVPA TDIST TINV TTEST VAR VARA VARP VARPA Content
305 ZTEST 306 306 308 308 309 310 311 312 312 313 314 315 316 316 317 318 319 319 320 322 323 323 324 325 Chapter 11: Text Functions 326 326 327 328 329 329 330 331 332 333 334 334 335 336 337 338 Chapter 12: Trigonometric Functions 10 Listing of Text Functions CHAR CLEAN CODE CONCATENATE DOLLAR EXACT FIND FIXED LEFT LEN LOWER MID PROPER REPLACE REPT RIGHT SEARCH SUBSTITUTE T TRIM UPPER VALUE Listing of Trigonometric Functions ACOS ACOSH ASIN ASINH ATAN ATAN2 ATANH COS COSH DEGREES RADIANS SIN SINH
339 TANH 340 340 341 348 348 350 351 353 355 358 358 360 360 362 Chapter 13: Additional Examples and Topics 365 Index Additional Examples and Topics Included Common Arguments Used in Financial Functions Choosing Which Time Value of Money Function to Use Regular Cash Flows and Time Intervals Irregular Cash Flows and Time Intervals Which Function Should You Use to Solve Common Financial Questions? Example of a Loan Amortization Table More on Rounding Using Logical and Information Functions Together Addin
Preface Welcome to iWork Formulas & Functions iWork comes with more than 250 functions you can use to simplify statistical, financial, engineering, and other computations. The built-in Function Browser gives you a quick way to learn about functions and add them to a formula. To get started, just type the equal sign in an empty table cell to open the Formula Editor. Then choose Insert > Function > Show Function Browser.
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.
Using Formulas in Tables 1 This chapter explains how to perform calculations in table cells by using formulas. The Elements of Formulas A formula performs a calculation and displays the result in the cell where you place the formula. A cell containing a formula is referred to as a formula cell. For example, in the bottom cell of a column you can insert a formula that sums the numbers in all the cells above it.
=SUM(A2:A10): A formula that uses the function SUM to add the values in a range of cells (nine cells in the first column). A2:A10: A cell reference that refers to the values in cells A2 through A10.
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.
Using Predefined Quick Formulas An easy way to perform a basic calculation using values in a range of adjacent table cells is to select the cells and then add a quick formula. In Numbers, this is accomplished using the Function pop-up menu in the toolbar. In Keynote and Pages, use the Function pop-up menu in the Format pane of the Table inspector. Sum: Calculates the sum of numeric values in selected cells. Average: Calculates the average of numeric values in selected cells.
mm To use all the values in a row, first click the row’s header cell or reference tab. Then, in Numbers, click Function in the toolbar, and choose a calculation from the popup menu. In Keynote or Pages, choose Insert > Function and use the submenu that appears. The result is placed in a new column. Clicking on the cell will display the formula.
ÂÂ In Numbers only, select a table cell and then choose Insert > Function > Formula Editor. In Keynote and Pages, choose Formula Editor from the Function pop-up menu in the Format pane of the Table inspector. ÂÂ Select a cell that contains a formula, and then press Option-Return. The Formula Editor opens over the selected cell, but you can move it. mm To move the Formula Editor, hold the pointer over the left side of the Formula Editor until it changes into a hand, and then drag.
ÂÂ To add an operator or a constant, place the insertion point in the formula bar and type. You can use the arrow keys to move the insertion point around. See “Using Operators in Formulas” on page 28 to learn about operators you can use. When your formula requires an operator and you haven’t added one, the + operator is inserted automatically. Select the + operator and type a different operator if needed.
Although you can type a function into the text field of the Formula Editor or into the formula bar (Numbers only), the Function Browser offers a convenient way to add a function to a formula. Select a category to view functions in that category. Search for a function. Select a function to view information about it. Insert the selected function. Left pane: Lists categories of functions. Select a category to view functions in that category. Most categories represent families of related functions.
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.
To view error and warning messages: mm Click the icon. A message window summarizes each error and warning condition associated with "the cell. To have Numbers issue a warning when a cell referenced in a formula is empty, choose Numbers > Preferences and in the General pane select “Show warnings when formulas reference empty cells.” This option is not available in Keynote or Pages. Removing Formulas If you no longer want to use a formula that’s associated with a cell, you can quickly remove the formula.
Cell references have different formats, depending on such factors as whether the cell’s table has headers, whether you want to refer to a single cell or a range of cells, and so on. Here’s a summary of the formats that you can use for cell references. To refer to Use this format Any cell in the table containing the formula The reference tab letter followed C55 refers to the 55th row in the by the reference tab number for third column.
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.
ÂÂ 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.
If the column component of a cell reference is absolute ($A1): The row component is relative and may change to retain its position relative to the formula cell. For example, if a formula containing $A1 appears in C4 and you copy the formula and paste it in C5 or in D5, the cell reference in C5 and D5 becomes $A2. Here are ways to specify the absoluteness of cell reference components: mm Type the cell reference using one of the conventions described above.
The Comparison Operators You can use comparison operators to compare two values in formulas. Comparison operations always return the values TRUE or FALSE. Comparison operators can also used to build the conditions used by some functions. See “condition” in the table “Syntax Elements and Terms Used In Function Definitions” on page 34 When you want to determine 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.
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.
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: Identifies 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: mm To identify the cell containing a formula, click the formula.
Finding and Replacing Formula Elements In Numbers, using the Find & Replace window, you can search through all of a spreadsheet’s formulas to find and optionally change elements. Here are ways to open the Find & Replace window: mm Choose Edit > Find > Show Search, and then click Find & Replace. mm Choose View > Show Formula List, and then click Find & Replace. Find: Type the formula element (cell reference, operator, function, and so on) you want to find. In: Choose Formulas Only from this pop-up menu.
Overview of the iWork Functions 2 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 financial operations, retrieve cell values based on a search, manipulate strings of text, or get the current date and time.
Information About Functions For further information on Go to Syntax used in function definitions “Syntax Elements and Terms Used In Function Definitions” 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.
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 definition. 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”}.
Value Types A function argument has a type, which specifies 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 specified 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.
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 first 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.
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 specific mode for a modal argument. “Modal argument” is defined in “Syntax Elements and Terms Used In Function Definitions” on page 34.
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 (::).
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 financial calculations. Each of the categories of functions is discussed in a separate chapter.
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 first 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.
Date and Time Functions 3 The date and time functions help you work with dates and times to solve problems such as finding the number of working days between two dates or finding 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. 42 Function Description “DATE” (page 44) The DATE function combines separate values for year, month, and day and returns a date/time value.
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.
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 specifically excluded.
“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.
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.
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 specified within quotations or a date/time value. If date-text is not a valid date, an error is returned.
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: “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.
“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-offset) ÂÂ start-date: The starting date. start-date is a date/time value.
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-offset) ÂÂ start-date: The starting date. start-date is a date/time value. ÂÂ month-offset: The number of months before or after the starting date. month-offset is a number value.
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.
“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.
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: “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.
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.
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.
“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 file.
WEEKDAY The WEEKDAY function returns a number that is the day of the week for a given date. WEEKDAY(date, first-day) ÂÂ date: The date the function should use. date is a date/time value. The time portion is ignored by this function. ÂÂ first-day: An optional value that specifies how days are numbered. Sunday is 1 (1 or omitted): Sunday is the first day (day 1) of the week and Saturday is day 7. Monday is 1 (2): Monday is the first day (day 1) of the week and Sunday is day 7.
WEEKNUM The WEEKNUM function returns the number of the week within the year for a given date. WEEKNUM(date, first-day) ÂÂ date: The date the function should use. date is a date/time value. The time portion is ignored by this function. ÂÂ first-day: An optional value that specifies whether weeks should begin on Sunday or Monday. Sunday is 1 (1 or omitted): Sunday is the first day (day 1) of the week and Saturday is day 7. Monday is 1 (2): Monday is the first day (day 1) of the week and Sunday is day 7.
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 specifically 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.
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.
YEARFRAC The YEARFRAC function finds 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.
4 Duration Functions The duration functions help you work with periods of time (durations) by converting between different time periods, such as hours, days, and weeks. Listing of Duration Functions iWork provides these duration functions for use with tables. 64 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.
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: “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 millisec
“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.
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.
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.
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.
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.
Engineering Functions 5 The engineering functions help you calculate some common engineering values and convert between different numeric bases. Listing of Engineering Functions iWork provides these engineering functions for use with tables. 72 Function Description “BASETONUM” (page 73) The BASETONUM function converts a number of the specified base into a number in base 10. “BESSELJ” (page 74) The BESSELJ function returns the integer Bessel function Jn(x).
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 infinity.
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: “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.
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.
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.
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.
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 specified constants. ÂÂ to-unit: The new unit of the number to be converted. to-unit is a string value.
Supported Conversion Units Weight and mass Measure Constant Gram “g” (can be used with metric prefixes) Slug “sg” Pound mass (avoirdupois) “lbm” U (atomic mass unit) “u” (can be used with metric prefixes) Ounce mass (avoirdupois) “ozm” Distance Measure Constant Meter “m” (can be used with metric prefixes) Statute mile “mi” Nautical mile “Nmi” Inch “in” Foot “ft” Yard “yd” Angstrom “ang” (can be used with metric prefixes) Pica (1/6 in.
Speed Measure Constant Miles per hour “mi/h” Miles per minute “mi/mn” Meters per hour “m/h” (can be used with metric prefixes) Meters per minute “m/mn” (can be used with metric prefixes) Meters per second “m/s” (can be used with metric prefixes) Feet per minute “ft/mn” Feet per second “ft/s” Knot “kt” Pressure Measure Constant Pascal “Pa” (can be used with metric prefixes) Atmosphere “atm” (can be used with metric prefixes) Millimeters of mercury “mmHg” (can be used with metric pref
Measure Constant Watt-hour “Wh” (can be used with metric prefixes) Foot-pound “flb” BTU “BTU” Power Measure Constant Horsepower “HP” Watt “W” (can be used with metric prefixes) Magnetism Measure Constant Tesla “T” (can be used with metric prefixes) Gauss “ga” (can be used with metric prefixes) Temperature Measure Constant Degrees Celsius “C” Degrees Fahrenheit “F” Kelvins “K” (can be used with metric prefixes) Liquid 82 Measure Constant Teaspoon “tsp” Tablespoon “tbs” F
Metric prefixes Measure Constant Multiplier exa “E” 1E+18 peta “P” 1E+15 tera “T” 1E+12 giga “G” 1E+09 mega “M” 1E+06 kilo “k” 1E+03 hecto “h” 1E+02 deca “e” 1E+01 deci “d” 1E-01 centi “c” 1E-02 milli “m” 1E-03 micro “u” or “µ” 1E-06 nano “n” 1E-09 pico “p” 1E-12 femto “f” 1E-15 atto “a” 1E-18 Usage Notes ÂÂ These prefixes 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”,
Examples =DEC2BIN(100) returns 01100100. =DEC2BIN(”1001”, 12) returns 001111101001.
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.
“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.
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: “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.
“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.
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.
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.
NUMTOBASE The NUMTOBASE function converts a number from base 10 into a number in the specified 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.
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.
OCT2DEC The OCT2DEC function converts an octal number to the corresponding decimal number. OCT2DEC(octal-string, convert-length) ÂÂ octal-string: The string representing the number to be converted. octal-string is a string value. It must contain only the numbers 0 through 7. ÂÂ convert-length: An optional value specifying minimum length of the number returned. convert-length is a number value and must be in the range 1 to 32. If omitted, it is assumed to be 1.
OCT2HEX The OCT2HEX function converts an octal number to the corresponding hexadecimal number. OCT2HEX(octal-string, convert-length) ÂÂ octal-string: The string representing the number to be converted. octal-string is a string value. It must contain only the numbers 0 through 7. ÂÂ convert-length: An optional value specifying minimum length of the number returned. convert-length is a number value and must be in the range 1 to 32. If omitted, it is assumed to be 1.
6 Financial Functions The financial functions help you work with cash flows, depreciable assets, annuities, and investments by solving problems such as the amount of annual depreciation of an asset, the interest earned on an investment, and the current market price of a bond. Listing of Financial Functions iWork provides these financial functions for use with tables.
Function Description “COUPDAYSNC” (page 108) The COUPDAYSNC function returns the number of days between the settlement date and the end of the coupon period in which settlement occurs. “COUPNUM” (page 109) The COUPNUM function returns the number of coupons remaining to be paid between the settlement date and the maturity date.
Function Description “ISPMT” (page 126) The ISPMT function returns the interest portion of a specified loan or annuity payment based on fixed, periodic payments and a fixed interest rate. This function is provided for compatibility with tables imported from other spreadsheet applications. “MIRR” (page 128) The MIRR function returns the modified internal rate of return for an investment that is based on a series of potentially irregular cash flows that occur at regular time intervals.
Function Description “PV” (page 141) The PV function returns the present value of an investment or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate. “RATE” (page 144) The RATE function returns the interest rate of an investment, loan, or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate.
ÂÂ settle: The trade settlement date. settle is a date/time value. The trade settlement date is usually one or more days after the trade date. ÂÂ annual-rate: The annual coupon rate or stated annual interest rate of the security. annual-rate is a number value and is either entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%). ÂÂ par: The face (par) or maturity value of the security. par is a number value. If omitted (comma, but no value), par is assumed to be 1000.
Example 2 Assume you are considering the purchase of the hypothetical security described by the values listed. The settlement date is assumed to be after the first coupon date. You could use the ACCRINT function to determine the amount of accrued interest that would be added to the purchase/sale price. The function evaluates to approximately $20.56, which represents the interest accrued between the immediately preceding coupon payment date and the settlement date.
ÂÂ par: The face (par) or maturity value of the security. par is a number value. If omitted (comma, but no value), par is assumed to be 1000. ÂÂ 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.
BONDDURATION The BONDDURATION function returns the weighted average of the present value of the cash flows for an assumed par value of $100. BONDDURATION(settle, maturity, annual-rate, annual-yield, frequency, days-basis) ÂÂ settle: The trade settlement date. settle is a date/time value. The trade settlement date is usually one or more days after the trade date. ÂÂ maturity: The date when the security matures. maturity is a date/time value. It must be after settle.
Related Topics For related functions and additional information, see: “BONDMDURATION” on page 104 “Common Arguments Used in Financial Functions” on page 341 “Listing of Financial Functions” on page 96 “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 BONDMDURATION The BONDMDURATION function returns the modified weighted average of the present value of the cash flows for an assumed
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). Usage Notes ÂÂ This function returns a value known as the modified Macauley duration. Example Assume you are considering the purchase of a hypothetical security. The purchase will settle April 2, 2010 and the maturity will mature on December 31, 2015.
semiannual (2): Two payments per year. quarterly (4): Four payments per year. ÂÂ 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.
COUPDAYS The COUPDAYS function returns the number of days in the coupon period in which settlement occurs. COUPDAYS(settle, maturity, frequency, days-basis) ÂÂ settle: The trade settlement date. settle is a date/time value. The trade settlement date is usually one or more days after the trade date. ÂÂ maturity: The date when the security matures. maturity is a date/time value. It must be after settle. ÂÂ frequency: The number of coupon payments each year. annual (1): One payment per year.
Related Topics For related functions and additional information, see: “COUPDAYBS” on page 105 “COUPDAYSNC” on page 108 “Common Arguments Used in Financial Functions” on page 341 “Listing of Financial Functions” on page 96 “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 COUPDAYSNC The COUPDAYSNC function returns the number of days between the settlement date and the end of the cou
Example Assume you are considering the purchase of the hypothetical security described by the values listed. You could use the COUPDAYSNC function to determine the number of days until the next coupon payment date. This would be the number of days until the first coupon payment you would receive. The function returns 89, since there are 89 days between settlement date of April 2, 2010, and the next coupon payment date of June 30, 2010.
ÂÂ 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.
ÂÂ num-periods: The number of periods. num-periods is a number value and must be greater than or equal to 0. ÂÂ present-value: The value of the initial investment, or the amount of the loan or annuity. present-value is a number value. At time 0, an amount received is a positive amount and an amount invested is a negative amount. For example, it could be an amount borrowed (positive) or the initial payment made on an annuity contract (negative).
Related Topics For related functions and additional information, see: “CUMPRINC” on page 112 “IPMT” on page 123 “PMT” on page 134 “PPMT” on page 135 “Example of a Loan Amortization Table” on page 353 “Common Arguments Used in Financial Functions” on page 341 “Listing of Financial Functions” on page 96 “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 CUMPRINC The CUMPRINC function
end (0): Payments are due at the end of each period. beginning (1): Payments are due at the beginning of each period. Examples It is generally understood that the amount of the principal reduction on a loan is higher in the later years, as compared to the early years. This example demonstrates just how much higher the later years can be. Assume a mortgage loan with an initial loan amount of $550,000, an interest rate of 6%, and a 30-year term.
DB The DB function returns the amount of depreciation of an asset for a specified period using the fixed-declining balance method. DB(cost, salvage, life, depr-period, first-year-months) ÂÂ cost: The initial cost of the asset. cost is a number value and must be greater than or equal to 0. ÂÂ salvage: The salvage value of the asset. salvage is a number value and must be greater than or equal to 0. ÂÂ life: The number of periods over which the asset is depreciating.
Example 2 Depreciation for Partial First Year Assume the same facts as Example 1, except that the asset will be depreciated for less than 12 months in the first year. cost salvage life depr-period first-year-months 1000 100 4 1 Depreciate 9 months (returns $328.50) =DB(B2, C2, D2, E2, F3) 9 Depreciate 6 months (returns $219) =DB(B2, C2, D2, E2, F4) 3 Depreciate 3 months (returns $109.
DDB The DDB function returns the amount of depreciation of an asset based on a specified depreciation rate. DDB(cost, salvage, life, depr-period, depr-factor) ÂÂ cost: The initial cost of the asset. cost is a number value and must be greater than or equal to 0. ÂÂ salvage: The salvage value of the asset. salvage is a number value and must be greater than or equal to 0. ÂÂ life: The number of periods over which the asset is depreciating. life is a number value and must be greater than 0.
salvage life depr-period depr-factor First year, straight- =DDB(B2, C2, D2, line (returns $250) E7, F7) 1 1 =DDB(B2, C2, D2, E8, F8) 3 1 First year, tripledeclining balance (returns $750) cost Related Topics For related functions and additional information, see: “DB” on page 114 “SLN” on page 147 “SYD” on page 148 “VDB” on page 149 “Common Arguments Used in Financial Functions” on page 341 “Listing of Financial Functions” on page 96 “Value Types” on page 36 “The Elements of F
ÂÂ 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.
EFFECT The EFFECT function returns the effective annual interest rate from the nominal annual interest rate based on the number of compounding periods per year. EFFECT(nominal-rate, num-periods-year) ÂÂ nominal-rate: The nominal rate of interest of a security. nominal-rate is a number value and is either entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%). ÂÂ num-periods-year: The number of compounding periods per year.
FV The FV function returns the future value of an investment based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate. FV(periodic-rate, num-periods, payment, present-value, when-due) ÂÂ periodic-rate: The interest rate per period. periodic-rate is a number value and is either entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%). ÂÂ num-periods: The number of periods.
Example 2 Assume you are presented with an investment opportunity. The opportunity requires that you invest $50,000 in a discount security today and then nothing further. The discount security matures in 14 years and has a redemption value of $100,000. Your alternative is to leave your money in your money market savings account where it is expected to earn an annual yield of 5.25%.
INTRATE The INTRATE function returns the effective annual interest rate for a security that pays interest only at maturity. INTRATE(settle, maturity, invest-amount, redemption, days-basis) ÂÂ settle: The trade settlement date. settle is a date/time value. The trade settlement date is usually one or more days after the trade date. ÂÂ maturity: The date when the security matures. maturity is a date/time value. It must be after settle. ÂÂ invest-amount: The amount invested in the security.
Related Topics For related functions and additional information, see: “RECEIVED” on page 146 “Common Arguments Used in Financial Functions” on page 341 “Listing of Financial Functions” on page 96 “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 IPMT The IPMT function returns the interest portion of a specified loan or annuity payment based on fixed, periodic payments and a fixed i
ÂÂ when-due: An optional argument that specifies whether payments are due at the beginning or end of each period. Most mortgage and other loans require the first payment at the end of the first period (0), which is the default. Most lease and rent payments, and some other types of payments, are due at the beginning of each period (1). end (0 or omitted): Payments are due at the end of each period. beginning (1): Payments are due at the beginning of each period.
IRR The IRR function returns the internal rate of return for an investment that is based on a series of potentially irregular cash flows (payments that do not need to be a constant amount) that occur at regular time intervals. IRR(flows-range, estimate) ÂÂ flows-range: A collection that contains the cash flow values. flows-range is a collection containing number values. Income (a cash inflow) is specified as a positive number, and an expenditure (a cash outflow) is specified as a negative number.
Example 2 Assume you are presented with the opportunity to invest in a partnership. The initial investment required is $50,000. Because the partnership is still developing its product, an additional $25,000 and $10,000 must be invested at the end of the first and second years, respectively. In the third year the partnership expects to be self-funding but not return any cash to investors. In the fourth and fifth years, investors are projected to receive $10,000 and $30,000, respectively.
ISPMT(annual-rate, period, num-periods, present-value) ÂÂ annual-rate: The annual coupon rate or stated annual interest rate of the security. annual-rate is a number value and is either entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%). ÂÂ period: The payment period for which you want to calculate the amount of principal or interest. period is a number and must be greater than 0. ÂÂ num-periods: The number of periods.
MIRR The MIRR function returns the modified internal rate of return for an investment that is based on a series of potentially irregular cash flows (payments that do not need to be a constant amount) that occur at regular time intervals. The rate earned on positive cash flows and the rate paid to finance negative cash flows can differ. MIRR(flows-range, finance-rate, reinvest-rate) ÂÂ flows-range: A collection that contains the cash flow values. flows-range is a collection containing number values.
=MIRR (B2:H2, 0.09, 0.0425) Initial Deposit Year 1 Year 2 Year 3 Year 4 Year 5 Sales proceeds -50000 -25000 -10000 0 10000 30000 100000 Example 2 Assume the same information as in Example 1, but rather than placing the cash flows in individual cells, you specify the cash flows as an array constant. The MIRR function would then be as follows: =MIRR({-50000, -25000, -10000, 0, 10000, 30000, 100000}, 0.09, 0.0425) returns approximately 9.75%.
Examples =NOMINAL(0.0513, 365) returns approximately 5.00%, the nominal annual interest rate if the effective rate of 5.13% was based on daily compounding. =NOMINAL(0.0512, 12) returns approximately 5.00%, the nominal annual interest rate if the effective rate of 5.12% was based on monthly compounding. =NOMINAL(0.0509, 4) returns approximately 5.00%, the nominal annual interest rate if the effective rate of 5.09% was based on quarterly compounding. =NOMINAL(0.0506, 2) returns approximately 5.
ÂÂ present value: The value of the initial investment, or the amount of the loan or annuity, specified as a negative number. present-value is a number value. At time 0, an amount received is a positive amount and an amount invested is a negative amount. For example, It could be an amount borrowed (positive) or the initial payment made on an annuity contract (negative).
=NPER(B2, C2, D2, E2, F2) periodic-rate payment present-value future-value when-due =0.
Usage Notes ÂÂ periodic-discount-rate is specified using the same time frame as the time frame used for the cash flows. For example, if the cash flows are monthly and the desired annual discount rate is 8%, periodic-discount-rate must be specified as 0.00667 or 0.667% (0.08 divided by 12). ÂÂ If cash flows are irregular, use the IRR function. Example Assume you are presented with the opportunity to invest in a partnership.
PMT The PMT function returns the fixed periodic payment for a loan or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate. PMT(periodic-rate, num-periods, present-value, future-value, when-due) ÂÂ periodic-rate: The interest rate per period. periodic-rate is a number value and is either entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%).
Related Topics For related functions and additional information, see: “FV” on page 120 “IPMT” on page 123 “NPER” on page 130 “PPMT” on page 135 “PV” on page 141 “RATE” on page 144 “Example of a Loan Amortization Table” on page 353 “Choosing Which Time Value of Money Function to Use” on page 348 “Common Arguments Used in Financial Functions” on page 341 “Listing of Financial Functions” on page 96 “Value Types” on page 36 “The Elements of Formulas” on page 15 “Using the Keyboard and Mouse to Create and Edit F
ÂÂ present-value: The value of the initial investment, or the amount of the loan or annuity. present-value is a number value. At time 0, an amount received is a positive amount and an amount invested is a negative amount. For example, it could be an amount borrowed (positive) or the initial payment made on an annuity contract (negative).
“Listing of Financial Functions” on page 96 “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 PRICE The PRICE function returns the price of a security that pays periodic interest per $100 of redemption (par) value. PRICE(settle, maturity, annual-rate, annual-yield, redemption, frequency, days-basis) ÂÂ settle: The trade settlement date. settle is a date/time value.
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). Example In this example, the PRICE function is used to determine the purchase price when trading the hypothetical security described by the values listed. The security pays periodic interest. The function evaluates to $106.50, which represents the price per $100 of face value.
ÂÂ redemption: The redemption value per $100 of par value. redemption is a number value that must be greater than 0. redemption is the amount that will be received per $100 of face value. Often, it is 100, meaning that the security’s redemption value is equal to its face value. ÂÂ days-basis: An optional argument specifying the number of days per month and days per year used in the calculations.
PRICEMAT The PRICEMAT function returns the price of a security that pays interest only at maturity per $100 of redemption (par) value. PRICEMAT(settle, maturity, issue, annual-rate, annual-yield, days-basis) ÂÂ settle: The trade settlement date. settle is a date/time value. The trade settlement date is usually one or more days after the trade date. ÂÂ maturity: The date when the security matures. maturity is a date/time value. It must be after settle.
Related Topics For related functions and additional information, see: “PRICE” on page 137 “PRICEDISC” on page 138 “YIELDMAT” on page 153 “Common Arguments Used in Financial Functions” on page 341 “Listing of Financial Functions” on page 96 “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 PV The PV function returns the present value of an investment or annuity based on a series of
ÂÂ when-due: An optional argument that specifies whether payments are due at the beginning or end of each period. Most mortgage and other loans require the first payment at the end of the first period (0), which is the default. Most lease and rent payments, and some other types of payments, are due at the beginning of each period (1). end (0 or omitted): Payments are due at the end of each period. beginning (1): Payments are due at the beginning of each period.
Example 2 In this example, you are presented with an investment opportunity. The opportunity is to invest in a discount security today and then pay or receive nothing further until the security matures. The discount security matures in 14 years and has a redemption value of $100,000. Your alternative is to leave your money in your money market savings account where it is expected to earn an annual yield of 5.25%.
RATE The RATE function returns the interest rate of an investment, loan, or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate. RATE(num-periods, payment, present-value, future-value, when-due, estimate) ÂÂ num-periods: The number of periods. num-periods is a number value and must be greater than or equal to 0. ÂÂ payment: The payment made or amount received each period. payment is a number value.
Example Assume you are planning for your daughter’s college education. She has just turned 3 and you expect she will begin college in 15 years. You think you will need to have $150,000 set aside in a savings account by the time she reaches college. You can set aside $50,000 today and add $200 to the account at the end of each month. Over the next 15 years, the savings account is expected to earn an annual interest rate of 4.5%, and earns interest monthly.
RECEIVED The RECEIVED function returns the maturity value for a security that pays interest only at maturity. RECEIVED(settle, maturity, invest-amount, annual-rate, days-basis) ÂÂ settle: The trade settlement date. settle is a date/time value. The trade settlement date is usually one or more days after the trade date. ÂÂ maturity: The date when the security matures. maturity is a date/time value. It must be after settle. ÂÂ invest-amount: The amount invested in the security.
“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 SLN The SLN function returns the depreciation of an asset for a single period using the straight-line method. SLN(cost, salvage, life) ÂÂ cost: The initial cost of the asset. cost is a number value and must be greater than or equal to 0. ÂÂ salvage: The salvage value of the asset.
SYD The SYD function returns the amount of depreciation of an asset for a specified period using the sum-of-the-years-digits method. SYD(cost, salvage, life, depr-period) ÂÂ cost: The initial cost of the asset. cost is a number value and must be greater than or equal to 0. ÂÂ salvage: The salvage value of the asset. salvage is a number value and must be greater than or equal to 0. ÂÂ life: The number of periods over which the asset is depreciating. life is a number value and must be greater than 0.
VDB The VDB (variable declining balance) function returns the amount of depreciation of an asset over a chosen time interval, based on a specified depreciation rate. VDB(cost, salvage, life, starting-per, ending-per, depr-factor, no-switch) ÂÂ cost: The initial cost of the asset. cost is a number value and must be greater than or equal to 0. ÂÂ salvage: The salvage value of the asset. salvage is a number value and must be greater than or equal to 0.
Related Topics For related functions and additional information, see: “DB” on page 114 “DDB” on page 116 “SLN” on page 147 “SYD” on page 148 “Common Arguments Used in Financial Functions” on page 341 “Listing of Financial Functions” on page 96 “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 YIELD The YIELD function returns the effective annual interest rate for a security that pa
ÂÂ 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.
YIELDDISC The YIELDDISC function returns the effective annual interest rate for a security that is sold at a discount to redemption value and pays no interest. YIELDDISC(settle, maturity, price, redemption, days-basis) ÂÂ settle: The trade settlement date. settle is a date/time value. The trade settlement date is usually one or more days after the trade date. ÂÂ maturity: The date when the security matures. maturity is a date/time value. It must be after settle.
Related Topics For related functions and additional information, see: “PRICEDISC” on page 138 “YIELD” on page 150 “YIELDMAT” on page 153 “Common Arguments Used in Financial Functions” on page 341 “Listing of Financial Functions” on page 96 “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 YIELDMAT The YIELDMAT function returns the effective annual interest rate for a security that
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). Example In this example, the YIELDMAT function is used to determine the effective annual yield of the hypothetical security described by the values listed. The security pays interest only at maturity. The function evaluates to 6.565%.
Logical and Information Functions 7 The logical and information functions help you to evaluate the contents of cells and help in determining how to evaluate or otherwise work with cell contents or formula results. Listing of Logical and Information Functions iWork provides these logical and information functions for use with tables. Function Description “AND” (page 156) The AND function returns TRUE if all arguments are true, and FALSE otherwise.
Function Description “ISEVEN” (page 162) The ISEVEN function returns TRUE if the value is even (leaves no remainder when divided by 2); otherwise it returns FALSE. “ISODD” (page 163) The ISODD function returns TRUE if the value is odd (leaves a remainder when divided by 2); otherwise it returns FALSE. “NOT” (page 164) The NOT function returns the opposite of the Boolean value of a specified expression. “OR” (page 165) The OR function returns TRUE if any argument is true; otherwise it returns FALSE.
Related Topics For related functions and additional information, see: “IF” on page 158 “NOT” on page 164 “OR” on page 165 “Specifying Conditions and Using Wildcards” on page 360 “Adding Comments Based on Cell Contents” on page 358 “Using Logical and Information Functions Together” on page 358 “Listing of Logical and Information Functions” on page 155 “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
“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 IF The IF function returns one of two values depending on whether a specified expression evaluates to a Boolean value of TRUE or FALSE. IF(if-expression, if-true, if-false) ÂÂ if-expression: A logical expression. if-expression can contain anything as long as the expression can be evaluated as a Boolean.
Related Topics For related functions and additional information, see: “AND” on page 156 “NOT” on page 164 “OR” on page 165 “Specifying Conditions and Using Wildcards” on page 360 “Trapping Division by Zero” on page 360 “Adding Comments Based on Cell Contents” on page 358 “Using Logical and Information Functions Together” on page 358 “Listing of Logical and Information Functions” on page 155 “Value Types” on page 36 “The Elements of Formulas” on page 15 “Using the Keyboard and Mouse to Create and Edit Formul
Examples If B1 is a number value and D1 evaluates to 0, then: =IFERROR(B1/D1,0) returns 0 since division by zero results in an error. =IF(ISERROR(B1/D1),0,B1/D1) is equivalent to the previous IFERROR example, but requires the use of both IF and ISERROR. =IF(IFERROR(OR(ISEVEN(B4+B5),ISODD(B4+B5), FALSE),), “All numbers”, “Not all numbers”) returns the text “All numbers” if both cells B4 and B5 contain numbers; otherwise the text “Not all numbers.
Examples If the table cell A1 is empty and cell B2 is equal to 100: =ISBLANK(A1) returns TRUE. =ISBLANK(B2) returns FALSE.
Related Topics For related functions and additional information, see: “IFERROR” on page 159 “ISBLANK” on page 160 “Listing of Logical and Information Functions” on page 155 “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 ISEVEN The ISEVEN function returns TRUE if the given number is even (leaves no remainder when divided by 2); otherwise it returns FALSE.
ISODD The ISODD function returns TRUE if the given number is odd (leaves a remainder when divided by 2); otherwise it returns FALSE. ISODD(num) ÂÂ num: A number. num is a number value. Usage Notes ÂÂ If num is text, the function returns an error. If num is the Boolean TRUE (value of 1), the function returns TRUE. If num is the Boolean FALSE (value of 0), the function returns FALSE. Examples =ISODD(3) returns TRUE. =ISODD(3.75) returns TRUE. =ISODD(2) returns FALSE.
NOT The NOT function returns the opposite of the Boolean value of a specified expression. NOT(any-expression) ÂÂ any-expression: An expression to be tested. any-expression can contain anything as long as the expression can be evaluated as a Boolean. If the expression evaluates to a number, 0 is considered to be FALSE, and any other number is considered to be TRUE. Examples =NOT(0) returns TRUE because 0 is interpreted as FALSE.
OR The OR function returns TRUE if any argument is true; otherwise it returns FALSE. OR(any-expression, any-expression…) ÂÂ any-expression: An expression to be tested. any-expression can contain anything as long as the expression can be evaluated as a Boolean. If the expression evaluates to a number, 0 is considered to be FALSE, and any other number is considered to be TRUE. ÂÂ any-expression…: Optionally include one or more additional expressions to be tested.
TRUE The TRUE function returns the Boolean value TRUE. This function is included for compatibility with tables imported from other spreadsheet applications. TRUE() Usage Notes ÂÂ The TRUE function does not have any arguments. However, you must include the parentheses: =TRUE(). ÂÂ Instead of using the TRUE function, you can specify a Boolean value of TRUE by simply typing TRUE (or true) into a cell or function argument. Examples =TRUE() returns the Boolean value TRUE.
8 Numeric Functions The numeric functions help you to calculate commonly used mathematical values. Listing of Numeric Functions iWork provides these numeric functions for use with tables. Function Description “ABS” (page 170) The ABS function returns the absolute value of a number or duration. “CEILING” (page 170) The CEILING function rounds a number away from zero to the nearest multiple of the specified factor.
Function Description “INT” (page 178) The INT function returns the nearest integer that is less than or equal to the number. “LCM” (page 179) The LCM function returns the least common multiple of the specified numbers. “LN” (page 179) The LN function returns the natural logarithm of a number, the power to which e must be raised to result in the number. “LOG” (page 180) The LOG function returns the logarithm of a number using a specified base.
Function Description “ROUND” (page 191) The ROUND function returns a number rounded to the specified number of places. “ROUNDDOWN” (page 192) The ROUNDDOWN function returns a number rounded toward zero (rounded down) to the specified number of places. “ROUNDUP” (page 193) The ROUNDUP function returns a number rounded away from zero (rounded up) to the specified number of places.
ABS The ABS function returns the absolute value of a number or duration. ABS(num-dur) ÂÂ num-dur: A number or duration value. num-dur is a number or duration value. Usage Notes ÂÂ The result returned by ABS is either a positive number or 0. Examples =ABS(A1) returns 5, if cell A1 contains 5. =ABS(8-5) returns 3. =ABS(5-8) returns 3. =ABS(0) returns 0. =ABS(A1) returns 0, if cell A1 is empty.
Examples =CEILING(0.25, 1) returns 1. =CEILING(1.25, 1) returns 2. =CEILING(-1.25, -1) returns -2. =CEILING(5, 2) returns 6. =CEILING(73, 10) returns 80. =CEILING(7, 2.5) returns 7.5.
COMBIN The COMBIN function returns the number of different ways you can combine a number of items into groups of a specific size, ignoring the order within the groups. COMBIN(total-items, group-size) ÂÂ total-items: The total number of items. total-items is a number value and must be greater than or equal to 0. If total-items has a decimal (fractional) part, it is ignored. ÂÂ group-size: The number of items combined in each group. group-size is a number value and must be greater than or equal to 0.
EVEN The EVEN function rounds a number away from zero to the next even number. EVEN(num-to-round) ÂÂ num-to-round: The number to be rounded. num-to-round is a number value. Usage Notes ÂÂ To round to an odd number, use the ODD function. Examples =EVEN(1) returns 2. =EVEN(2) returns 2. =EVEN(2.5) returns 4. =EVEN(-2.5) returns -4. =EVEN(0) returns 0.
EXP The EXP function returns e (the base of natural logarithms) raised to the specified power. EXP(exponent) ÂÂ exponent: The power to which you want to raise e. exponent is a number value. Usage Notes ÂÂ EXP and LN are mathematically inverses over the domain where LN is defined, but because of floating-point rounding, EXP(LN(x)) may not give exactly x. Example =EXP(1) returns 2.71828182845905, an approximation of e.
Related Topics For related functions and additional information, see: “FACTDOUBLE” on page 175 “MULTINOMIAL” on page 184 “Listing of Numeric Functions” on page 167 “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 FACTDOUBLE The FACTDOUBLE function returns the double factorial of a number. FACTDOUBLE(fact-num) ÂÂ fact-num: A number.
“Listing of Numeric Functions” on page 167 “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 FLOOR The FLOOR function rounds a number toward zero to the nearest multiple of the specified factor. FLOOR(num-to-round, factor) ÂÂ num-to-round: The number to be rounded. num-to-round is a number value. ÂÂ factor: The number to use to determine the closet multiple.
“TRUNC” on page 204 “More on Rounding” on page 355 “Listing of Numeric Functions” on page 167 “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 GCD The GCD function returns the greatest common divisor of the specified numbers. GCD(num-value, num-value…) ÂÂ num-value: A number. num-value is a number value. If there is a decimal portion it is ignored.
INT The INT function returns the nearest integer that is less than or equal to the number. INT(num-to-round) ÂÂ num-to-round: The number to be rounded. num-to-round is a number value. Examples =INT(1.49) returns 1. =INT(1.50) returns 1. =INT(1.23456) returns 1. =INT(1111.222) returns 1111. =INT(-2.2) returns -3. =INT(-2.8) returns -3.
LCM The LCM function returns the least common multiple of the specified numbers. LCM(num-value, num-value…) ÂÂ num-value: A number. num-value is a number value. ÂÂ num-value…: Optionally include one or more additional numbers. Usage Notes ÂÂ Sometimes called the lowest or smallest common multiple, the least common multiple is the smallest integer that is a multiple of the specified numbers. Examples =LCM(2, 3) returns 6. =LCM(34, 68) returns 68. =LCM(30, 40, 60) returns 120. =LCM(30.25, 40.333, 60.
Example =LN(2.71828) returns approximately 1, the power to which e must be raised to produce 2.71828.
“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 LOG10 The LOG10 function returns the base-10 logarithm of a number. LOG10(pos-num) ÂÂ pos-num: A positive number. pos-num is a number value and must be greater than 0. Usage Notes ÂÂ To find the logarithm for a base other than 10, use the LOG function. Examples =LOG10(1) returns 0. =LOG10(10) returns 1. =LOG10(100) returns 2.
MOD The MOD function returns the remainder from a division. MOD(dividend, divisor) ÂÂ dividend: A number to be divided by another number. dividend is a number value. ÂÂ divisor: A number to divide into another number. divisor is a number value. If 0, a division by zero will result and the function will return an error. Usage Notes ÂÂ The sign of the result matches that of the divisor.
MROUND The MROUND function rounds a number to the nearest multiple of a specified factor. MROUND(num-to-round, factor) ÂÂ num-to-round: The number to be rounded. num-to-round is a number value. ÂÂ factor: The number to use to determine the closet multiple. factor is a number value. It must have the same sign as num-to-round. Examples =MROUND(2, 3) returns 3. =MROUND(4, 3) returns 3. =MROUND(4.4999, 3) returns 3. =MROUND(4.5, 3) returns 6. =MROUND(-4.5, 3) returns an error.
MULTINOMIAL The MULTINOMIAL function returns the multinomial coefficient of the given numbers. It accomplishes this by determining the ratio of the factorial of the sum of the given numbers to the product of the factorials of the given numbers. MULTINOMIAL(non-neg-num, non-neg-num…) ÂÂ non-neg-num: A number. non-neg-num is a number value and must be greater than or equal to 0. ÂÂ non-neg-num…: Optionally include one or more additional numbers. Examples =MULTINOMIAL(2) returns 1.
ODD The ODD function rounds a number away from zero to the next odd number. ODD(num-to-round) ÂÂ num-to-round: The number to be rounded. num-to-round is a number value. Usage Notes ÂÂ To round to an even number, use the EVEN function. Examples =ODD(1) returns 1. =ODD(2) returns 3. =ODD(2.5) returns 3. =ODD(-2.5) returns -3. =ODD(0) returns 1.
PI The PI function returns the approximate value of π (pi), the ratio of a circle’s circumference to its diameter. PI() Usage Notes ÂÂ The PI function does not have any arguments. However, you must include the parentheses: =PI(). ÂÂ PI is accurate to 15 decimal places. Examples =PI() returns 3.14159265358979. =SIN(PI()/2) returns 1, the sine of π/2 radians or 90 degrees.
Examples =POWER(2, 3) returns 8. =POWER(2, 10) returns 1024. =POWER(0.5, 3) returns 0.125. =POWER(100, 0.5) returns 10. Related Topics For related functions and additional information, see: “Listing of Numeric Functions” on page 167 “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 PRODUCT The PRODUCT function returns the product of one or more numbers.
“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26 “Pasting from Examples in Help” on page 41 QUOTIENT The QUOTIENT function returns the integer quotient of two numbers. QUOTIENT(dividend, divisor) ÂÂ dividend: A number to be divided by another number. dividend is a number value. ÂÂ divisor: A number to divide into another number. divisor is a number value. If 0, a division by zero will result and the function will return an error.
RAND The RAND function returns a random number that is greater than or equal to 0 and less than 1. RAND() Usage Notes ÂÂ The RAND function does not have any arguments. However, you must include the parentheses: =RAND(). ÂÂ Any time you change a value in the table, a new random number greater than or equal to 0 and less than 1 is generated. Example =RAND() returns, for example, 0.217538648284972, 0.6137690856, 0.0296026556752622, and 0.4684193600 for four recalculations.
Related Topics For related functions and additional information, see: “RAND” on page 189 “Listing of Numeric Functions” on page 167 “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 ROMAN The ROMAN function converts a number to Roman numerals. ROMAN(arabic-num, roman-style) ÂÂ arabic-num: The Arabic numeral that you want to convert.
Examples =ROMAN(12) returns XII. =ROMAN(999) returns CMXCIX. =ROMAN(999, 1) returns LMVLIV. =ROMAN(999, 2) returns XMIX. =ROMAN(999, 3) returns VMIV.
“EVEN” on page 173 “FLOOR” on page 176 “INT” on page 178 “MROUND” on page 183 “ODD” on page 185 “ROUNDDOWN” on page 192 “ROUNDUP” on page 193 “TRUNC” on page 204 “More on Rounding” on page 355 “Listing of Numeric Functions” on page 167 “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 ROUNDDOWN The ROUNDDOWN function returns a number rounded toward zero (rounded down) to the specif
Related Topics For related functions and additional information, see: “CEILING” on page 170 “EVEN” on page 173 “FLOOR” on page 176 “INT” on page 178 “MROUND” on page 183 “ODD” on page 185 “ROUND” on page 191 “ROUNDUP” on page 193 “TRUNC” on page 204 “More on Rounding” on page 355 “Listing of Numeric Functions” on page 167 “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 ROUNDUP Th
Examples =ROUNDUP(1.49, 0) returns 2. =ROUNDUP(1.50, 0) returns 2. =ROUNDUP(1.23456, 3) returns 1.235. =ROUNDUP(1111.222, -2) returns 1200. =ROUNDUP(-2.2, 0) returns -3. =ROUNDUP(-2.8, 0) returns -3.
SIGN The SIGN function returns 1 when the argument number is positive, –1 when it is negative, and 0 when it is zero. SIGN(num) ÂÂ num: A number. number is a number value. Examples =SIGN(2) returns 1. =SIGN(0) returns 0. =SIGN(-2) returns -1. =SIGN(A4) returns -1, if cell A4 contains -2.
“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26 “Pasting from Examples in Help” on page 41 SQRTPI The SQRTPI function returns the square root of a number after it has been multiplied by π (pi). SQRTPI(non-neg-number) ÂÂ non-neg-number: A nonnegative number. non-neg-num is a number value and must be greater than or equal to 0. Examples =SQRTPI(5) returns 3.96332729760601. =SQRTPI(8) returns 5.013256549262.
ÂÂ Date/time values can’t be added together, so only one date/time value (as discussed above) is permitted. ÂÂ The values can be in individual cells, ranges of cells, or included directly as arguments to the function. Examples =SUM(A1:A4) adds the numbers in four cells. =SUM(A1:D4) adds the numbers in a square array of sixteen cells. =SUM(A1:A4, 100) adds the numbers in four cells plus 100.
ÂÂ If sum-values is omitted, test-values would normally contain only number or duration values. Examples Given the following table: =SUMIF(A1:A8, “<5”) returns 10. =SUMIF(A1:A8, “<5”, B1:B8) returns 100. =SUMIF(D1:F3, “=c”, D5:F7) returns 27. =SUMIF(B1:D1, 1) or SUMIF(B1:D1, SUM(1)) both total all the occurrences of 1 in the range.
SUMIFS(sum-values, test-values, condition, test-values…, condition…) ÂÂ sum-values: A collection containing the values to be summed. sum-values is a collection containing number, date/time, or duration values. ÂÂ test-values: A collection containing values to be tested. test-values is a collection containing any type of value. ÂÂ condition: An expression that results in a logical TRUE or FALSE.
Related Topics For related functions and additional information, see: “AVERAGEIF” on page 233 “AVERAGEIFS” on page 234 “COUNTIF” on page 247 “COUNTIFS” on page 248 “SUMIF” on page 197 “Specifying Conditions and Using Wildcards” on page 360 “Listing of Numeric Functions” on page 167 “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 SUMPRODUCT The SUMPRODUCT function returns the sum
Related Topics For related functions and additional information, see: “Listing of Numeric Functions” on page 167 “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 SUMSQ The SUMSQ function returns the sum of the squares of a collection of numbers. SUMSQ(num-value, num-value…) ÂÂ num-value: A number. num-value is a number value.
SUMX2MY2 The SUMX2MY2 function returns the sum of the difference of the squares of corresponding values in two collections. SUMX2MY2(set-1-values, set-2-values) ÂÂ set-1-values: The first collection of values. set-1-values is a collection containing number values. ÂÂ set-2-values: The second collection of values. set-2-values is a collection containing number values.
SUMX2PY2 The SUMX2PY2 function returns the sum of the squares of corresponding values in two collections. SUMX2PY2(set-1-values, set-2-values) ÂÂ set-1-values: The first collection of values. set-1-values is a collection containing number values. ÂÂ set-2-values: The second collection of values. set-2-values is a collection containing number values.
SUMXMY2 The SUMXMY2 function returns the sum of the squares of the differences between corresponding values in two collections. SUMXMY2(set-1-values, set-2-values) ÂÂ set-1-values: The first collection of values. set-1-values is a collection containing number values. ÂÂ set-2-values: The second collection of values. set-2-values is a collection containing number values.
Usage Notes ÂÂ If digits is omitted, it is assumed to be 0. Examples =TRUNC(1.49, 0) returns 1. =TRUNC(1.50, 0) returns 1. =TRUNC(1.23456, 3) returns 1.234. =TRUNC(1111.222, -2) returns 1100. =TRUNC(-2.2, 0) returns -2. =TRUNC(-2.8, 0) returns -2.
9 Reference Functions The reference functions help you find data within tables and retrieve data from cells. Listing of Reference Functions iWork provides these reference functions for use with tables. 206 Function Description “ADDRESS” (page 207) The ADDRESS function constructs a cell address string from separate row, column, and table identifiers. “AREAS” (page 209) The AREAS function returns the number of ranges the function references.
Function Description “LOOKUP” (page 217) The LOOKUP function finds a match for a given search value in one range, and then returns the value in the cell with the same relative position in a second range. “MATCH” (page 218) The MATCH function returns the position of a value within a range. “OFFSET” (page 219) The OFFSET function returns a range of cells that is the specified number of rows and columns away from the specified base cell.
A1 (TRUE, 1, or omitted): The address format should use letters for columns and numbers for rows. R1C1 (FALSE): The address format isn’t supported, returning an error. ÂÂ table: An optional value specifying the name of the table. table is a string value. If the table is on another sheet, you must also include the name of the sheet. If omitted, table is assumed to be the current table on the current sheet (that is, the table where the ADDRESS function resides).
AREAS The AREAS function returns the number of ranges the function references. AREAS(areas) ÂÂ areas: The areas the function should count. areas is a list value. It is either a single range or more than one range separated by commas and enclosed in an additional set of parentheses; for example, AREAS((B1:B5, C10:C12)). Examples =AREAS(A1:F8) returns 1. =AREAS(C2:C8 B6:E6) returns 1. =AREAS((A1:F8, A10:F18)) returns 2. =AREAS((A1:C1, A3:C3, A5:C5)) returns 3.
Related Topics For related functions and additional information, see: “Listing of Reference Functions” on page 206 “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 COLUMN The COLUMN function returns the column number of the column containing a specified cell. COLUMN(cell) ÂÂ cell: An optional reference to a single table cell.
COLUMNS The COLUMNS function returns the number of columns included in a specified range of cells. COLUMNS(range) ÂÂ range: A range of cells. range is a reference to a single range of cells, which may contain values of any type. Usage Notes ÂÂ If you select an entire table row for range, COLUMNS returns the total number of columns in the row, which changes when you resize the table. Examples =COLUMNS(B3:D10) returns 3, the number of columns in the range (columns B, C, and D).
close match (TRUE, 1, or omitted): If there’s no exact match, select the column with the largest top-row value that is less than the search value. Wildcards can’t be used in search-for. exact match (FALSE or 0): If there’s no exact match, return an error. Wildcards can be used in search-for. Usage Notes ÂÂ HLOOKUP compares a search value to the values in the top row of a specified range.
“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 HYPERLINK The HYPERLINK function creates a clickable link that opens a webpage or new email message. HYPERLINK(url, link-text) ÂÂ url: A standard universal resource locator. url is a string value that should contain a properly formatted universal resource locator string.
INDEX The INDEX function returns the value in the cell located at the intersection of the specified row and column within a range of cells or an array. INDEX(range, row-index, column-index, area-index) ÂÂ range: A range of cells. range may contain values of any type. range is either a single range or more than one range separated by commas and enclosed in an additional set of parentheses. For example, ((B1:B5, C10:C12)). ÂÂ row-index: The row number of the value to be returned.
ÂÂ Except when INDEX is specified as shown in the third case above, row-index can’t be omitted, and if column-index is omitted, it is assumed to be 1. Examples Given the following table: =INDEX(B2:D5,2,3) returns 22, the value in the second row and third column (cell D3). =INDEX((B2:D5,B7:D10), 2, 3, 2) returns “f”, the value in the second row and third column of the second area (cell D8). =SUM(INDEX(B2:D5, , 3)) returns 90, the sum of the values in the third column (cells D2 through D5).
INDIRECT The INDIRECT function returns the contents of a cell or range referenced by an address specified as a string. INDIRECT(addr-string, addr-style) ÂÂ addr-string: A string representing a cell address. addr-string is a string value. ÂÂ addr-style: An optional value specifying the address style. A1 (TRUE, 1, or omitted): The address format should use letters for columns and numbers for rows. R1C1 (FALSE): The address format isn’t supported, returning an error.
LOOKUP The LOOKUP function finds a match for a given search value in one range, and then returns the value in the cell with the same relative position in a second range. LOOKUP(search-for, search-where, result-values) ÂÂ search-for: The value to find. search-value can contain any value type. ÂÂ search-where: The collection containing the values to be searched. search-where is a collection containing any value type.
Related Topics For related functions and additional information, see: “HLOOKUP” on page 211 “MATCH” on page 218 “VLOOKUP” on page 223 “Listing of Reference Functions” on page 206 “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 MATCH The MATCH function returns the position of a value within a range.
Examples Given the following table: =MATCH(40, A1:A5) returns 4. =MATCH(40, E1:E5) returns 1. =MATCH(35, E1:E5, 1) returns 3 (30 is the largest value less than or equal to 35). =MATCH(35, E1:E5, -1) returns 1 (40 is the smallest value greater than or equal to 35). =MATCH(35, E1:E5, 0) displays an error (no exact match can be found). =MATCH(”lorem”, C1:C5) returns 1 (“lorem” appears in the first cell of the range).
ÂÂ column-offset: The number of columns from the base cell to the target cell. column- offset is a number value. 0 means the target cell is in the same column as the base cell. A negative number means the target is in a column to the left of the base. ÂÂ rows: An optional value specifying the number of rows to return starting with the offset location.rows is a number value. ÂÂ columns: An optional value specifying the number of columns to return starting with the offset location.
ROW The ROW function returns the row number of the row containing a specified cell. ROW(cell) ÂÂ cell: An optional reference to a single table cell. cell is a reference value to a single cell that can contain any value, or be empty. If cell is omitted, as in =ROW(), the function returns the row number of the cell that contains the formula. Examples =ROW(B7) returns 7, the number of row 7. =ROW() returns the absolute row number of the cell containing the function.
Related Topics For related functions and additional information, see: “COLUMNS” on page 211 “Listing of Reference Functions” on page 206 “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 TRANSPOSE The transpose function returns a vertical range of cells as a horizontal range of cells, or vice versa.
=INDEX(TRANSPOSE($A$1:$E$3),1,1) returns 5, the value in row 1. column 1 of the transposed range (was row 1, column A, of the original array). =INDEX(TRANSPOSE($A$1:$E$3),1,2) returns 11, the value in row 1, column 2 of the transposed range (was row 2, column A, of the original range). =INDEX(TRANSPOSE($A$1:$E$3),1,3) returns 37, the value in row 1, column 3 of the transposed range (was row 3, column A, of the original range).
Usage Notes ÂÂ VLOOKUP compares a search value to the values in the leftmost column of a specified range. Unless an exact match is required, the row containing the largest left-column value that is less than the search value is selected. Then, the value from the specified column in that row is returned by the function. If an exact match is required and none of the leftmost-column values match the search value, the function returns an error.
10 Statistical Functions The statistical functions help you manipulate and analyze collections of data using a variety of measures and statistical techniques. Listing of Statistical Functions iWork provides these statistical functions for use with tables. Function Description “AVEDEV” (page 230) The AVEDEV function returns the average of the difference of a collection of numbers from their average (arithmetic mean).
Function Description “BETAINV” (page 237) The BETAINV function returns the inverse of the given cumulative beta distribution probability value. “BINOMDIST” (page 238) The BINOMDIST function returns the individual term binomial distribution probability of the specified form. “CHIDIST” (page 239) The CHIDIST function returns the one-tailed probability of the chi-square distribution.
Function Description “EXPONDIST” (page 253) The EXPONDIST function returns the exponential distribution of the specified form. “FDIST” (page 254) The FDIST function returns the F probability distribution. “FINV” (page 255) The FINV function returns the inverse of the F probability distribution. “FORECAST” (page 256) The FORECAST function returns the forecasted y value for a given x value based on sample values using linear regression analysis.
Function Description “MAXA” (page 270) The MAXA function returns the largest number in a collection of values that may include text and Boolean values. “MEDIAN” (page 271) The MEDIAN function returns the median value in a collection of numbers. The median is the value where half the numbers in the collection are less than the median and half are greater. “MIN” (page 272) The MIN function returns the smallest number in a collection.
Function Description “QUARTILE” (page 284) The QUARTILE function returns the value for the specified quartile of a given collection. “RANK” (page 285) The RANK function returns the rank of a number within a range of numbers. “SLOPE” (page 287) The SLOPE function returns the slope of the bestfit line for the collection using linear regression analysis. “SMALL” (page 288) The SMALL function returns the nth-smallest value within a range. The smallest value is ranked number 1.
Function Description “VARA” (page 300) The VARA function returns the sample (unbiased) variance, a measure of dispersion, of a collection of values, including text and Boolean values. “VARP” (page 302) The VARP function returns the population (true) variance, a measure of dispersion, of a collection of values. “VARPA” (page 303) The VARPA function returns the sample (unbiased) variance, a measure of dispersion, of a collection of values, including text and Boolean values.
AVERAGE The AVERAGE function returns the average (arithmetic mean) of a collection of numbers. AVERAGE(num-date-dur, num-date-dur…) ÂÂ num-date-dur: A value. num-date-dur is a number value, a date/time value, or a duration value. ÂÂ num-date-dur…: Optionally include one or more additional values. If more than one num-date-dur value is specified, all must be of the same type. Usage Notes ÂÂ AVERAGE divides the sum of the numbers by the number of numbers.
AVERAGEA The AVERAGEA function returns the average (arithmetic mean) of a collection of values, including text and Boolean values. AVERAGEA(value, value…) ÂÂ value: A value. value can contain any value type. ÂÂ value…: Optionally include one or more additional values. All numeric values must be of the same type. You cannot mix numbers, dates, and duration values. Usage Notes ÂÂ A string value included in a referenced cell is given a value of 0.
AVERAGEIF The AVERAGEIF function returns the average (arithmetic mean) of the cells in a range that meet a given condition. AVERAGEIF(test-values, condition, avg-values) ÂÂ test-values: A collection containing values to be tested. test-values is a collection containing any type of value. ÂÂ condition: An expression that results in a logical TRUE or FALSE.
Related Topics For related functions and additional information, see: “AVERAGE” on page 231 “AVERAGEA” on page 232 “AVERAGEIFS” on page 234 “Specifying Conditions and Using Wildcards” on page 360 “Listing of Statistical Functions” on page 225 “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 AVERAGEIFS The AVERAGEIFS function returns the average (arithmetic mean) of the cells in a
Usage Notes ÂÂ For each of the test-values and condition pairs, the corresponding (same position within range or array) value is compared to the conditional test. If all of the conditional tests are met, the corresponding value in avg-values is included in the average. ÂÂ avg-values and all test-values collections must be the same size.
“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26 “Pasting from Examples in Help” on page 41 BETADIST The BETADIST function returns the cumulative beta distribution probability value. BETADIST(x-value, alpha, beta, x-lower, x-upper) ÂÂ x-value: The x value at which you want to evaluate the function. x-value is a number value and must be in the range 0 to 1. ÂÂ alpha: One of the shape parameters of the distribution. alpha is a number value and must be greater than 0.
BETAINV The BETAINV function returns the inverse of the given cumulative beta distribution probability value. BETAINV(probability, alpha, beta, x-lower, x-upper) ÂÂ probability: A probability associated with the distribution. probability is a number value and must be greater than 0 and less than 1. ÂÂ alpha: One of the shape parameters of the distribution. alpha is a number value and must be greater than 0. ÂÂ beta: One of the shape parameters of the distribution.
BINOMDIST The BINOMDIST function returns the individual term binomial distribution probability of the specified form. BINOMDIST(success-num, trials, prob-success, form-type) ÂÂ success-num: The number of successful trials or tests. success-num is a number value that must be greater than or equal to 1 and less than or equal to trials. ÂÂ trials: The total number of trials or tests. trials is a number value that must be greater than or equal to 0.
CHIDIST The CHIDIST function returns the one-tailed probability of the chi-square distribution. CHIDIST(non-neg-x-value, degrees-freedom) ÂÂ non-neg-x-value: The value at which you want to evaluate the function. non-neg-x- value is a number value that must be greater than or equal to 0. ÂÂ degrees-freedom: Degrees of freedom. degrees-freedom is a number value and must be greater than or equal to 1. Examples =CHIDIST(5, 2) returns 0.0820849986238988. =CHIDIST(10, 10) returns 0.440493285065212.
Examples =CHIINV(0.5, 2) returns 1.38629436111989. =CHIINV(0.1, 10) returns 15.9871791721053. =CHIINV(0.5, 1) returns 0.454936423119572.
Example Given the following table: =CHITEST(A2:B6,A9:B13) returns 5.91020074984668E-236. Each expected value is computed by multiplying the sum of the row by the sum of the column and dividing by the grand total. The formula for the first expected value (cell A9) is =SUM(A$2:B$2)*SUM($A2:$A6)/SUM($A$2:$B$6). This formula can be extended to cell B9 and then A9:B9 extended to A13:B13 to complete the expected values.
CONFIDENCE The CONFIDENCE function returns a value for creating a statistical confidence interval for a sample from a population with a known standard deviation. CONFIDENCE(alpha, stdev, sample-size) ÂÂ alpha: The probability that the true population value lies outside the interval. alpha is a number value and must be greater than or equal to 1. Subtracting the confidence interval from 1 yields the alpha. ÂÂ stdev: The standard deviation of the population.
CORREL(y-values, x-values) ÂÂ y-values: The collection containing the y (dependent) values. y-values is a collection that can contain number, date/time, or duration values. All values must be of the same type. ÂÂ x-values: The collection containing the x (independent) values. x-values is a collection that can contain number, date/time, or duration values. All values must be of the same type. Usage Notes ÂÂ y-values and x-values must have the same dimensions.
COUNT The COUNT function returns the number of its arguments that contain numbers, numeric expressions, or dates. COUNT(value, value…) ÂÂ value: A value. value can contain any value type. ÂÂ value…: Optionally include one or more additional values. Usage Notes ÂÂ To count any cell that contains any type of value (that is, any cell that is not empty), use the COUNTA function. Examples The table in this example is used to illustrate all variations of the COUNT function.
“Listing of Statistical Functions” on page 225 “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 COUNTA The COUNTA function returns the number of its arguments that are not empty. COUNTA(value, value…) ÂÂ value: A value. value can contain any value type. ÂÂ value…: Optionally include one or more additional values.
Related Topics For related functions and additional information, see: “COUNT” on page 244 “COUNTBLANK” on page 246 “COUNTIF” on page 247 “COUNTIFS” on page 248 “Survey Results Example” on page 362 “Listing of Statistical Functions” on page 225 “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 COUNTBLANK The COUNTBLANK function returns the number of cells in a range that are empty.
Related Topics For related functions and additional information, see: “COUNT” on page 244 “COUNTA” on page 245 “COUNTIF” on page 247 “COUNTIFS” on page 248 “Survey Results Example” on page 362 “Listing of Statistical Functions” on page 225 “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 COUNTIF The COUNTIF function returns the number of cells in a range that satisfy a given condi
Examples The table in this example is used to illustrate all variations of the COUNT function, including COUNTIF. The information is not meaningful, but does illustrate what type of arguments each variation of COUNT includes in the function result. =COUNTIF(A1:E1, “>0”) returns 5, as all cells in the range have a value greater than zero. =COUNTIF(A3:E3, “>=100”) returns 3, as all three numbers are greater than 100 and the two text values are ignored in the comparison.
COUNTIFS(test-values, condition, test-values…, condition…) ÂÂ test-values: A collection containing values to be tested. test-values is a collection containing any type of value. ÂÂ condition: An expression that results in a logical TRUE or FALSE. condition is an expression that can contain anything as long as the result from comparing condition to a value in test-values can be expressed as a Boolean value of TRUE or FALSE.
Related Topics For related functions and additional information, see: “COUNT” on page 244 “COUNTA” on page 245 “COUNTBLANK” on page 246 “COUNTIF” on page 247 “Specifying Conditions and Using Wildcards” on page 360 “Listing of Statistical Functions” on page 225 “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 COVAR The COVAR function returns the covariance of two collections.
Example In this example, the COVAR function is used to determine how closely related the price of heating oil (column A) is to the temperature that this hypothetical homeowner has set on the thermostat. =COVAR(A2:A11, B2:B11) evaluates to approximately -1.6202, indicating a correlation (as prices rose, the thermostat was lowered).
CRITBINOM The CRITBINOM function returns the smallest value for which the cumulative binomial distribution is greater than or equal to a given value. CRITBINOM(trials, prob-success, alpha) ÂÂ trials: The total number of trials or tests. trials is a number value that must be greater than or equal to 0. ÂÂ prob-success: The probability of success for each trial or test. prob-success is a number value that must be greater than or equal to 0 and less than or equal to 1.
DEVSQ The DEVSQ function returns the sum of the squares of deviations of a collection of numbers from their average (arithmetic mean). DEVSQ(num-value, num-value…) ÂÂ num-value: A number. num-value is a number value. ÂÂ num-value…: Optionally include one or more additional numbers. Usage Notes ÂÂ DEVSQ divides the sum of the numbers by the number of numbers to get the average (arithmetic mean).
Examples =EXPONDIST(4, 2, 1) returns 0.999664537372097 (cumulative distribution form). =EXPONDIST(4, 2, 0) returns 0.000670925255805024 (probability density form).
“FINV” on page 255 “Listing of Statistical Functions” on page 225 “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 FINV The FINV function returns the inverse of the F probability distribution. FINV(prob, d-f-numerator, d-f-denominator) ÂÂ prob: A probability associated with the distribution. prob is a number value and must be greater than 0 and less than or equal to 1.
FORECAST The FORECAST function returns the forecasted y value for a given x based on sample values using linear regression analysis. FORECAST(x-num-date-dur, y-values, x-values) ÂÂ x-num-date-dur: The x value for which the function should return a forecasted y value. x-num-date-dur is a number value, a date/time value, or a duration value. ÂÂ y-values: The collection containing the y (dependent) values. y-values is a collection that can contain number, date/time, or duration values.
“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 FREQUENCY The FREQUENCY function returns an array of how often data values occur within a range of interval values. FREQUENCY(data-values, interval-values) ÂÂ data-values: A collection containing the values to be evaluated. data-values is a collection containing number or date/time values. All values should be of the same type.
Example Assume the following table contains the test scores of 30 students who recently took an exam you administered. Assume further that that the minimum passing grade is 65 and that the lowest score for other grades are as given. In order to facilitate building the formulas, an “F” is represented by 1 and an “A” by 5. =INDEX(FREQUENCY($A$1:$F$5, $B$8:$E$8), B9) returns 5, the number of students who received an “F” (score of 65 or less).
GAMMADIST The GAMMADIST function returns the gamma distribution in the specified form. GAMMADIST(non-neg-x-value, alpha, beta, form-type) ÂÂ non-neg-x-value: The value at which you want to evaluate the function. non-neg-x- value is a number value that must be greater than or equal to 0. ÂÂ alpha: One of the shape parameters of the distribution. alpha is a number value and must be greater than 0. ÂÂ beta: One of the shape parameters of the distribution.
GAMMAINV The GAMMAINV function returns the inverse gamma cumulative distribution. GAMMAINV(probability, alpha, beta) ÂÂ probability: A probability associated with the distribution. probability is a number value and must be greater than 0 and less than 1. ÂÂ alpha: One of the shape parameters of the distribution. alpha is a number value and must be greater than 0. ÂÂ beta: One of the shape parameters of the distribution. beta is a number value and must be greater than 0. Examples =GAMMAINV(0.
Related Topics For related functions and additional information, see: “GAMMADIST” on page 259 “GAMMAINV” on page 260 “LN” on page 179 “Listing of Statistical Functions” on page 225 “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 GEOMEAN The GEOMEAN function returns the geometric mean. GEOMEAN(pos-num, pos-num…) ÂÂ pos-num: A positive number.
“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26 “Pasting from Examples in Help” on page 41 HARMEAN The HARMEAN function returns the harmonic mean. HARMEAN(pos-num, pos-num…) ÂÂ pos-num: A positive number. a-pos-num is a number value and must be greater than 0. ÂÂ pos-num…: Optionally include one or more additional positive numbers. Usage Notes ÂÂ The harmonic mean is the reciprocal of the arithmetic mean of the reciprocals. Example =HARMEAN(5, 7, 3, 2, 6, 22) returns 4.
ÂÂ x-numbers: The collection containing the x (independent) values. x-numbers is a collection containing number values. Usage Notes ÂÂ The two arrays must be of the same size. ÂÂ To find the slope of the best-fit line, use the SLOPE function.
LARGE The LARGE function returns the nth-largest value within a collection. The largest value is ranked number 1. LARGE(num-date-dur-set, ranking) ÂÂ num-date-dur-set: A collection of values. num-date-dur-set is a collection containing number, date, or duration values. All values must be of the same type. ÂÂ ranking: A number representing the size ranking of the value you want to retrieve. ranking is a number value and must be in the range of 1 to the number of values in the collection.
“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26 “Pasting from Examples in Help” on page 41 LINEST The LINEST function returns an array of the statistics for a straight line that best fits the given data using the “least squares” method. LINEST(known-y-values, known-x-values, nonzero-y-intercept, more-stats) ÂÂ known-y-values: The collection containing the known y values. known-y-values is a collection containing number values.
If additional statistics are not returned (stats is FALSE), the array returned is one row deep. The number of columns is equal to the the number of sets of known-x-values plus 1. It contains the line slopes (one value for each row/column of x values) in reverse order (the first value relates to the last row/column of x values) and then the value for b, the intercept. If additional statistics are returned (stats is TRUE), the array contains five rows.
Additional Statistics This section discusses the additional statistics that can be returned by the LINEST function. LINEST can include additional statistical information in the array returned by the function. For purposes of the following discussion, assume that there are five sets of known x values, in addition to the known y values. Assume further that the known x values are in five table rows or five table columns.
reg-ss: The regression sum of squares. reside-ss: The residual sum of squares. Usage Notes ÂÂ It does not matter whether the known x values and known y values are in rows or columns. In either case, the returned array is ordered by rows as illustrated in the table. ÂÂ The example assumed five sets of known x values.
“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 LOGNORMDIST The LOGNORMDIST function returns the log-normal distribution. LOGNORMDIST(pos-x-value, mean, stdev) ÂÂ pos-x-value: The positive x value at which you want to evaluate the function. pos-x- value is a number value that must be greater than 0. ÂÂ mean: The mean of the natural logarithm, that is, ln(x).
MAX The MAX function returns the largest number in a collection. MAX(value, value…) ÂÂ value: A value. value can contain any value type. ÂÂ value…: Optionally include one or more additional values. Usage Notes ÂÂ If value does not evaluate to a date or number, it is not included in the result. ÂÂ To determine the largest of any type of value in a collection, use the MAXA function. Examples =MAX(5, 5, 5, 5, 6) returns 6. =MAX(1, 2, 3, 4, 5) returns 5.
Usage Notes ÂÂ Text values and logical FALSE are given a value of 0 and logical TRUE is given a value of 1. ÂÂ To determine the largest value of a collection that contains only numbers or dates, use the MAX function. Examples =MAXA(1, 2, 3, 4) returns 4. =MAXA(A1:C1), where A1:C1 contains -1, -10, hello, returns 0.
Examples =MEDIAN(1, 2, 3, 4, 5) returns 3. =MEDIAN(1, 2, 3, 4, 5, 6) returns 3.5. =MEDIAN(5, 5, 5, 5, 6) returns 5.
“MINA” on page 273 “SMALL” on page 288 “Listing of Statistical Functions” on page 225 “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 MINA The MINA function returns the smallest number in a collection of values that may include text and Boolean values. MINA(value, value…) ÂÂ value: A value. value can contain any value type.
“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26 “Pasting from Examples in Help” on page 41 MODE The MODE function returns the most frequently occurring value in a collection of numbers. MODE(num-date-dur, num-date-dur…) ÂÂ num-date-dur: A value. num-date-dur is a number value, a date/time value, or a duration value. ÂÂ num-date-dur…: Optionally include one or more additional values. If more than one num-date-dur value is specified, all must be of the same type.
NEGBINOMDIST The NEGBINOMDIST function returns the negative binomial distribution. NEGBINOMDIST(f-num, s-num, prob-success) ÂÂ f-num: The number of failures. f-num is a number value and must be greater than or equal to 0. ÂÂ s-num: The number of successful trials or tests. s-num is a number value that must be greater than or equal to 1. ÂÂ prob-success: The probability of success for each trial or test. prob-success is a number value that must be greater than 0 and less than 1.
NORMDIST The NORMDIST function returns the normal distribution of the specified function form. NORMDIST(num, average, stdev, form-type) ÂÂ num: The number to be evaluated. num is a number value. ÂÂ average: The average of the distribution. average is a number value representing the known average (arithmetic mean) rate at which events occur. ÂÂ stdev: The standard deviation of the population. stdev is a number value and must be greater than 0.
NORMINV The NORMINV function returns the inverse of the cumulative normal distribution. NORMINV(probability, average, stdev) ÂÂ probability: A probability associated with the distribution. probability is a number value and must be greater than 0 and less than 1. ÂÂ average: The average of the distribution. average is a number value representing the known average (arithmetic mean) rate at which events occur. ÂÂ stdev: The standard deviation of the population.
Example =NORMSDIST(4.3) returns 0.999991460094529. Related Topics For related functions and additional information, see: “NORMDIST” on page 276 “NORMSINV” on page 278 “Listing of Statistical Functions” on page 225 “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 NORMSINV The NORMSINV function returns the inverse of the cumulative standard normal distribution.
“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 PERCENTILE The PERCENTILE function returns the value within a collection that corresponds to a particular percentile. PERCENTILE(num-date-dur-set, percentile-value) ÂÂ num-date-dur-set: A collection of values. num-date-dur-set is a collection containing number, date, or duration values. All values must be of the same type.
“QUARTILE” on page 284 “Listing of Statistical Functions” on page 225 “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 PERCENTRANK The PERCENTRANK function returns the rank of a value in a collection as a percentage of the collection. PERCENTRANK(num-date-dur-set, num-date-dur, significance) ÂÂ num-date-dur-set: A collection of values.
“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 PERMUT The PERMUT function returns the number of permutations for a given number of objects that can be selected from a total number of objects. PERMUT(num-objects, num-elements) ÂÂ num-objects: The total number of objects. num-objects is a number value and must be greater than or equal to 0.
POISSON The POISSON function returns the probability that a specific number of events will occur using the Poisson distribution. POISSON(events, average, form-type) ÂÂ events: The number of events (arrivals) for which you want to calculate the probability. events is a number value. ÂÂ average: The average of the distribution. average is a number value representing the known average (arithmetic mean) rate at which events occur.
ÂÂ probability-values: The collection containing the probability values. probability- values is a collection containing number values. The sum of the probabilities must add up to 1. Any string values are ignored. ÂÂ lower: The lower limit or bound. lower is a number value. ÂÂ upper: An optional upper limit or bound. upper is a number value and must be greater than or equal to lower.
Related Topics For related functions and additional information, see: “BINOMDIST” on page 238 “CRITBINOM” on page 252 “NEGBINOMDIST” on page 275 “PERMUT” on page 281 “Listing of Statistical Functions” on page 225 “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 QUARTILE The QUARTILE function returns the value for the specified quartile of a given data collection.
Examples =QUARTILE({5, 6, 9, 3, 7, 11, 8, 2, 14}, 0) returns 2, the smallest value. =QUARTILE({5, 6, 9, 3, 7, 11, 8, 2, 14}, 1) returns 5, the 25th percentile or first quartile. =QUARTILE({5, 6, 9, 3, 7, 11, 8, 2, 14}, 2) returns 7, the 50th percentile or second quartile. =QUARTILE({5, 6, 9, 3, 7, 11, 8, 2, 14}, 3) returns 9, the 75th percentile or third quartile. =QUARTILE({5, 6, 9, 3, 7, 11, 8, 2, 14}, 0) returns 14, the largest value.
ÂÂ largest-is-high: An optional value specifying whether the smallest or the largest value in the collection is ranked 1. largest is low (0, FALSE, or omitted): Assign the largest value in the collection the rank 1. largest is high (1, or TRUE): Assign the smallest value in the collection the rank 1. Usage Notes ÂÂ Values included in the collection that are the same are ranked together, but impact the outcome.
SLOPE The SLOPE function returns the slope of the best-fit line for the collection using linear regression analysis. SLOPE(y-values, x-values) ÂÂ y-values: The collection containing the y (dependent) values. y-values is a collection that can contain number, date/time, or duration values. All values must be of the same type. ÂÂ x-values: The collection containing the x (independent) values. x-values is a collection that can contain number, date/time, or duration values.
“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 SMALL The SMALL function returns the nth-smallest value within a range. The smallest value is ranked number 1. SMALL(num-date-dur-set, ranking) ÂÂ num-date-dur-set: A collection of values. num-date-dur-set is a collection containing number, date, or duration values. All values must be of the same type.
“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 STANDARDIZE The STANDARDIZE function returns a normalized value from a distribution characterized by a given mean and standard deviation. STANDARDIZE(num, average, stdev) ÂÂ num: The number to be evaluated. num is a number value. ÂÂ average: The average of the distribution.
STDEV The STDEV function returns the standard deviation, a measure of dispersion, of a collection of values based on their sample (unbiased) variance. STDEV(num-date-dur, num-date-dur…) ÂÂ num-date-dur: A value. num-date-dur is a number value, a date/time value, or a duration value. ÂÂ num-date-dur…: One or more additional values (a minimum of two values are required). All num-date-dur values must be of the same type.
“STDEVPA” on page 294 “VAR” on page 298 “VARA” on page 300 “VARP” on page 302 “VARPA” on page 303 “Survey Results Example” on page 362 “Listing of Statistical Functions” on page 225 “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 STDEVA The STDEVA function returns the standard deviation, a measure of dispersion, of a collection of values that may include text and Boolean values,
Example Assume you have installed a temperature sensor in Cupertino, California. The sensor records each day’s high and low temperatures. In addition, you have kept a record of each day you turned on the air conditioner in your condo. The data from the first few days is shown in the following table and is used as a sample for the population of high and low temperatures (note that this is an example only; this would not be statistically valid). =STDEVA(B2:B13) returns 24.
“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26 “Pasting from Examples in Help” on page 41 STDEVP The STDEVP function returns the standard deviation, a measure of dispersion, of a collection of values based on their population (true) variance. STDEVP(num-date-dur, num-date-dur…) ÂÂ num-date-dur: A value. num-date-dur is a number value, a date/time value, or a duration value. ÂÂ num-date-dur…: Optionally include one or more additional values.
Related Topics For related functions and additional information, see: “STDEV” on page 290 “STDEVA” on page 291 “STDEVPA” on page 294 “VAR” on page 298 “VARA” on page 300 “VARP” on page 302 “VARPA” on page 303 “Survey Results Example” on page 362 “Listing of Statistical Functions” on page 225 “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 STDEVPA The STDEVPA function returns the
ÂÂ The standard deviation is the square root of the variance returned by the VARPA function. Example Assume you have installed a temperature sensor in Cupertino, California. The sensor records each day’s high and low temperatures. In addition, you have kept a record of each day you turned on the air conditioner in your condo. The sensor failed after the first few days so the following table is the population of high and low temperatures. =STDEVPA(B2:B13) returns 23.
“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26 “Pasting from Examples in Help” on page 41 TDIST The TDIST function returns the probability from the student’s t-distribution. TDIST(non-neg-x-value, degrees-freedom, tails) ÂÂ non-neg-x-value: The value at which you want to evaluate the function. non-neg-x- value is a number value that must be greater than or equal to 0. ÂÂ degrees-freedom: Degrees of freedom.
TINV The TINV function returns the t value (a function of the probability and degrees of freedom) from the student’s t-distribution. TINV(probability, degrees-freedom) ÂÂ probability: A probability associated with the distribution. probability is a number value and must be greater than 0 and less than 1. ÂÂ degrees-freedom: Degrees of freedom. degrees-freedom is a number value and must be greater than or equal to 1. Example =TINV(0.88, 2) returns 0.170940864689457.
paired (1): Perform a paired test. two-sample equal (2): Perform a two-sample equal variance (homoscedastic) test. two-sample unequal (3): Perform a two-sample unequal variance (heteroscedastic) test. Examples =TTEST({57, 75, 66, 98, 92, 80}, {87, 65, 45, 95, 88, 79}, 1, 1) returns 0.418946725989974, for the one-tailed, paired test. =TTEST({57, 75, 66, 98, 92, 80}, {87, 65, 45, 95, 88, 79}, 2, 1) returns 0.837893451979947 for the two-tailed, paired test.
Usage Notes ÂÂ The VAR function finds the sample (unbiased) variance by dividing the sum of the squares of the deviations of the data points by one less than the number of values. ÂÂ It is appropriate to use VAR when the specified values represent only a sample of a larger population. If the values you are analyzing represent the entire collection or population, use the VARP function. ÂÂ If you want to include text or Boolean values in the computation, use the VARA function.
“Listing of Statistical Functions” on page 225 “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 VARA The VARA function returns the sample (unbiased) variance, a measure of dispersion, of a collection of values, including text and Boolean values. VARA(value, value…) ÂÂ value: A value. value can contain any value type. ÂÂ value…: Optionally include one or more additional values.
Example Assume you have installed a temperature sensor in Cupertino, California. The sensor records each day’s high and low temperatures. In addition, you have kept a record of each day you turned on the air conditioner in your condo. The data from the first few days is shown in the following table and is used as a sample for the population of high and low temperatures (note that this is an example only; this would not be statistically valid). =VARA(B2:B13) returns 616.
VARP The VARP function returns the population (true) variance, a measure of dispersion, of a collection of values. VARP(num-date, num-date…) ÂÂ num-date: A value. num-date is a number value or a date/time value. ÂÂ num-date…: Optionally include one or more additional values. If more than one num-date value is specified, all must be of the same type.
“STDEVP” on page 293 “STDEVPA” on page 294 “VAR” on page 298 “VARA” on page 300 “VARPA” on page 303 “Survey Results Example” on page 362 “Listing of Statistical Functions” on page 225 “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 VARPA The VARPA function returns the sample (unbiased) variance, a measure of dispersion, of a collection of values, including text and Boolean values
Example Assume you have installed a temperature sensor in Cupertino, California. The sensor records each day’s high and low temperatures. In addition, you have kept a record of each day you turned on the air conditioner in your condo. The sensor failed after the first few days so the following table is the population of high and low temperatures. =VARPA(B2:B13) returns 565.0208, the dispersion as measured by VARPA, of the sample of daily high temperatures.
ZTEST The ZTEST function returns the one-tailed probability value of the Z-test. ZTEST(num-date-dur-set, num-date-dur, stdev) ÂÂ num-date-dur-set: A collection of values. num-date-dur-set is a collection containing number, date, or duration values. All values must be of the same type. ÂÂ num-date-dur: A value. num-date-dur is a number value, a date/time value, or a duration value.num-date-dur is the value to test. ÂÂ stdev: An optional value for the standard deviation of the population.
11 Text Functions The text functions help you work with strings of characters. Listing of Text Functions iWork provides these text functions for use with tables. 306 Function Description “CHAR” (page 308) The CHAR function returns the character that corresponds to a decimal Unicode character code. “CLEAN” (page 308) The CLEAN function removes most common nonprinting characters (Unicode character codes 0–31) from text.
Function Description “LEN” (page 315) The LEN function returns the number of characters in a string. “LOWER” (page 316) The LOWER function returns a string that is entirely lowercase, regardless of the case of the characters in the specified string. “MID” (page 316) The MID function returns a string consisting of the given number of characters from a string starting at the specified position.
CHAR The CHAR function returns the character that corresponds to a decimal Unicode character code. CHAR(code-number) ÂÂ code-number: A number for which you want to return the corresponding Unicode character. code-number is a number value and must be greater than or equal to 32, less than or equal to 65,535, and not equal to 127. If there is a decimal portion, it is ignored. Note that character 32 is the space character. Usage Notes ÂÂ Not all Unicode numbers are associated with a printable character.
Usage Notes ÂÂ This can be helpful if text you paste from another application contains unwanted question marks, spaces, boxes, or other unexpected characters. ÂÂ There are some less common nonprinting characters that are not removed by CLEAN (character codes 127, 129, 141, 143, 144, and 157). To remove these, you can use the SUBSTITUTE function to replace them with a code in the range 0–31 before you use the CLEAN function. ÂÂ You can use the TRIM function to remove extra spaces in text.
ÂÂ You can use the CHAR function to do the opposite of the CODE function: convert a numeric code into a text character. Examples =CODE(”A”) returns 65, the character code for uppercase “A”. =CODE(”abc”) returns 97 for lowercase “a”. =CHAR(97) returns “a”. =CODE(A3) returns 102 for lowercase “f”. =CODE(”三二一”) returns 19,977, the decimal Unicode value of the first character.
“Listing of Text Functions” on page 306 “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 DOLLAR The DOLLAR function returns a string formatted as a dollar amount from a given number. DOLLAR(num, places) ÂÂ num: The number to be used. num is a number value.
EXACT The EXACT function returns TRUE if the argument strings are identical in case and content. EXACT(string-1, string-2) ÂÂ string-1: The first string. string-1 is a string value. ÂÂ string-2: The second string. string-2 is a string value. Examples =EXACT(”toledo”, “toledo”) returns TRUE, since all the characters and their cases are identical. =EXACT(”Toledo”, “toledo”) returns FALSE, since the case of the two strings is not identical.
ÂÂ Specifying start-pos permits you to begin the search for search-string within, rather than at the beginning of, source-string. This is particularly useful if source-string may contain multiple instances of search-string and you wish to determine the starting position of other than the first instance. If start-pos is omitted, it is assumed to be 1. Examples =FIND(”e”, “where on earth”) returns 3 (”e” is the third character in the string “where on earth”).
Examples =FIXED(6789.123, 2) returns “6,789.12.” =FIXED(6789.123, 1, 1) returns “6789.1.” =FIXED(6789.123, -2) returns “6,800.” =FIXED(12.4, 0) returns “12.” =FIXED(12.5, 0) returns “13.” =FIXED(4, -1) returns “0.” =FIXED(5, -1) returns “10.
Related Topics For related functions and additional information, see: “MID” on page 316 “RIGHT” on page 319 “Listing of Text Functions” on page 306 “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 LEN The LEN function returns the number of characters in a string. LEN(source-string) ÂÂ source-string: A string. source-string is a string value.
LOWER The LOWER function returns a string that is entirely lowercase, regardless of the case of the characters in the specified string. LOWER(source-string) ÂÂ source-string: A string. source-string is a string value. Examples =LOWER(”UPPER”) returns “upper”. =LOWER(”Lower”) returns “lower”. =LOWER(”MiXeD”) returns “mixed”.
Examples =MID(”lorem ipsum dolor sit amet”, 7, 5) returns “ipsum”. =MID(”1234567890”, 4, 3) returns “456”. =MID(”shorten”, 5, 20) returns “ten”.
“UPPER” on page 324 “Listing of Text Functions” on page 306 “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 REPLACE The REPLACE function returns a string where a specified number of characters of a given string have been replaced with a new string. REPLACE(source-string, start-pos, replace-length, new-string) ÂÂ source-string: A string. source-string is a string value.
REPT The REPT function returns a string that contains a given string repeated a specified number of times. REPT(source-string, repeat-number) ÂÂ source-string: A string. source-string is a string value. ÂÂ repeat-number: The number of times the given string should be repeated. repeat- number is a number value that must be greater than or equal to 0. Examples =REPT(”*”, 5) returns “*****”. =REPT(”ha”, 3) returns “hahaha”.
Related Topics For related functions and additional information, see: “LEFT” on page 314 “MID” on page 316 “Listing of Text Functions” on page 306 “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 SEARCH The SEARCH function returns the starting position of one string within another, ignoring case and allowing wildcards.
Examples =SEARCH(”ra”, “abracadabra”) returns 3; the first occurrence of the string “ra” starts at the third character in “abracadabra”. =SEARCH(”ra”, “abracadabra”, 5) returns 10, the position of the first occurrence of string “ra” when you start looking at position 5. =SEARCH(“*card”, “Wildcard”) returns 1, since the asterisk at the beginning of the search string matches all the characters before “card”. =SEARCH(“*cad”, “Wildcard”) returns an error, since the string “cad” does not exist.
SUBSTITUTE The SUBSTITUTE function returns a string where the specified characters of a given string have been replaced with a new string. SUBSTITUTE(source-string, existing-string, new-string, occurrence) ÂÂ source-string: A string. source-string is a string value. ÂÂ existing-string: The string within the given string that is to be replaced. existing- string is a string value. ÂÂ new-string: The text used as a replacement for the section of the given string that is replaced.
T The T function returns the text contained in a cell. This function is included for compatibility with tables imported from other spreadsheet applications. T(cell) ÂÂ cell: A reference to a single table cell. cell is a reference value to a single cell that can contain any value, or be empty. Usage Notes ÂÂ If the cell doesn’t contain a string, T returns an empty string. Examples If cell A1 contains “text” and cell B1 is empty: =T(A1) returns “text” =T(B1) returns nothing.
Related Topics For related functions and additional information, see: “Listing of Text Functions” on page 306 “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 UPPER The UPPER function returns a string that is entirely uppercase, regardless of the case of the characters in the specified string. UPPER(source-string) ÂÂ source-string: A string. source-string is a string value.
VALUE The VALUE function returns a number value even if the argument is formatted as text. This function is included for compatibility with tables imported from other spreadsheet applications. VALUE(source-string) ÂÂ source-string: A string. source-string is a string value. Usage Notes ÂÂ You’ll never need to use the VALUE function in a new table, as numbers in text are automatically converted for you. ÂÂ Only the formatted text is converted. For example, if the string $100.
Trigonometric Functions 12 The trigonometric functions help you work with angles and their components. Listing of Trigonometric Functions iWork provides these trigonometric functions for use with tables. 326 Function Description “ACOS” (page 327) The ACOS function returns the inverse cosine (arccosine) of a number. “ACOSH” (page 328) The ACOSH function returns the inverse hyperbolic cosine (hyperbolic arccosine) of a number.
Function Description “DEGREES” (page 334) The DEGREES function returns the number of degrees in an angle expressed in radians. “RADIANS” (page 335) The RADIANS function returns the number of radians in an angle expressed in degrees. “SIN” (page 336) The SIN function returns the sine of an angle that is expressed in radians. “SINH” (page 337) The SINH function returns the hyperbolic sine of the specified number.
“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 ACOSH The ACOSH function returns the inverse hyperbolic cosine (hyperbolic arccosine) of a number. ACOSH(num) ÂÂ num: A number. num is a number value that must be greater than or equal to 1. Examples =ACOSH(10.0676619957778) returns 3. =ACOSH(COSH(5)) returns 5.
ASIN The ASIN function returns the arcsine (the inverse sine) of a number. ASIN(num) ÂÂ num: A number. num is a number value that must be greater than or equal to 1. Usage Notes ÂÂ The ASIN function takes a sine and returns the corresponding angle. The result is expressed in radians, in the range –pi/2 to +pi/2. To see the resulting angle in degrees instead of radians, wrap this function in the DEGREES function; that is, =DEGREES(ASIN(num)). Examples =ASIN(0.
Examples =ASINH(27.2899171971277) returns 4. =ASINH(SINH(1)) returns 1. Related Topics For related functions and additional information, see: “ASIN” on page 329 “SIN” on page 336 “SINH” on page 337 “Listing of Trigonometric Functions” on page 326 “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 ATAN The ATAN function returns the inverse tangent (arctangent) of a number.
“DEGREES” on page 334 “TAN” on page 338 “TANH” on page 339 “Listing of Trigonometric Functions” on page 326 “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 ATAN2 The ATAN2 function returns the angle, relative to the positive x-axis, of the line passing through the origin and the specified point.
“TANH” on page 339 “Listing of Trigonometric Functions” on page 326 “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 ATANH The ATANH function returns the inverse hyperbolic tangent of a number. ATANH(num) ÂÂ num: A number. num is a number value that must be greater than –1 and less than 1. Examples =ATANH(0.995054753686731) returns 3. =ATANH(TANH(2)) returns 2.
COS The COS function returns the cosine of an angle that is expressed in radians. COS(radian-angle) ÂÂ radian-angle: An angle, expressed in radians. radian-angle is a number value. Although it can be any value, it would normally be in the range –π to +π (–pi to +pi). Usage Notes ÂÂ To return an angle in degrees, use the DEGREES function (to convert radians to degrees) with this function; that is, =DEGREES(COS(radian-angle)). Examples =COS(1) returns 0.540302306, the cosine of 1 radian (approximately 57.
COSH The COSH function returns the hyperbolic cosine of a number. COSH(num) ÂÂ num: A number. num is a number value. Examples =COSH(0) returns 1. =COSH(1) returns 1.543. =COSH(5) returns 74.21. =COSH(10) returns 11,013.233.
Related Topics For related functions and additional information, see: “ACOS” on page 327 “ASIN” on page 329 “ATAN” on page 330 “ATAN2” on page 331 “COS” on page 333 “SIN” on page 336 “TAN” on page 338 “Listing of Trigonometric Functions” on page 326 “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 RADIANS The RADIANS function returns the number of radians in an angle expressed in
“ACOS” on page 327 “ASIN” on page 329 “ATAN” on page 330 “ATAN2” on page 331 “COS” on page 333 “SIN” on page 336 “TAN” on page 338 “Listing of Trigonometric Functions” on page 326 “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 SIN The SIN function returns the sine of an angle that is expressed in radians. SIN(radian-angle) ÂÂ radian-angle: An angle, expressed in radians.
“COS” on page 333 “DEGREES” on page 334 “SINH” on page 337 “TAN” on page 338 “Listing of Trigonometric Functions” on page 326 “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 SINH The SINH function returns the hyperbolic sine of the specified number. SINH(num) ÂÂ num: A number. num is a number value. Examples =SINH(0) returns 0. =SINH(1) returns 1.175. =SINH(5) returns 74.203.
TAN The TAN function returns the tangent of an angle that is expressed in radians. TAN(radian-angle) ÂÂ radian-angle: An angle, expressed in radians. radian-angle is a number value. Although it can be any value, it would normally be in the range –pi to +pi. Usage Notes ÂÂ The tangent is the ratio of the sine to the cosine. ÂÂ To return an angle in degrees, use the DEGREES function (to convert radians to degrees) with this function; that is, =DEGREES(TAN(radian-angle)). Examples =TAN(1) returns 1.
TANH The TANH function returns the hyperbolic tangent of the specified number. TANH(num) ÂÂ num: A number. num is a number value. Examples =TANH(0) returns 0. =TANH(1) returns 0.762. =TANH(5) returns 0.999909. =TANH(10) returns 0.999999996.
Additional Examples and Topics 13 The in-depth examples and additional topics in this chapter illustrate working with some of the more complex functions. Additional Examples and Topics Included The following table tells you where to find in-depth examples and additional topics that illustrate working with some of the more complex functions with real-world examples.
Common Arguments Used in Financial Functions Many arguments are common among related financial functions. This section provides information regarding these arguments. Date arguments (issue, maturity, and settle) are not included. Arguments that are used by only a single financial function are also not included. annual-rate Bonds and other fixed-rate, interest-bearing debt securities have a stated coupon or annual interest rate used to determine periodic interest payments.
cum-when-due See discussion at when-due. The only difference is that functions that use cum-when-due require the argument to be specified and do not assume a value if it is omitted. days-basis There are several different conventions used when counting the number of days in a month and number of days in a year to determine interest on a loan or investment. days-basis is used to indicate how days are counted for a specific investment or loan.
depr-period Certain functions return the amount of depreciation for a specified period. depr-period is used to specify the period. depr-period is specified as a number representing the desired depreciation period using the same time frame (for example, monthly, quarterly, or annually) as life. Assume that you purchase a new digital photocopy machine for your office. The purchase price of the photocopy machine was $2,625 with tax. The vendor charged $100 to deliver and set it up.
future-value A future value is a cash flow received or paid at the end of the investment or loan period or the cash value remaining after the final payment. future-value is specified as a number, usually formatted as currency. Since future-value is a cash flow, amounts received are specified as positive numbers and amounts paid are specified as negative numbers. Assume that there is a townhouse that you plan to purchase, rent out for a period of time, and then resell.
num-periods-year The calculation of the effective and nominal interest rates is based on the number of interest compounding periods per year. num-periods-year is used to specify the number of periods. num-periods-year is specified as a number and must be greater than 0. Assume that you have purchased a certificate of deposit that pays interest annually, compounded quarterly. If you wanted to determine the effective interest rate, num-periods-year would be 4.
periodic-rate In some cases, when working with a series of cash flows, or an investment, or a loan, it may be necessary to know the interest rate each period. This is the periodic-rate. periodic-rate is specified as a decimal number using the same time frame (for example, monthly, quarterly, or annually) as other arguments (num-periods or payment). Assume that you are purchasing a home.
salvage Assets often have value remaining at the end of the depreciable life or the expected useful life. This is the salvage value. salvage is specified as a number, usually formatted as currency. salvage can be 0, but cannot be negative. Assume that you purchase a new digital photocopy machine for your office. The purchase price of the photocopy machine was $2,625 with tax. The vendor charged $100 to deliver and set it up.
Choosing Which Time Value of Money Function to Use This section provides additional information regarding the functions used to solve time value of money problems. Time value of money, or TVM, problems involve cash flows over time and interest rates. This section contains several parts. “Regular Cash Flows and Time Intervals” on page 348 discusses the TVM functions used with regular cash flows, regular time intervals, and fixed interest rates.
Function and its purpose Arguments used by the function “PV” (page 141) is the function to use if you wish periodic-rate, num-periods, payment, future-value, to determine the present value (what it is worth when-due today) of a series of cash flows, considering the other factors such as the interest rate. It solves for the argument present-value.
Irregular Cash Flows and Time Intervals Some TVM problems involve irregular fixed periodic cash flows where cash flows occur at regular time intervals but the amounts vary. Still other problems have cash flows that have irregular time intervals where cash flows do not necessarily occur at regular time intervals.
Which Function Should You Use to Solve Common Financial Questions? This section describes some common questions you might want to address and lists the financial function that might be helpful. The questions help with everyday financial questions. More complex uses of the financial functions are described in “Regular Cash Flows and Time Intervals” on page 348, “Irregular Cash Flows and Time Intervals” on page 350, and “Example of a Loan Amortization Table” on page 353.
If you would like to know This function may be helpful The effective annual interest rate for a bond that pays interest only at its maturity (no periodic payments, but the bond does have a coupon rate) “INTRATE” (page 122) The expected purchase price of a bond that pays “PRICE” (page 137), “PRICEDISC” (page 138), and “PRICEMAT” (page 140) periodic interest, a bond sold at a discount that does not pay interest, or a bond that pays interest only at maturity 352 The amount received on a bond that pays
Example of a Loan Amortization Table This example uses IPMT, PPMT, and PMT to construct a loan amortization table. The information returned by IPMT, PPMT, and PMT is related. This is illustrated in the example. Constructing the Amortization Table Assume you wish to construct an amortization table for all periods of a loan with an initial principal amount of $50,000, a term of 2 years, an annual interest rate of 7%, and a balance due at the end of the term of $30,000.
The Completed Amortization Table To complete the table, it would be necessary to select cells A10:A11 and extend the selection down to A32 to include all 24 periods in the hypothetical loan. Then C9:F9 would be selected and extended to C32:F32 to complete the formulas. Here is the complete table, showing the entire amortization using the formulas shown in the previous table.
More on Rounding iWork supports many different functions that round numbers. This section compares these functions. To Use this function “CEILING” (page 170) Round a number away from zero to the nearest multiple of a given number Round a number away from zero to the nearest even number “EVEN” (page 173) Comments Rounding occurs in steps; for example, the closest multiple of 10. Rounding is away from zero, so =CEILING(0.4, 1) results in 1 and =CEILING (-0.4, -1) results in -1.
To Use this function Comments Round a number to the specified number of places “ROUND” (page 191) A positive number indicates the number of digits (decimal places) to the right of the decimal separator to include in the rounded number. A negative number indicates the number of digits to the left of the decimal separator to replace with zeros (the number of zeros at the end of the number). The number is rounded based on this. So =ROUND(1125, -2) returns 1,100 and =ROUND(1155, -2) returns 1,200.
To Use this function Comments Round a number up (away from “ROUNDUP” (page 193) zero) to the specified number of places A positive number indicates the number of digits (decimal places) to the right of the decimal separator to include in the rounded number. A negative number indicates the number of digits to the left of the decimal separator to replace with zeros (the number of zeros at the end of the number). The number is rounded based on this.
Using Logical and Information Functions Together Logical and information functions are often used together in a formula. Although logical functions are used independently, it is rare for an information function to be used on its own. This section includes more complex examples to illustrate how the use of several logical and information functions in a single formula can be very powerful.
Expression 1 =AND(B2>60, B2<=75) tests for a low score. If the test score is in the range 61 to 75, AND will return TRUE meaning the student should come in for a special study session. Otherwise it will return FALSE. Expression 2 =OR(ISBLANK(B2), B2<0, B2>100) tests for invalid data. The first OR expression “ISBLANK(B2)” will be TRUE if there is no test score. The second expression will return TRUE if the test score is negative and the third expression will return TRUE if the test score is over 100.
Trapping Division by Zero Sometimes it is not possible to construct a table in a manner that can avoid division by zero. However, if division by zero occurs, the result is an error value in the cell, which is usually not the desired result. This example shows three methods of preventing this error. Examples Assume that cell D2 and E2 each contain a number. It is possible that E2 contains 0. You wish to divide D2 by E2, but avoid a division by zero error.
Function Allows numeric comparisons Accepts wildcards HLOOKUP no if exact match specified MATCH no if exact match specified VLOOKUP no if exact match specified Examples of conditions, both with and without wildcards, are illustrated in this section. Expression Example “>4” means match any number greater than 4. =COUNTIF(B2:E7, “>4”) returns a count of the number of cells in the range B2:E7 that contain a value greater than 4. “>=7” means match any number greater than or equal to 7.
Survey Results Example This example brings together the illustrations used throughout the statistical functions. It is based on a hypothetical survey. The survey was short (only five questions) and had a very limited number of respondents (10). Each question could be answered on a scale of 1 to 5 (perhaps the range from “never” to “always”), or not answered. Each survey was assigned a number before mailing. The following table shows the results.
Function and arguments Description of result =CORREL(B2:B11, C2:C11) Determines the correlation of question 1 and question 2 using linear regression analysis. Correlation is a measure of how much two variables (in this case, answers to survey questions) change together.
Function and arguments Description of result =COVAR(B2:B11, C2:C11) Determines the covariance of question 1 and question 2. Covariance is a measure of how much two variables (in this case, answers to survey questions) change together.
Symbols ? wildcard 30, 361 * wildcard 30, 361 & string operator 30, 310 ~ wildcard escape character 30, 361 A ABS numeric function 170 absolute cell references 27 ACCRINT financial function 99, 351 ACCRINTM financial function 101 ACOS trigonometric function 327 ACOSH trigonometric function 328 ADDRESS reference function 207 ampersand string operator 30, 310 AND logical and information function 156, 358 any value type 36 AREAS reference function 209 arithmetic operators 28 array constant
constant defined 35 conversion units distance 80 duration 80 energy 81 force 81 liquid 82 magnetism 82 metric prefixes 83 power 82 pressure 81 speed 81 temperature 82 weight and mass 80 CONVERT engineering function 79 copying help examples into a table 41 CORREL statistical function 242, 363 COS trigonometric function 333 COSH trigonometric function 334 COUNT statistical function 244, 363 COUNTA statistical function 245, 363 COUNTBLANK statistical function 246, 363 COUNTIF statistical f
DEC2OCT 85 DELTA 86 ERF 87 ERFC 87 GESTEP 88 HEX2BIN 89 HEX2DEC 90 HEX2OCT 91 NUMTOBASE 92 OCT2BIN 93 OCT2DEC 94 OCT2HEX 95 EOMONTH date and time function 51 ERF engineering function 87 ERFC engineering function 87 European days-basis 342 EVEN numeric function 173, 355 EXACT text function 312 EXP numeric function 174 F FACT numeric function 174 FACTDOUBLE numeric function 175 FALSE logical and information function 157 FDIST statistical function 254, 255, 261 financial function ACCRI
handling errors and warnings 23 inserting cell references 26 operators 28 performing instant calculations 17 using arithmetic operators 28 using cell references 24 using comparison operators 29 using the formula bar 20 using the Formula Editor 19 using the Function Browser 21 viewing all in a spreadsheet 31 formulas that reference the same cell in multiple tables 39 FREQUENCY statistical function 257 Function Browser 21. See also functions functions.
IF 158, 358 IFERROR 159 ISBLANK 160, 358 iserror 161 ISEVEN 162 ISODD 163 NOT 164 OR 165, 358 TRUE 166 LOGINV statistical function 268 LOGNORMDIST statistical function 269 LOOKUP reference function 217 LOWER text function 316 M magnetism conversion units 82 MATCH reference function 218, 361 MAX statistical function 270 MAXA statistical function 270 MEDIAN statistical function 271 metric prefixes for conversion units 83 MID text function 316 MIN statistical function 272 MINA statistica
comparison 29 string 30, 310 OR logical and information function 165, 358 P parentheses syntax elements 34 pasting help examples into a table 41 PERCENTILE statistical function 279 PERCENTRANK statistical function 280 PERMUT statistical function 281 PI numeric function 186 PMT financial function 134, 348, 351, 353 POISSON statistical function 282 power conversion units 82 POWER numeric function 186 PPMT financial function 135, 349, 353 pressure conversion units 81 PRICE financial function
COVAR 250, 364 CRITBINOM 252 DEVSQ 253 EXPONDIST 253 FDIST 254 FINV 255 FORECAST 256 FREQUENCY 257 GAMMADIST 259 GAMMAINV 260 GAMMALN 260 GEOMEAN 261 HARMEAN 262 INTERCEPT 262 LARGE 264 LINEST 265 LOGINV 268 LOGNORMDIST 269 MAX 270 MAXA 270 MEDIAN 271 MIN 272 MINA 273 MODE 274 NEGBINOMDIST 275 NORMDIST 276 NORMINV 277 NORMSDIST 277 NORMSINV 278 PERCENTILE 279 PERCENTRANK 280 PERMUT 281 POISSON 282 PROB 282 QUARTILE 284 RANK 285 SLOPE 287 SMALL 288 STANDARDIZE 289 STDEV
ASIN 329 ASINH 329 ATAN 330 ATAN2 331 ATANH 332 COS 333 COSH 334 DEGREES 334 RADIANS 335 SIN 336 SINH 337 TAN 338 TANH 339 TRIM text function 323 TRUE logical and information function 166 TRUNC numeric function 204, 357 TTEST statistical function 297 U UPPER text function 324 uppercase text syntax elements 34 using a formula to reference the same cell in multiple tables 39 using help examples in a table 41 using logical and information functions together 358 V VALUE text function
