iWork Formulas and Functions User Guide K Apple Inc. Apple, the Apple logo, iWork, Keynote, Mac, Mac OS, © 2009 Apple Inc. All rights reserved. Numbers, and Pages are trademarks of Apple Inc., registered in the U.S. and other countries. Under the copyright laws, this manual may not be copied, in whole or in part, without the written consent Adobe and Acrobat are trademarks or registered of Apple. Your rights to the software are governed by trademarks of Adobe Systems Incorporated in the U.S. the accompanying software license agreement. and/or other countries. The Apple logo is a trademark of Apple Inc., registered Other company and product names mentioned herein in the U.S. and other countries. Use of the “keyboard” are trademarks of their respective companies. Mention Apple logo (Option-Shift-K) for commercial purposes of third-party products is for informational purposes without the prior written consent of Apple may only and constitutes neither an endorsement nor a constitute trademark infringement and unfair recommendation. Apple assumes no responsibility with competition in violation of federal and state laws. regard to the performance or use of these products. Every effort has been made to ensure that the 019-1588 09/2009 information in this manual is accurate. Apple is not responsible for printing or clerical errors. Apple 1 Infinite Loop Cupertino, CA 95014-2084 408-996-1010 www.apple.com Contents 13 Preface: Welcome to iWork Formulas & Functions 15 Chapter 1: Using Formulas in Tables 15 The Elements of Formulas 17 Performing Instant Calculations in Numbers 18 Using Predefined Quick Formulas 19 Creating Your Own Formulas 19 Adding and Editing Formulas Using the Formula Editor 20 Adding and Editing Formulas Using the Formula Bar 21 Adding Functions to Formulas 23 Handling Errors and Warnings in Formulas 24 Removing Formulas 24 Referring to Cells in Formulas 26 Using the Keyboard and Mouse to Create and Edit Formulas 27 Distinguishing Absolute and Relative Cell References 28 Using Operators in Formulas 28 The Arithmetic Operators 29 The Comparison Operators 30 The String Operator and the Wildcards 30 Copying or Moving Formulas and Their Computed Values 31 Viewing All Formulas in a Spreadsheet 32 Finding and Replacing Formula Elements 33 Chapter 2: Overview of the iWork Functions 33 An Introduction to Functions 34 Information About Functions 34 Syntax Elements and Terms Used In Function Definitions 36 Value Types 40 Listing of Function Categories 41 Pasting from Examples in Help 42 Chapter 3: Date and Time Functions 42 Listing of Date and Time Functions 44 DATE 3 45 DATEDIF 47 DATEVALUE 47 DAY 48 DAYNAME 49 DAYS360 50 EDATE 51 EOMONTH 51 HOUR 52 MINUTE 53 MONTH 54 MONTHNAME 54 NETWORKDAYS 55 NOW 56 SECOND 56 TIME 57 TIMEVALUE 58 TODAY 59 WEEKDAY 60 WEEKNUM 61 WORKDAY 62 YEAR 63 YEARFRAC 64 Chapter 4: Duration Functions 64 Listing of Duration Functions 65 DUR2DAYS 65 DUR2HOURS 66 DUR2MILLISECONDS 67 DUR2MINUTES 68 DUR2SECONDS 69 DUR2WEEKS 70 DURATION 71 STRIPDURATION 72 Chapter 5: Engineering Functions 72 Listing of Engineering Functions 73 BASETONUM 74 BESSELJ 75 BESSELY 76 BIN2DEC 77 BIN2HEX 78 BIN2OCT 79 CONVERT 4 Contents 80 Supported Conversion Units 80 Weight and mass 80 Distance 80 Duration 81 Speed 81 Pressure 81 Force 81 Energy 82 Power 82 Magnetism 82 Temperature 82 Liquid 83 Metric prefixes 83 DEC2BIN 84 DEC2HEX 85 DEC2OCT 86 DELTA 87 ERF 87 ERFC 88 GESTEP 89 HEX2BIN 90 HEX2DEC 91 HEX2OCT 92 NUMTOBASE 93 OCT2BIN 94 OCT2DEC 95 OCT2HEX 96 Chapter 6: Financial Functions 96 Listing of Financial Functions 99 ACCRINT 101 ACCRINTM 103 BONDDURATION 104 BONDMDURATION 105 COUPDAYBS 107 COUPDAYS 108 COUPDAYSNC 109 COUPNUM 110 CUMIPMT 112 CUMPRINC 114 DB 116 DDB 117 DISC Contents 5 119 EFFECT 120 FV 122 INTRATE 123 IPMT 125 IRR 126 ISPMT 128 MIRR 129 NOMINAL 130 NPER 132 NPV 134 PMT 135 PPMT 137 PRICE 138 PRICEDISC 140 PRICEMAT 141 PV 144 RATE 146 RECEIVED 147 SLN 148 SYD 149 VDB 150 YIELD 152 YIELDDISC 153 YIELDMAT 155 Chapter 7: Logical and Information Functions 155 Listing of Logical and Information Functions 156 AND 157 FALSE 158 IF 159 IFERROR 160 ISBLANK 161 ISERROR 162 ISEVEN 163 ISODD 164 NOT 165 OR 166 TRUE 167 Chapter 8: Numeric Functions 167 Listing of Numeric Functions 170 ABS 170 CEILING 6 Contents 172 COMBIN 173 EVEN 174 EXP 174 FACT 175 FACTDOUBLE 176 FLOOR 177 GCD 178 INT 179 LCM 179 LN 180 LOG 181 LOG10 182 MOD 183 MROUND 184 MULTINOMIAL 185 ODD 186 PI 186 POWER 187 PRODUCT 188 QUOTIENT 189 RAND 189 RANDBETWEEN 190 ROMAN 191 ROUND 192 ROUNDDOWN 193 ROUNDUP 195 SIGN 195 SQRT 196 SQRTPI 196 SUM 197 SUMIF 198 SUMIFS 200 SUMPRODUCT 201 SUMSQ 202 SUMX2MY2 203 SUMX2PY2 204 SUMXMY2 204 TRUNC 206 Chapter 9: Reference Functions 206 Listing of Reference Functions 207 ADDRESS 209 AREAS Contents 7 209 CHOOSE 210 COLUMN 211 COLUMNS 211 HLOOKUP 213 HYPERLINK 214 INDEX 216 INDIRECT 217 LOOKUP 218 MATCH 219 OFFSET 221 ROW 221 ROWS 222 TRANSPOSE 223 VLOOKUP 225 Chapter 10: Statistical Functions 225 Listing of Statistical Functions 230 AVEDEV 231 AVERAGE 232 AVERAGEA 233 AVERAGEIF 234 AVERAGEIFS 236 BETADIST 237 BETAINV 238 BINOMDIST 239 CHIDIST 239 CHIINV 240 CHITEST 242 CONFIDENCE 242 CORREL 244 COUNT 245 COUNTA 246 COUNTBLANK 247 COUNTIF 248 COUNTIFS 250 COVAR 252 CRITBINOM 253 DEVSQ 253 EXPONDIST 254 FDIST 255 FINV 256 FORECAST 257 FREQUENCY 8 Contents 259 GAMMADIST 260 GAMMAINV 260 GAMMALN 261 GEOMEAN 262 HARMEAN 262 INTERCEPT 264 LARGE 265 LINEST 267 Additional Statistics 268 LOGINV 269 LOGNORMDIST 270 MAX 270 MAXA 271 MEDIAN 272 MIN 273 MINA 274 MODE 275 NEGBINOMDIST 276 NORMDIST 277 NORMINV 277 NORMSDIST 278 NORMSINV 279 PERCENTILE 280 PERCENTRANK 281 PERMUT 282 POISSON 282 PROB 284 QUARTILE 285 RANK 287 SLOPE 288 SMALL 289 STANDARDIZE 290 STDEV 291 STDEVA 293 STDEVP 294 STDEVPA 296 TDIST 297 TINV 297 TTEST 298 VAR 300 VARA 302 VARP 303 VARPA Contents 9 305 ZTEST 306 Chapter 11: Text Functions 306 Listing of Text Functions 308 CHAR 308 CLEAN 309 CODE 310 CONCATENATE 311 DOLLAR 312 EXACT 312 FIND 313 FIXED 314 LEFT 315 LEN 316 LOWER 316 MID 317 PROPER 318 REPLACE 319 REPT 319 RIGHT 320 SEARCH 322 SUBSTITUTE 323 T 323 TRIM 324 UPPER 325 VALUE 326 Chapter 12: Trigonometric Functions 326 Listing of Trigonometric Functions 327 ACOS 328 ACOSH 329 ASIN 329 ASINH 330 ATAN 331 ATAN2 332 ATANH 333 COS 334 COSH 334 DEGREES 335 RADIANS 336 SIN 337 SINH 338 TAN 10 Contents