Apple iWork '09 Bedienungsanleitung

iWorkFormulas and Functions User Guide

10 Contents305 ZTEST306 Chapter 11: Text Functions306 Listing of Text Functions308 CHAR308 CLEAN309 CODE310 CONCATENATE311 DOLLAR312 EXAC

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

Chapter 6 Financial Functions 101Example 2Assume you are considering the purchase of the hypothetical security described by the values listed. T

 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-

Chapter 6 Financial Functions 103BONDDURATIONThe BONDDURATION function returns the weighted average of the present value of the cash ows for an

Related TopicsFor related functions and additional information, see:“BONDMDURATION” on page 104“Common Arguments Used in Financial Functions” on page

Chapter 6 Financial Functions 105actual/365 (3): Actual days in each month, 365 days in a year.30E/360 (4): 30 days in a month, 360 days in a

semiannual (2): Two payments per year.quarterly (4): Four payments per year. Â days-basis: An optional argument specifying the number of days per m

Chapter 6 Financial Functions 107COUPDAYSThe COUPDAYS function returns the number of days in the coupon period in which settlement occurs.COUPDA

Related TopicsFor related functions and additional information, see:“COUPDAYBS” on page 105“COUPDAYSNC” on page 108“Common Arguments Used in Financial

Chapter 6 Financial Functions 109ExampleAssume you are considering the purchase of the hypothetical security described by the values listed.You

Contents 11339 TANH340 Chapter 13: Additional Examples and Topics340 Additional Examples and Topics Included341 Common Arguments Used in Finan

 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 day

Chapter 6 Financial Functions 111 Â num-periods: The number of periods. num-periods is a number value and must be greater than or equal to 0. Â

Related TopicsFor related functions and additional information, see:“CUMPRINC” on page 11 2“IPMT” on page 12 3“PMT” on page 134“PPMT” on page 13 5“Exa

Chapter 6 Financial Functions 11 3end (0): Payments are due at the end of each period.beginning (1): Payments are due at the beginning of each

DBThe DB function returns the amount of depreciation of an asset for a specied period using the xed-declining balance method.DB(cost, salvage, life,

Chapter 6 Financial Functions 11 5Example 2Depreciation for Partial First YearAssume the same facts as Example 1, except that the asset will be

DDBThe DDB function returns the amount of depreciation of an asset based on a specied depreciation rate.DDB(cost, salvage, life, depr-period, depr-fa

Chapter 6 Financial Functions 11 7 cost salvage life depr-period depr-factorFirst year, straight-line (returns $250)=DDB(B2, C2, D2, E7, F7)

 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 day

Chapter 6 Financial Functions 11 9EFFECTThe EFFECT function returns the eective annual interest rate from the nominal annual interest rate base

FVThe FV function returns the future value of an investment based on a series of regular periodic cash ows (payments of a constant amount and all cas

Chapter 6 Financial Functions 121Example 2Assume you are presented with an investment opportunity. The opportunity requires that you invest $50,

INTRATEThe INTRATE function returns the eective annual interest rate for a security that pays interest only at maturity.INTRATE(settle, maturity, inv

Chapter 6 Financial Functions 12 3Related TopicsFor related functions and additional information, see:“RECEIVED” on page 14 6“Common Arguments U

 when-due: An optional argument that species whether payments are due at the beginning or end of each period. Most mortgage and other loans requir

Chapter 6 Financial Functions 12 5IRRThe IRR function returns the internal rate of return for an investment that is based on a series of potenti

Example 2Assume you are presented with the opportunity to invest in a partnership. The initial investment required is $50,000. Because the partnership

Chapter 6 Financial Functions 12 7ISPMT(annual-rate, period, num-periods, present-value) Â annual-rate: The annual coupon rate or stated annual

MIRRThe MIRR function returns the modied internal rate of return for an investment that is based on a series of potentially irregular cash ows (paym

Chapter 6 Financial Functions 12 9 Initial DepositYear 1 Year 2 Year 3 Year 4 Year 5 Sales proceeds=MIRR (B2:H2, 0.09, 0.0425)-50000 -25000 -1

13iWork comes with more than 250 functions you can use to simplify statistical, nancial, engineering, and other computations. The built-in Functi

Examples=NOMINAL(0.0513, 365) returns approximately 5.00%, the nominal annual interest rate if the eective rate of 5.13% was based on daily compoundi

Chapter 6 Financial Functions 131 Â present value: The value of the initial investment, or the amount of the loan or annuity, specied as a neg

periodic-rate payment present-value future-value when-due=NPER(B2, C2, D2, E2, F2)=0.07/12 -1500 170000 0 1Related TopicsFor related functions and a

Chapter 6 Financial Functions 13 3Usage Notes  periodic-discount-rate is specied using the same time frame as the time frame used for the cash

PMTThe PMT function returns the xed periodic payment for a loan or annuity based on a series of regular periodic cash ows (payments of a constant am

Chapter 6 Financial Functions 13 5Related TopicsFor related functions and additional information, see:“FV” on page 12 0“IPMT” on page 12 3“NPER”

 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 re

Chapter 6 Financial Functions 13 7“Listing of Financial Functions” on page 96“Value Types” on page 36“The Elements of Formulas” on page 15“Using

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).ExampleIn t

Chapter 6 Financial Functions 13 9 Â redemption: The redemption value per $100 of par value. redemption is a number value that must be greater

14 Preface Welcome to iWork Formulas & FunctionsiWork websiteRead the latest news and information about iWork at www.apple.com/iwork.Suppo

PRICEMATThe PRICEMAT function returns the price of a security that pays interest only at maturity per $100 of redemption (par) value.PRICEMAT(settle,

Chapter 6 Financial Functions 141Related TopicsFor related functions and additional information, see:“PRICE” on page 137“PRICEDISC” on page 13 8

 when-due: An optional argument that species whether payments are due at the beginning or end of each period. Most mortgage and other loans requir

Chapter 6 Financial Functions 143Example 2In this example, you are presented with an investment opportunity. The opportunity is to invest in a d

RATEThe RATE function returns the interest rate of an investment, loan, or annuity based on a series of regular periodic cash ows (payments of a cons

Chapter 6 Financial Functions 145ExampleAssume you are planning for your daughter’s college education. She has just turned 3 and you expect she

RECEIVEDThe RECEIVED function returns the maturity value for a security that pays interest only at maturity.RECEIVED(settle, maturity, invest-amount,

Chapter 6 Financial Functions 147“Value Types” on page 36“The Elements of Formulas” on page 15“Using the Keyboard and Mouse to Create and Edit F

SYDThe SYD function returns the amount of depreciation of an asset for a specied period using the sum-of-the-years-digits method.SYD(cost, salvage, l

Chapter 6 Financial Functions 149VDBThe VDB (variable declining balance) function returns the amount of depreciation of an asset over a chosen t

15This chapter explains how to perform calculations in table cells by using formulas. The Elements of FormulasA formula performs a calculation and

Related TopicsFor related functions and additional information, see:“DB” on page 114“DDB” on page 116“SLN” on page 147“SYD” on page 148“Common Argumen

Chapter 6 Financial Functions 151 Â days-basis: An optional argument specifying the number of days per month and days per year used in the calc

YIELDDISCThe YIELDDISC function returns the eective annual interest rate for a security that is sold at a discount to redemption value and pays no in

Chapter 6 Financial Functions 153Related TopicsFor related functions and additional information, see:“PRICEDISC” on page 13 8“YIELD” on page 150

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 dat

155The logical and information functions help you to evaluate the contents of cells and help in determining how to evaluate or otherwise work with

Function Description“ISEVEN” (page 162) The ISEVEN function returns TRUE if the value is even (leaves no remainder when divided by 2); otherwise it re

Chapter 7 Logical and Information Functions 157Related TopicsFor related functions and additional information, see:“IF” on page 15 8“NOT” on pag

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

Chapter 7 Logical and Information Functions 159Related TopicsFor related functions and additional information, see:“AND” on page 156“NOT” on pag

=SUM(A2:A10): A formula that uses the function SUM to add the values in a range of cells (nine cells in the rst column).A2:A10: A cell reference th

ExamplesIf 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),

Chapter 7 Logical and Information Functions 161ExamplesIf the table cell A1 is empty and cell B2 is equal to 100:=ISBLANK(A1) returns TRUE.=ISBL

Related TopicsFor related functions and additional information, see:“IFERROR” on page 15 9“ISBLANK” on page 160“Listing of Logical and Information Fun

Chapter 7 Logical and Information Functions 163ISODDThe ISODD function returns TRUE if the given number is odd (leaves a remainder when divided

NOTThe NOT function returns the opposite of the Boolean value of a specied expression.NOT(any-expression) Â any-expression: An expression to be test

Chapter 7 Logical and Information Functions 165ORThe OR function returns TRUE if any argument is true; otherwise it returns FALSE.OR(any-express

TRUEThe TRUE function returns the Boolean value TRUE. This function is included for compatibility with tables imported from other spreadsheet applicat

167The numeric functions help you to calculate commonly used mathematical values.Listing of Numeric FunctionsiWork provides these numeric function

Function Description“INT” (page 178 ) The INT function returns the nearest integer that is less than or equal to the number.“LCM” (page 17 9) The LCM

Chapter 8 Numeric Functions 169Function Description“ROUND” (page 191 ) The ROUND function returns a number rounded to the specied number of pla

Chapter 1 Using Formulas in Tables 17Performing Instant Calculations in NumbersIn the lower left of the Numbers window, you can view the results

ABSThe 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 dur

Chapter 8 Numeric Functions 171Examples=CEILING(0.25, 1) returns 1.=CEILING(1.25, 1) returns 2.=CEILING(-1.25, -1) returns -2.=CEILING(5, 2) ret

COMBINThe COMBIN function returns the number of dierent ways you can combine a number of items into groups of a specic size, ignoring the order with

Chapter 8 Numeric Functions 173EVENThe EVEN function rounds a number away from zero to the next even number.EVEN(num-to-round) Â num-to-round:

EXPThe EXP function returns e (the base of natural logarithms) raised to the specied power.EXP(exponent) Â exponent: The power to which you want to

Chapter 8 Numeric Functions 175Related TopicsFor related functions and additional information, see:“FACTDOUBLE” on page 175“MULTINOMIAL” on page

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

Chapter 8 Numeric Functions 177“TRUNC” on page 204“More on Rounding” on page 355“Listing of Numeric Functions” on page 167“Value Types” on page

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

Chapter 8 Numeric Functions 179LCMThe LCM function returns the least common multiple of the specied numbers.LCM(num-value, num-value…) Â num-va

Using Predened Quick FormulasAn easy way to perform a basic calculation using values in a range of adjacent table cells is to select the cells and th

Example=LN(2.71828) returns approximately 1, the power to which e must be raised to produce 2.71828.Related TopicsFor related functions and additional

Chapter 8 Numeric Functions 181“Value Types” on page 36“The Elements of Formulas” on page 15“Using the Keyboard and Mouse to Create and Edit For

MODThe MOD function returns the remainder from a division.MOD(dividend, divisor) Â dividend: A number to be divided by another number. dividend is a

Chapter 8 Numeric Functions 183MROUNDThe MROUND function rounds a number to the nearest multiple of a specied factor.MROUND(num-to-round, facto

MULTINOMIALThe MULTINOMIAL function returns the multinomial coecient of the given numbers. It accomplishes this by determining the ratio of the facto

Chapter 8 Numeric Functions 185ODDThe ODD function rounds a number away from zero to the next odd number.ODD(num-to-round) Â num-to-round: The

PIThe PI function returns the approximate value of π (pi), the ratio of a circle’s circumference to its diameter.PI()Usage NotesThe PI function does n

Chapter 8 Numeric Functions 187Examples=POWER(2, 3) returns 8.=POWER(2, 10) returns 1024.=POWER(0.5, 3) returns 0.125.=POWER(100, 0.5) returns 1

“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26“Pasting from Examples in Help” on page 41QUOTIENTThe QUOTIENT function returns t

Chapter 8 Numeric Functions 189RANDThe RAND function returns a random number that is greater than or equal to 0 and less than 1.RAND()Usage Note

Chapter 1 Using Formulas in Tables 19To use m all the values in a row, rst click the row’s header cell or reference tab. Then, in Numbers, cli

Related TopicsFor related functions and additional information, see:“RAND” on page 189“Listing of Numeric Functions” on page 167“Value Types” on page

Chapter 8 Numeric Functions 191Examples=ROMAN(12) returns XII.=ROMAN(999) returns CMXCIX.=ROMAN(999, 1) returns LMVLIV.=ROMAN(999, 2) returns XM

“EVEN” on page 173“FLOOR” on page 176“INT” on page 17 8“MROUND” on page 183“ODD” on page 185“ROUNDDOWN” on page 192“ROUNDUP” on page 193“TRUNC” on pag

Chapter 8 Numeric Functions 193Related TopicsFor related functions and additional information, see:“CEILING” on page 170“EVEN” on page 173“FLOOR

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,

Chapter 8 Numeric Functions 195SIGNThe SIGN function returns 1 when the argument number is positive, –1 when it is negative, and 0 when it is ze

“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26“Pasting from Examples in Help” on page 41SQRTPIThe SQRTPI function returns the s

Chapter 8 Numeric Functions 197Date/time values can’t be added together, so only one date/time value (as discussed Âabove) is permitted. The va

If  sum-values is omitted, test-values would normally contain only number or duration values.ExamplesGiven the following table:=SUMIF(A1:A8, “<5”

Chapter 8 Numeric Functions 199SUMIFS(sum-values, test-values, condition, test-values…, condition…) Â sum-values: A collection containing the v

Apple Inc. K© 2009 Apple Inc. All rights reserved.Under the copyright laws, this manual may not be copied, in whole or in part, without the written co

In Numbers only, select a table cell and then choose Insert > Function > Formula ÂEditor. In Keynote and Pages, choose Formula Editor from the

Related TopicsFor related functions and additional information, see:“AVERAGEIF” on page 233“AVERAGEIFS” on page 234“COUNTIF” on page 247“COUNTIFS” on

Chapter 8 Numeric Functions 201Related TopicsFor related functions and additional information, see:“Listing of Numeric Functions” on page 167“Va

SUMX2MY2The SUMX2MY2 function returns the sum of the dierence of the squares of corresponding values in two collections.SUMX2MY2(set-1-values, set-2-

Chapter 8 Numeric Functions 203SUMX2PY2The SUMX2PY2 function returns the sum of the squares of corresponding values in two collections.SUMX2PY2(

SUMXMY2The SUMXMY2 function returns the sum of the squares of the dierences between corresponding values in two collections.SUMXMY2(set-1-values, set

Chapter 8 Numeric Functions 205Usage NotesIf  digits is omitted, it is assumed to be 0.Examples=TRUNC(1.49, 0) returns 1.=TRUNC(1.50, 0) retur

206The reference functions help you nd data within tables and retrieve data from cells.Listing of Reference FunctionsiWork provides these reference

Chapter 9 Reference Functions 207Function Description“LOOKUP” (page 217 ) The LOOKUP function nds a match for a given search value in one range

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, r

Chapter 9 Reference Functions 209AREASThe AREAS function returns the number of ranges the function references.AREAS(areas) Â areas: The areas t

Chapter 1 Using Formulas in Tables 21To add an operator or a constant, place the insertion point in the formula bar and Âtype. You can use the

Related TopicsFor related functions and additional information, see:“Listing of Reference Functions” on page 206“Value Types” on page 36“The Elements

Chapter 9 Reference Functions 2 11COLUMNSThe COLUMNS function returns the number of columns included in a specied range of cells.COLUMNS(range)

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

Chapter 9 Reference Functions 213“The Elements of Formulas” on page 15“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26“Past

INDEXThe INDEX function returns the value in the cell located at the intersection of the specied row and column within a range of cells or an array.I

Chapter 9 Reference Functions 215Except when INDEX is specied as shown in the third case above, Â row-index can’t be omitted, and if column-in

INDIRECTThe INDIRECT function returns the contents of a cell or range referenced by an address specied as a string.INDIRECT(addr-string, addr-style)

Chapter 9 Reference Functions 217LOOKUPThe LOOKUP function nds a match for a given search value in one range, and then returns the value in the

Related TopicsFor related functions and additional information, see:“HLOOKUP” on page 211“MATCH” on page 218“VLOOKUP” on page 223“Listing of Reference

Chapter 9 Reference Functions 219ExamplesGiven the following table:=MATCH(40, A1:A5) returns 4.=MATCH(40, E1:E5) returns 1.=MATCH(35, E1:E5, 1)

Although you can type a function into the text eld of the Formula Editor or into the formula bar (Numbers only), the Function Browser oers a conveni

 column-oset: The number of columns from the base cell to the target cell. column-oset is a number value. 0 means the target cell is in the same

Chapter 9 Reference Functions 221ROWThe ROW function returns the row number of the row containing a specied cell.ROW(cell) Â cell: An optional

Related TopicsFor related functions and additional information, see:“COLUMNS” on page 211“Listing of Reference Functions” on page 206“Value Types” on

Chapter 9 Reference Functions 223=INDEX(TRANSPOSE($A$1:$E$3),1,1) returns 5, the value in row 1. column 1 of the transposed range (was row 1, co

Usage NotesVLOOKUP compares a search value to the values in the leftmost column of a Âspecied range. Unless an exact match is required, the row cont

225The statistical functions help you manipulate and analyze collections of data using a variety of measures and statistical techniques.Listing of

Function Description“BETAINV” (page 237) The BETAINV function returns the inverse of the given cumulative beta distribution probability value.“BINOMDI

Chapter 10 Statistical Functions 227Function Description“EXPONDIST” (page 253) The EXPONDIST function returns the exponential distribution of th

Function Description“MAXA” (page 270) The MAXA function returns the largest number in a collection of values that may include text and Boolean values.

Chapter 10 Statistical Functions 229Function Description“QUARTILE” (page 284) The QUARTILE function returns the value for the specied quartile

Chapter 1 Using Formulas in Tables 23 2 In Pages or Keynote, choose Insert > Function > Show Function Browser to open the Function Browse

Function Description“VARA” (page 300) The VARA function returns the sample (unbiased) variance, a measure of dispersion, of a collection of values, in

Chapter 10 Statistical Functions 231AVERAGEThe AVERAGE function returns the average (arithmetic mean) of a collection of numbers.AVERAGE(num-dat

AVERAGEAThe AVERAGEA function returns the average (arithmetic mean) of a collection of values, including text and Boolean values.AVERAGEA(value, value

Chapter 10 Statistical Functions 233AVERAGEIFThe AVERAGEIF function returns the average (arithmetic mean) of the cells in a range that meet a gi

Related TopicsFor related functions and additional information, see:“AVERAGE” on page 231“AVERAGEA” on page 232“AVERAGEIFS” on page 234“Specifying Con

Chapter 10 Statistical Functions 235Usage Notes For each of the  test-values and condition pairs, the corresponding (same position within rang

“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26“Pasting from Examples in Help” on page 41BETADISTThe BETADIST function returns t

Chapter 10 Statistical Functions 237BETAINVThe BETAINV function returns the inverse of the given cumulative beta distribution probability value.

BINOMDISTThe BINOMDIST function returns the individual term binomial distribution probability of the specied form.BINOMDIST(success-num, trials, prob

Chapter 10 Statistical Functions 239CHIDISTThe CHIDIST function returns the one-tailed probability of the chi-square distribution.CHIDIST(non-ne

To view error and warning messages:Click the icon. mA message window summarizes each error and warning condition associated with "the cell.To hav

Examples=CHIINV(0.5, 2) returns 1.38629436111989.=CHIINV(0.1, 10) returns 15.9871791721053.=CHIINV(0.5, 1) returns 0.454936423119572.Related TopicsFor

Chapter 10 Statistical Functions 241ExampleGiven the following table:=CHITEST(A2:B6,A9:B13) returns 5.91020074984668E-236.Each expected value is

CONFIDENCEThe CONFIDENCE function returns a value for creating a statistical condence interval for a sample from a population with a known standard d

Chapter 10 Statistical Functions 243CORREL(y-values, x-values) Â y-values: The collection containing the y (dependent) values. y-values is a co

COUNTThe COUNT function returns the number of its arguments that contain numbers, numeric expressions, or dates.COUNT(value, value…) Â value: A value

Chapter 10 Statistical Functions 245“Listing of Statistical Functions” on page 225“Value Types” on page 36“The Elements of Formulas” on page 15“

Related TopicsFor related functions and additional information, see:“COUNT” on page 244“COUNTBLANK” on page 246“COUNTIF” on page 247“COUNTIFS” on page

Chapter 10 Statistical Functions 247Related TopicsFor related functions and additional information, see:“COUNT” on page 244“COUNTA” on page 245“

ExamplesThe table in this example is used to illustrate all variations of the COUNT function, including COUNTIF. The information is not meaningful, bu

Chapter 10 Statistical Functions 249COUNTIFS(test-values, condition, test-values…, condition…) Â test-values: A collection containing values to

Chapter 1 Using Formulas in Tables 25Cell references have dierent formats, depending on such factors as whether the cell’s table has headers, w

Related TopicsFor related functions and additional information, see:“COUNT” on page 244“COUNTA” on page 245“COUNTBLANK” on page 246“COUNTIF” on page 2

Chapter 10 Statistical Functions 251ExampleIn this example, the COVAR function is used to determine how closely related the price of heating oil

CRITBINOMThe CRITBINOM function returns the smallest value for which the cumulative binomial distribution is greater than or equal to a given value.CR

Chapter 10 Statistical Functions 253DEVSQThe DEVSQ function returns the sum of the squares of deviations of a collection of numbers from their a

Examples=EXPONDIST(4, 2, 1) returns 0.999664537372097 (cumulative distribution form).=EXPONDIST(4, 2, 0) returns 0.000670925255805024 (probability den

Chapter 10 Statistical Functions 255“FINV” on page 255“Listing of Statistical Functions” on page 225“Value Types” on page 36“The Elements of For

FORECASTThe FORECAST function returns the forecasted y value for a given x based on sample values using linear regression analysis.FORECAST(x-num-date

Chapter 10 Statistical Functions 257“Value Types” on page 36“The Elements of Formulas” on page 15“Using the Keyboard and Mouse to Create and Edi

ExampleAssume the following table contains the test scores of 30 students who recently took an exam you administered. Assume further that that the min

Chapter 10 Statistical Functions 259GAMMADISTThe GAMMADIST function returns the gamma distribution in the specied form.GAMMADIST(non-neg-x-valu

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 c

GAMMAINVThe GAMMAINV function returns the inverse gamma cumulative distribution.GAMMAINV(probability, alpha, beta) Â probability: A probability assoc

Chapter 10 Statistical Functions 261Related TopicsFor related functions and additional information, see:“GAMMADIST” on page 259“GAMMAINV” on pag

“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26“Pasting from Examples in Help” on page 41HARMEANThe HARMEAN function returns the

Chapter 10 Statistical Functions 263 Â x-numbers: The collection containing the x (independent) values. x-numbers is a collection containing nu

LARGEThe LARGE function returns the nth-largest value within a collection. The largest value is ranked number 1.LARGE(num-date-dur-set, ranking) Â num

Chapter 10 Statistical Functions 265“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26“Pasting from Examples in Help” on page

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 se

Chapter 10 Statistical Functions 267Additional StatisticsThis section discusses the additional statistics that can be returned by the LINEST fun

reg-ss: The regression sum of squares.reside-ss: The residual sum of squares.Usage NotesIt does not matter whether the known x values and known y va

Chapter 10 Statistical Functions 269“The Elements of Formulas” on page 15“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26“P

Chapter 1 Using Formulas in Tables 27To refer to a range of cells, click a cell in the range and drag up, down, left, or right Âto select or re

MAXThe MAX function returns the largest number in a collection.MAX(value, value…) Â value: A value. value can contain any value type. Â value…:Opti

Chapter 10 Statistical Functions 271Usage NotesText values and logical FALSE are given a value of 0 and logical TRUE is given a value Âof 1.To

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.Related TopicsFor related functions an

Chapter 10 Statistical Functions 273“MINA” on page 273“SMALL” on page 288“Listing of Statistical Functions” on page 225“Value Types” on page 36“

“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26“Pasting from Examples in Help” on page 41MODEThe MODE function returns the most

Chapter 10 Statistical Functions 275NEGBINOMDISTThe NEGBINOMDIST function returns the negative binomial distribution.NEGBINOMDIST(f-num, s-num,

NORMDISTThe NORMDIST function returns the normal distribution of the specied function form.NORMDIST(num, average, stdev, form-type) Â num: The numbe

Chapter 10 Statistical Functions 277NORMINVThe NORMINV function returns the inverse of the cumulative normal distribution.NORMINV(probability, a

Example=NORMSDIST(4.3) returns 0.999991460094529.Related TopicsFor related functions and additional information, see:“NORMDIST” on page 276“NORMSINV”

Chapter 10 Statistical Functions 279“The Elements of Formulas” on page 15“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26“P

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 fo

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

Chapter 10 Statistical Functions 281“Value Types” on page 36“The Elements of Formulas” on page 15“Using the Keyboard and Mouse to Create and Edi

POISSONThe POISSON function returns the probability that a specic number of events will occur using the Poisson distribution.POISSON(events, average,

Chapter 10 Statistical Functions 283 Â probability-values: The collection containing the probability values. probability-values is a collection

Related TopicsFor related functions and additional information, see:“BINOMDIST” on page 238“CRITBINOM” on page 252“NEGBINOMDIST” on page 275“PERMUT” o

Chapter 10 Statistical Functions 285Examples=QUARTILE({5, 6, 9, 3, 7, 11, 8, 2, 14}, 0) returns 2, the smallest value.=QUARTILE({5, 6, 9, 3, 7,

 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

Chapter 10 Statistical Functions 287SLOPEThe SLOPE function returns the slope of the best-t line for the collection using linear regression ana

“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 41SM

Chapter 10 Statistical Functions 289“Value Types” on page 36“The Elements of Formulas” on page 15“Using the Keyboard and Mouse to Create and Edi

Chapter 1 Using Formulas in Tables 29The Comparison OperatorsYou can use comparison operators to compare two values in formulas. Comparison oper

STDEVThe STDEV function returns the standard deviation, a measure of dispersion, of a collection of values based on their sample (unbiased) variance.S

Chapter 10 Statistical Functions 291“STDEVPA” on page 294“VAR” on page 298“VARA” on page 300“VARP” on page 302“VARPA” on page 303“Survey Results

ExampleAssume you have installed a temperature sensor in Cupertino, California. The sensor records each day’s high and low temperatures. In addition,

Chapter 10 Statistical Functions 293“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26“Pasting from Examples in Help” on page

Related TopicsFor related functions and additional information, see:“STDEV” on page 290“STDEVA” on page 291“STDEVPA” on page 294“VAR” on page 298“VARA

Chapter 10 Statistical Functions 295The standard deviation is the square root of the variance returned by the VARPA Âfunction.ExampleAssume you

“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26“Pasting from Examples in Help” on page 41TDISTThe TDIST function returns the pro

Chapter 10 Statistical Functions 297TINVThe TINV function returns the t value (a function of the probability and degrees of freedom) from the st

paired (1): Perform a paired test.two-sample equal (2): Perform a two-sample equal variance (homoscedastic) test.two-sample unequal (3): Perform a

Chapter 10 Statistical Functions 299Usage NotesThe VAR function nds the sample (unbiased) variance by dividing the sum of the Âsquares of the

13 Preface: Welcome to iWork Formulas & Functions15 Chapter 1: Using Formulas in Tables15 The Elements of Formulas17 Performing Instant Cal

The String Operator and the WildcardsThe string operator can be used in formulas and wildcards can be used in conditions.When you want to Use this str

“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

Chapter 10 Statistical Functions 301ExampleAssume you have installed a temperature sensor in Cupertino, California. The sensor records each day’

VARPThe VARP function returns the population (true) variance, a measure of dispersion, of a collection of values.VARP(num-date, num-date…) Â num-date:

Chapter 10 Statistical Functions 303“STDEVP” on page 293“STDEVPA” on page 294“VAR” on page 298“VARA” on page 300“VARPA” on page 303“Survey Resul

ExampleAssume you have installed a temperature sensor in Cupertino, California. The sensor records each day’s high and low temperatures. In addition,

Chapter 10 Statistical Functions 305ZTESTThe ZTEST function returns the one-tailed probability value of the Z-test.ZTEST(num-date-dur-set, num-d

306The text functions help you work with strings of characters.Listing of Text FunctionsiWork provides these text functions for use with tables.Func

Chapter 11 Text Functions 307Function Description“LEN” (page 315 ) The LEN function returns the number of characters in a string.“LOWER” (page 3

CHARThe CHAR function returns the character that corresponds to a decimal Unicode character code. CHAR(code-number) Â code-number: A number for which

Chapter 11 Text Functions 309Usage NotesThis can be helpful if text you paste from another application contains unwanted Âquestion marks, space

Chapter 1 Using Formulas in Tables 31Viewing All Formulas in a SpreadsheetIn Numbers, to view a list of all the formulas in a spreadsheet, choos

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,

Chapter 11 Text Functions 3 11“Listing of Text Functions” on page 306“Value Types” on page 36“The Elements of Formulas” on page 15“Using the Key

EXACTThe EXACT function returns TRUE if the argument strings are identical in case and content.EXACT(string-1, string-2) Â string-1: The rst string.

Chapter 11 Text Functions 313Specifying  start-pos permits you to begin the search for search-string within, rather than at the beginning of,

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

Chapter 11 Text Functions 315Related TopicsFor related functions and additional information, see:“MID” on page 316“RIGHT” on page 319“Listing of

LOWERThe LOWER function returns a string that is entirely lowercase, regardless of the case of the characters in the specied string.LOWER(source-stri

Chapter 11 Text Functions 317Examples=MID(”lorem ipsum dolor sit amet”, 7, 5) returns “ipsum”.=MID(”1234567890”, 4, 3) returns “456”.=MID(”short

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

Chapter 11 Text Functions 319REPTThe REPT function returns a string that contains a given string repeated a specied number of times.REPT(source

Finding and Replacing Formula ElementsIn Numbers, using the Find & Replace window, you can search through all of a spreadsheet’s formulas to nd a

Related TopicsFor related functions and additional information, see:“LEFT” on page 314“MID” on page 316“Listing of Text Functions” on page 306“Value T

Chapter 11 Text Functions 321Examples=SEARCH(”ra”, “abracadabra”) returns 3; the rst occurrence of the string “ra” starts at the third characte

SUBSTITUTEThe SUBSTITUTE function returns a string where the specied characters of a given string have been replaced with a new string.SUBSTITUTE(sou

Chapter 11 Text Functions 323TThe T function returns the text contained in a cell. This function is included for compatibility with tables impor

Related TopicsFor related functions and additional information, see:“Listing of Text Functions” on page 306“Value Types” on page 36“The Elements of Fo

Chapter 11 Text Functions 325VALUEThe VALUE function returns a number value even if the argument is formatted as text. This function is included

326The trigonometric functions help you work with angles and their components.Listing of Trigonometric FunctionsiWork provides these trigonometric f

Chapter 12 Trigonometric Functions 327Function Description“DEGREES” (page 334) The DEGREES function returns the number of degrees in an angle ex

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

Chapter 12 Trigonometric Functions 329ASINThe ASIN function returns the arcsine (the inverse sine) of a number.ASIN(num) Â num: A number. num i

33This chapter introduces the functions available in iWork.An Introduction to FunctionsA function is a named operation that you can include in a f

Examples=ASINH(27.2899171971277) returns 4.=ASINH(SINH(1)) returns 1.Related TopicsFor related functions and additional information, see:“ASIN” on pag

Chapter 12 Trigonometric Functions 331“DEGREES” on page 334“TAN” on page 338“TANH” on page 339“Listing of Trigonometric Functions” on page 326“V

“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

Chapter 12 Trigonometric Functions 333COSThe COS function returns the cosine of an angle that is expressed in radians.COS(radian-angle) Â radian

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

Chapter 12 Trigonometric Functions 335Related TopicsFor related functions and additional information, see:“ACOS” on page 327“ASIN” on page 329“A

“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

Chapter 12 Trigonometric Functions 337“COS” on page 333“DEGREES” on page 334“SINH” on page 337“TAN” on page 338“Listing of Trigonometric Functio

TANThe TAN function returns the tangent of an angle that is expressed in radians.TAN(radian-angle) Â radian-angle: An angle, expressed in radians. ra

Chapter 12 Trigonometric Functions 339TANHThe TANH function returns the hyperbolic tangent of the specied number.TANH(num) Â num: A number. nu

Information About FunctionsFor further information on Go toSyntax used in function denitions “Syntax Elements and Terms Used In Function Denitions”

340The in-depth examples and additional topics in this chapter illustrate working with some of the more complex functions.Additional Examples and To

Chapter 13 Additional Examples and Topics 341Common Arguments Used in Financial FunctionsMany arguments are common among related nancial functi

cum-when-dueSee discussion at when-due. The only dierence is that functions that use cum-when-due require the argument to be specied and do not assu

Chapter 13 Additional Examples and Topics 343depr-periodCertain functions return the amount of depreciation for a specied period. depr-period i

future-valueA future value is a cash ow received or paid at the end of the investment or loan period or the cash value remaining after the nal payme

Chapter 13 Additional Examples and Topics 345num-periods-yearThe calculation of the eective and nominal interest rates is based on the number o

periodic-rateIn some cases, when working with a series of cash ows, or an investment, or a loan, it may be necessary to know the interest rate each p

Chapter 13 Additional Examples and Topics 347salvageAssets often have value remaining at the end of the depreciable life or the expected useful

Choosing Which Time Value of Money Function to UseThis section provides additional information regarding the functions used to solve time value of mon

Chapter 13 Additional Examples and Topics 349Function and its purpose Arguments used by the function“PV” (page 141) is the function to use if yo

Chapter 2 Overview of the iWork Functions 35Term or symbol Meaningellipsis (…) An argument followed by an ellipsis can be repeated as many times

Irregular Cash Flows and Time IntervalsSome TVM problems involve irregular xed periodic cash ows where cash ows occur at regular time intervals but

Chapter 13 Additional Examples and Topics 351Which Function Should You Use to Solve Common Financial Questions?This section describes some commo

If you would like to know This function may be helpfulThe eective annual interest rate for a bond that pays interest only at its maturity (no periodi

Chapter 13 Additional Examples and Topics 353Example of a Loan Amortization TableThis example uses IPMT, PPMT, and PMT to construct a loan amort

The Completed Amortization TableTo complete the table, it would be necessary to select cells A10:A11 and extend the selection down to A32 to include a

Chapter 13 Additional Examples and Topics 355More on RoundingiWork supports many dierent functions that round numbers. This section compares th

To Use this function CommentsRound a number to the specied number of places“ROUND” (page 191 ) A positive number indicates the number of digits (deci

Chapter 13 Additional Examples and Topics 357To Use this function CommentsRound a number up (away from zero) to the specied number of places“RO

Using Logical and Information Functions TogetherLogical and information functions are often used together in a formula. Although logical functions are

Chapter 13 Additional Examples and Topics 359Expression 1=AND(B2>60, B2<=75) tests for a low score. If the test score is in the range 61 t

Value TypesA function argument has a type, which species what type of information the argument can contain. Functions also return a value of a partic

Trapping Division by ZeroSometimes it is not possible to construct a table in a manner that can avoid division by zero. However, if division by zero o

Chapter 13 Additional Examples and Topics 361Function Allows numeric comparisons Accepts wildcardsHLOOKUP no if exact match speciedMATCH no if

Survey Results ExampleThis example brings together the illustrations used throughout the statistical functions. It is based on a hypothetical survey.

Chapter 13 Additional Examples and Topics 363Function and arguments Description of result=CORREL(B2:B11, C2:C11) Determines the correlation of q

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

Index 365 IndexSymbols? wildcard 30, 361* wildcard 30, 361& string operator 30, 310~ wildcard escape character 30, 361AABS numeric function

366 Indexconstant dened 35conversion unitsdistance 80duration 80energy 81force 81liquid 82magnetism 82metric prexes 83power 82pressure

Index 367DEC2OCT 85DELTA 86ERF 87ERFC 87GESTEP 88HEX2BIN 89HEX2DEC 90HEX2OCT 91NUMTOBASE 92OCT2BIN 93OCT2DEC 94OCT2HEX 95EOMONTH date a

368 Indexhandling errors and warnings 23inserting cell references 26operators 28performing instant calculations 17using arithmetic operators 2

Index 369IF 15 8, 358IFERROR 15 9ISBLANK 160, 358iserror 161ISEVEN 162ISODD 163NOT 164OR 165, 358TRUE 166LOGINV statistical function 268L

Chapter 2 Overview of the iWork Functions 37Value Type Descriptionduration A duration is a length of time or a reference to a cell containing a

370 Indexcomparison 29string 30, 310OR logical and information function 165, 358Pparentheses syntax elements 34pasting help examples into a tab

Index 371COVAR 250, 364CRITBINOM 252DEVSQ 253EXPONDIST 253FDIST 254FINV 255FORECAST 256FREQUENCY 257GAMMADIST 259GAMMAINV 260GAMMALN 260

372 IndexASIN 329ASINH 329ATAN 330ATAN2 331ATANH 332COS 333COSH 334DEGREES 334RADIANS 335SIN 336SINH 337TAN 338TANH 339TRIM text funct

Value Type Descriptionlist A list is a comma-separated sequence of other values. For example, =CHOOSE(3, “1st”, “second”, 7, “last”). In some cases, t

Chapter 2 Overview of the iWork Functions 39Value Type Descriptionreference This is a reference to a single cell or a range of cells. If the ran

4 Contents45 DATEDIF47 DATEVALUE47 DAY48 DAYNAME49 DAYS36050 EDATE51 EOMONTH51 HOUR52 MINUTE53 MONTH54 MONTHNAME54 NETWORKDAYS55 NOW

Listing of Function CategoriesThere are several categories of functions. For example, some functions perform calculations on date/time values, logical

Chapter 2 Overview of the iWork Functions 41Pasting from Examples in HelpMany of the examples in help can be copied and pasted directly into a t

42The date and time functions help you work with dates and times to solve problems such as nding the number of working days between two dates or n

Chapter 3 Date and Time Functions 43Function Description“EDATE” (page 50) The EDATE function returns a date that is some number of months before

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 WEE

Chapter 3 Date and Time Functions 45“TIME” on page 56“Listing of Date and Time Functions” on page 42“Value Types” on page 36“The Elements of For

ExamplesIf 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

Chapter 3 Date and Time Functions 47DATEVALUEThe DATEVALUE function converts a date text string and returns a date/time value. This function is

Examples=DAY(”4/6/88 11:59:22 PM”) returns 6.=DAY(“5/12/2009”) returns 12.Related TopicsFor related functions and additional information, see:“DAYNAME

Chapter 3 Date and Time Functions 49Related TopicsFor related functions and additional information, see:“DAY” on page 47“MONTHNAME” on page 54“W

Contents 580 Supported Conversion Units80 Weight and mass80 Distance80 Duration81 Speed81 Pressure81 Force81 Energy82 Power82 Magnetism82

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

Chapter 3 Date and Time Functions 51EOMONTHThe EOMONTH function returns a date that is the last day of the month some number of months before or

Related TopicsFor related functions and additional information, see:“DAY” on page 47“MINUTE” on page 52“MONTH” on page 53“SECOND” on page 56“YEAR” on

Chapter 3 Date and Time Functions 53“Value Types” on page 36“The Elements of Formulas” on page 15“Using the Keyboard and Mouse to Create and Edi

MONTHNAMEThe MONTHNAME function returns the name of the month from a number. Month 1 is January.MONTHNAME(month-num) Â month-num: The desired month.

Chapter 3 Date and Time Functions 55Related TopicsFor related functions and additional information, see:“DATEDIF” on page 45“DAYS360” on page 49

SECONDThe 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

Chapter 3 Date and Time Functions 57Examples=TIME(12, 0, 0) returns 12:00 pm.=TIME(16, 45, 30) returns 4:45 pm.=TIME(0, 900, 0) returns 3:00 pm.

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

Chapter 3 Date and Time Functions 59WEEKDAYThe WEEKDAY function returns a number that is the day of the week for a given date.WEEKDAY(date, rst

6 Contents119 EFFECT120 FV122 INTRATE123 IPMT125 IRR126 ISPMT128 MIRR129 NOMINAL130 NPER132 NPV134 PMT135 PPMT137 PRICE138 PRICEDIS

WEEKNUMThe WEEKNUM function returns the number of the week within the year for a given date.WEEKNUM(date, rst-day) Â date: The date the function sho

Chapter 3 Date and Time Functions 61WORKDAYThe WORKDAY function returns the date that is the given number of working days before or after a give

YEARThe 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. Th

Chapter 3 Date and Time Functions 63YEARFRACThe YEARFRAC function nds the fraction of a year represented by the number of whole days between tw

64The duration functions help you work with periods of time (durations) by converting between dierent time periods, such as hours, days, and weeks.

Chapter 4 Duration Functions 65DUR2DAYSThe DUR2DAYS function converts a duration value to a number of days.DUR2DAYS(duration) Â duration: The l

Related TopicsFor related functions and additional information, see:“DUR2DAYS” on page 65“DUR2MILLISECONDS” on page 66“DUR2MINUTES” on page 67“DUR2SEC

Chapter 4 Duration Functions 67“Value Types” on page 36“The Elements of Formulas” on page 15“Using the Keyboard and Mouse to Create and Edit For

DUR2SECONDSThe DUR2SECONDS function converts a duration value to a number of seconds.DUR2SECONDS(duration) Â duration: The length of time to be conve

Chapter 4 Duration Functions 69DUR2WEEKSThe DUR2WEEKS function converts a duration value to a number of weeks.DUR2WEEKS(duration) Â duration: T

Contents 7172 COMBIN173 EVEN174 EXP174 FACT175 FACTDOUBLE176 FLOOR177 GCD178 INT179 LCM179 LN180 LOG181 LOG10182 MOD183 MROUND184 MUL

DURATIONThe DURATION function combines separate values for weeks, days, hours, minutes, seconds, and milliseconds and returns a duration value.DURATIO

Chapter 4 Duration Functions 71STRIPDURATIONThe STRIPDURATION function evaluates a given value and returns either the number of days represented

72The engineering functions help you calculate some common engineering values and convert between dierent numeric bases.Listing of Engineering Func

Chapter 5 Engineering Functions 73Function Description“DEC2OCT” (page 85) The DEC2OCT function converts a decimal number to the corresponding oc

Usage NotesThis function returns a number value and can properly be used in a formula Âcontaining other number values. Some other spreadsheet applica

Chapter 5 Engineering Functions 75Related TopicsFor related functions and additional information, see:“BESSELY” on page 75“Listing of Engineerin

BIN2DECThe BIN2DEC function converts a binary number to the corresponding decimal number.BIN2DEC(binary-string, convert-length) Â binary-string: The

Chapter 5 Engineering Functions 77BIN2HEXThe BIN2HEX function converts a binary number to the corresponding hexadecimal number.BIN2HEX(binary-st

BIN2OCTThe BIN2OCT function converts a binary number to the corresponding octal number.BIN2OCT(binary-string, convert-length) Â binary-string: The st

Chapter 5 Engineering Functions 79CONVERTThe CONVERT function converts a number from one measurement system to its corresponding value in anothe

8 Contents209 CHOOSE210 COLUMN211 COLUMNS211 HLOOKUP213 HYPERLINK214 INDEX216 INDIRECT217 LOOKUP218 MATCH219 OFFSET221 ROW221 ROWS22

Supported Conversion UnitsWeight and massMeasure ConstantGram “g” (can be used with metric prexes)Slug “sg”Pound mass (avoirdupois) “lbm”U (atomic ma

Chapter 5 Engineering Functions 81SpeedMeasure ConstantMiles per hour “mi/h”Miles per minute “mi/mn”Meters per hour “m/h” (can be used with metr

Measure ConstantWatt-hour “Wh” (can be used with metric prexes)Foot-pound “b”BTU “BTU”PowerMeasure ConstantHorsepower “HP”Watt “W” (can be used with

Chapter 5 Engineering Functions 83MetricprexesMeasure Constant Multiplierexa “E” 1E+18peta “P” 1E+15tera “T” 1E+12giga “G” 1E+09mega “M” 1E+06

Examples=DEC2BIN(100) returns 01100100.=DEC2BIN(”1001”, 12) returns 001111101001.Related TopicsFor related functions and additional information, see:“

Chapter 5 Engineering Functions 85Related TopicsFor related functions and additional information, see:“BIN2HEX” on page 77“DEC2BIN” on page 83“D

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

Chapter 5 Engineering Functions 87ERFThe ERF function returns the error function integrated between two values.ERF(lower, upper) Â lower: The l

Related TopicsFor related functions and additional information, see:“ERF” on page 87“Listing of Engineering Functions” on page 72“Value Types” on page

Chapter 5 Engineering Functions 89“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26“Pasting from Examples in Help” on page 4

Contents 9259 GAMMADIST260 GAMMAINV260 GAMMALN261 GEOMEAN262 HARMEAN262 INTERCEPT264 LARGE265 LINEST267 Additional Statistics268 LOGINV269

HEX2DECThe HEX2DEC function converts a hexadecimal number to the corresponding decimal number.HEX2DEC(hex-string, convert-length) Â hex-string: The s

Chapter 5 Engineering Functions 91HEX2OCTThe HEX2OCT function converts a hexadecimal number to the corresponding octal number.HEX2OCT(hex-string

NUMTOBASEThe NUMTOBASE function converts a number from base 10 into a number in the specied base.NUMTOBASE(decimal-string, base, convert-length) Â de

Chapter 5 Engineering Functions 93OCT2BINThe OCT2BIN function converts an octal number to the corresponding binary number.OCT2BIN(octal-string,

OCT2DECThe OCT2DEC function converts an octal number to the corresponding decimal number.OCT2DEC(octal-string, convert-length) Â octal-string: The st

Chapter 5 Engineering Functions 95OCT2HEXThe OCT2HEX function converts an octal number to the corresponding hexadecimal number.OCT2HEX(octal-str

96The nancial functions help you work with cash ows, depreciable assets, annuities, and investments by solving problems such as the amount of annu

Chapter 6 Financial Functions 97Function Description“COUPDAYSNC” (page 108) The COUPDAYSNC function returns the number of days between the settl

Function Description“ISPMT” (page 126) The ISPMT function returns the interest portion of a specied loan or annuity payment based on xed, periodic p

Chapter 6 Financial Functions 99Function Description“PV” (page 141) The PV function returns the present value of an investment or annuity based