Index

Symbols

• * (asterisk), 196
• {} (braces), 56–57
• $ (dollar sign), 349
• ("") (empty string), 319
• = (equal sign), 19
• > (greater than), 334
• >= (greater than or equal to), 334
• < (less than), 334
• <= (less than or equal to), 334
• ? (question mark), 196

A

• ABS function, 135–136
• absolute addressing, 23
• absolute deviations, 171
• ABSOLUTE (ABS) function, 134
• absolute reference, 270, 349
• accelerated depreciation schedule, 106–107, 109–112
• Accounting format, 86–88
• active cell, 1
• Add Watch dialog box, 78–79
• addition, 26, 346
• address
  • cell, 14
  • range, 15
• address argument, 290
• ADDRESS function, 270–274
• Alexander, Michael (author)
  • Excel VBA Programming For Dummies, 5th Edition, 357
• analyzing
  • data with percentiles and bins, 183–194
  • deviations, 170–172
• AND function, 267–269, 333–334
• appreciation. See also depreciation
  • about, 103–104
  • FV function, 92, 103, 104–106
  • IRR function, 114–118
• arguments
  • address, 290
  • for CELL function, 290–291
  • col, 290
  • color, 290
  • contents, 290
  • defined, 37
  • directory, 294
  • filename, 290
  • format, 290, 292–293
  • for functions, 29–32
  • Numfile, 294
  • origin, 294
  • osversion, 294
  • parentheses, 290
  • prefix, 290
  • protect, 291
  • recalc, 294
  • release, 294
  • row, 291
  • selecting functions that take no, 41–42
  • selecting functions that use, 42–43
  • system, 294
  • type, 291
  • values for SUBTOTAL function, 149–151
  • width, 291
• arrays
  • about, 55–57
  • functions that return, 61–64
  • using in formulas, 57–60
• assembling formulas, 25–27
• asterisk (*), 196
• auditing formulas, 75–78
• Automatic Calculation, 69
• AutoSum, 122–124
• AVEDEV function, 171
• average
  • about, 162
  • weighted, 166–167
• AVERAGE function
  • about, 360
  • analyzing deviations, 170–172
  • argument values for SUBTOTAL function, 149–151
  • arrays, 60
  • nesting functions, 32–35
  • using, 27–29, 163–167
  • using in formulas, 27–29

B

• bell curve, 172–174
• binary numbers, 368–369
• bins
  • defined, 183
  • FREQUENCY function, 189–191
  • LARGE function, 193–194
  • MAX function, 191–193
  • MIN function, 191–193
  • RANK function, 186–187
  • SMALL function, 193–194
• braces ({}), 56–57
• Break Link button, 72
• broken links, mending, 70–72

C

• Calculation feature
  • about, 12
  • enabling/disabling, 349–350
• case, text, 320–321
• CASE function, 373
• cash flow, 86
• CEILING function, 126, 127–129
• CELL function, 289–294
• cells
  • about, 13–17
  • address, 14
  • entering as function arguments, 43–48
  • entering formulas and functions in worksheet, 51–53
• Change Source button, 71
• Check Status button, 72
• Chi Square test, 204–207
• CHISQ.DIST.RT function, 206–207
• CHISQ.INV.RT function, 206–207
• CHISQ.TEST function, 205–206
• CHOOSE function, 263–265, 267–268
• circular references, avoiding, 68–70
• circumference, calculating with Pi, 138
• col argument, 290
• color, worksheet, 10
• color argument, 290
• COLUMN function, 274–276
• columns, 13–17
• COLUMNS function, 274–276
• COMBIN function, 144–145
• combinations, 144–145
• comparing
  • data sets, 179–183
  • text, 321–326
• CONCATENATE function, 308–309
• concatenation, 308–309, 337
• Conditional Formatting, 353–354
• contents argument, 290
• CONVERT function, 369–370
• copying formulas with fill handle, 24–25
• CORREL function, 181–183, 215
• correlation, 181–183, 215
• COS function, 156–157
• COUNT function, 27–29, 150, 195–196, 361
• COUNTA function, 150, 195–196
• COUNTIF function, 196–198, 235, 236, 252, 364–365
• covariance, 179–181
• COVARIANCE function, 179–181
• Create Table dialog box, 45–46
• criteria
  • about, 329
  • establishing, 331–333
  • fine-tuning, 333–334
• cumulative probability, 221, 225
• currency, formatting, 86–88
• Customize Format dialog box, 89–90
• Customize Regional Options dialog box, 230–231

D

• data
  • analyzing with percentiles and bins, 183–194
  • formatting, 17–18
• data sets, comparing, 179–183
• Data Validation, 354–355
• database functions
  • about, 327
  • DAVERAGE function, 332, 335–336
  • DCOUNT function, 336–338
  • DGET function, 339
  • DMAX function, 338
  • DMIN function, 338
  • DPRODUCT function, 339–341
  • DSUM function, 335
  • AND function, 333–334
  • OR function, 333–334
  • structure of databases, 328–329
  • working with, 329–333
• database range, 329
• databases, establishing, 329–331
• DATE function, 232–233
• date functions
  • about, 229–231
  • DATE function, 232–233
  • DATEDIF function, 245–246
  • DATEVALUE function, 237–239
  • DAY function, 234–236
  • formatting dates, 231–232
  • MONTH function, 234, 236–237
  • NETWORKDAYS function, 242–243
  • TODAY function, 41–42, 239–240, 254, 363
  • WEEKDAY function, 240–242
  • WORKDAY function, 244
  • YEAR function, 234, 237
• DATEDIF function, 245–246
• dates, formatting, 231–232
• DATEVALUE function, 237–239
• DAVERAGE function, 332, 335–336
• DAY function, 234–236
• DB function, 104, 112–114
• DCOUNT function, 336–338
• DDB function, 104, 111–112
• decimal numbers, 368–369
• Defined Names feature, 11
• DEGREES function, 157
• deleting worksheets, 10–11
• dependents, 75–78
• depreciable cost, 107
• depreciation. See also appreciation
  • about, 103–104, 106–108
  • accelerated schedule, creating, 109–112
  • DB function, 104, 112–114
  • DDB function, 104, 111–112
  • mid-year schedule, creating, 112–114
  • SLN function, 104, 108–109
  • straight line, calculating, 108–109
  • SYD function, 104, 109–111
• descriptive statistics
  • AVERAGE function, 27–29, 32–35, 60, 150, 163–167, 171, 360
  • bins, 183–194
  • COUNT function, 27–29, 150, 195–196, 361
  • COUNTA function, 195–196
  • COUNTIF function, 196–198, 235, 236, 252, 364–365
  • MEAN function, 163–167
  • MODE function, 163–167
  • percentiles, 183–194
  • variance, 167–170
• deviations, analyzing, 170–172
• DGET function, 339
• dialog boxes
  • Add Watch, 78–79
  • Create Table, 45–46
  • Customize Format, 89–90
  • Customize Regional Options, 230–231
  • Edit Links, 70–72
  • Edit Name, 330–331
  • Error Checking, 80, 348
  • Evaluate Formula, 79–80
  • Excel Options, 14, 69–70, 348, 350
  • Format Cells, 18, 231–232, 248–249, 292, 310
  • Function Arguments, 41–42, 43, 46–47, 49
  • Insert Function. See Insert Function dialog box
  • Name Manager, 330
  • New Name, 56, 351–352
  • Paste Special, 63–64
• diameter, calculating with Pi, 138
• directional rounding, 126–127
• directory argument, 294
• disabling
  • calculation, 349–350
  • Formula AutoComplete, 53
• displaying
  • formulas, 346–347
  • Insert Function dialog box, 38–39

• #DIV/0! error, 27

• division, 26, 346
• DMAX function, 338
• DMIN function, 338
• DOLLAR function, 310–311
• dollar sign ($), 23, 108–109, 349
• Double Declining Balance depreciation
  • about, 107
  • calculating, 109–112
• DPRODUCT function, 339–341
• DSUM function, 335
• duration of the loan, 90

E

• Edit Links dialog box, 70–72
• Edit Name dialog box, 330–331
• editing functions, 49–53
• effective interest rate, 99
• empty strings, 319
• enabling
  • calculation, 349–350
  • Formula AutoComplete, 53
• End If statements, 261
• equal sign (=), 19
• Error Checking dialog box, 80, 348
• errors
  • about, 27, 65
  • auditing formulas, 75–78
  • catching as you enter them, 65–75
  • evaluating and checking, 79–80
  • IfError feature, 81
  • Watch Window, 78–79
• estimation
  • about, 199
  • Chi Square test, 204–207
  • TTEST function, 200–204
• Evaluate Formula dialog box, 79–80
• evaluating errors, 79–80
• EVEN function, 126, 130
• even numbers, rounding to next, 130
• EXACT function, 321–323
• Excel. See also specific topics
  • about, 7–8
  • fundamentals, 8–19
• Excel 2019 For Dummies (Harvey), 2
• Excel Options dialog box, 14, 69–70, 348, 350
• Excel VBA Programming For Dummies, 5th Edition (Alexander), 357
• exponential model, 210–211
• exponents, 25, 346
• extensions, 8

F

• FACT function, 371
• factorials, 371
• field, 329
• filename argument, 290
• fill handle, copying formulas with, 24–25
• FIND function, 323–324
• FLOOR function, 126, 127–129
• FORECAST function, 215–217
• format argument, 290, 292–293
• Format Cells dialog box, 18, 231–232, 248–249, 292
• formatting
  • Conditional Formatting, 353–354
  • currencies, 86–88
  • data, 17–18
  • dates, 231–232
  • time, 248–249
• Formula Auditing feature, 12, 352
• Formula AutoComplete, 53
• Formula Bar, 14, 49–50
• Formula Box, 14
• Formula Error Checker, 72–75
• formulas
  • about, 19–20
  • assembling, 25–27
  • auditing, 75–78
  • copying with fill handle, 24–25
  • directly entering, 51–53
  • displaying, 346–347
  • entering in Formula Bar, 49–50
  • entering in worksheet cells, 51–53
  • entering your first, 20–21
  • errors. See errors
  • fixing, 348
  • matching parentheses in, 66–68
  • tips for working with, 345–357
  • using arrays in, 57–60
  • using functions in, 27–35
• Formulas tab, 11–13
• FORMULATEXT function, 286
• frequency distribution, 191
• FREQUENCY function, 189–191
• Function Arguments dialog box, 31, 41–42, 43, 46–47, 49
• Function Library feature, 11
• function tools
  • about, 37
  • directly entering formulas and functions, 51–53
  • finding functions, 39–40
  • Insert Function dialog box, 37–39, 40–49
• functions. See also specific functions
  • arguments for, 29–32
  • creating, 355–357
  • directly entering, 51–53
  • editing, 49–53
  • entering in Formula Bar, 49–50
  • entering in worksheet cells, 51–53
  • entering using Insert Functions dialog box, 40–49
  • finding, 39–40
  • inputs for, 29
  • most common, 359–365
  • nesting, 32–35
  • recommended, 367–373
  • that return arrays, 61–64
  • using in formulas, 27–35
• functions, database
  • about, 327
  • DAVERAGE function, 332, 335–336
  • DCOUNT function, 336–338
  • DGET function, 339
  • DMAX function, 338
  • DMIN function, 338
  • DPRODUCT function, 339–341
  • DSUM function, 335
  • AND function, 333–334
  • OR function, 333–334
  • structure of databases, 328–329
  • working with, 329–333
• functions, date
  • about, 229–231
  • DATE function, 232–233
  • DATEDIF function, 245–246
  • DATEVALUE function, 237–239
  • DAY function, 234–236
  • formatting dates, 231–232
  • MONTH function, 234, 236–237
  • NETWORKDAYS function, 242–243
  • TODAY function, 41–42, 239–240, 254, 363
  • WEEKDAY function, 240–242
  • WORKDAY function, 244
  • YEAR function, 234, 237
• functions, information
  • about, 289
  • CELL function, 289–294
  • INFO function, 294–295
  • IS function, 295–299
  • ISBLANK function, 297–299
  • ISERR function, 296–297
  • ISERROR function, 296–297
  • ISNA function, 296–297
  • ISNONTEXT function, 297–299
  • ISNUMBER function, 297–299
  • ISTEXT function, 297–299
  • TYPE function, 299–301
• functions, math
  • about, 119
  • ABSOLUTE function, 134
  • COMBIN function, 144–145
  • COS function, 156–157
  • INT function, 131–132, 140–141, 361
  • MOD function, 148–149
  • multiplying multiple numbers, 146–147
  • PERMUT function, 143–144
  • Pi, 138
  • POWER function, 145–146
  • PRODUCT function, 31–32, 42–43, 146–147, 150
  • RAND function, 139–141, 371
  • RANDBETWEEN function, 141–142, 371
  • random numbers, 139–142
  • ROUND function, 124–130, 140–141, 362
  • SIGN function, 133–135
  • SIN function, 156–157
  • SUBTOTAL function, 149–151
  • SUM function, 27–29, 43–48, 57–60, 119–124, 150, 360
  • SUMIF function, 134, 153–156, 235, 266, 364–365
  • SUMIFS function, 153–156
  • SUMPRODUCT function, 58, 151–153, 167
  • TAN function, 156–157
  • trigonometry, 156–157
  • TRUNC function, 132–133
• functions, text
  • about, 303
  • CONCATENATE function, 308–309
  • DOLLAR function, 310–311
  • EXACT function, 321–323
  • FIND function, 323–324
  • LEFT function, 303–305, 307, 321
  • LEN function, 307–308, 321
  • LOWER function, 320–321
  • MID function, 303, 306–307
  • PROPER function, 320–321
  • REPLACE function, 315–317
  • REPT function, 314–315
  • RIGHT function, 303, 305, 307, 321
  • SEARCH function, 324–326
  • SUBSTITUTE function, 315, 317–319
  • TEXT function, 311–314
  • TRIM function, 319–320
  • UPPER function, 320–321
• functions, time
  • about, 247–248
  • calculating elapsed time over days, 255
  • formatting time, 248–249
  • HOUR function, 251–252
  • MINUTE function, 251, 253
  • NOW function, 29, 254, 363
  • SECOND function, 251, 253–254
  • TIME function, 250
  • TIMEVALUE function, 250–251
• FUTURE VALUE (FV) function, 92, 103, 104–106

G

• GCD function, 370
• greater than (>), 334
• greater than or equal to (>=), 334
• greatest common divisor, 370
• GROWTH function, 219–220

H

• Harvey, Greg (author)
  • Excel 2019 For Dummies, 2
• headers, 329
• Help, 19, 48
• hexadecimal numbers, 368–369
• histogram, 191
• HLOOKUP function, 278–281, 363
• Hot Items, 261–262
• HOUR function, 251–252

I

• icons, explained, 2–3
• IF function, 258–263, 362
• IfError feature, 81
• ignoring signs, 135–136
• INDEX function, 281–285
• inferential statistics
  • about, 199–200
  • Chi Square test, 204–207
  • TTEST function, 200–204
• INFO function, 294–295
• information functions
  • about, 289
  • CELL function, 289–294
  • INFO function, 294–295
  • IS function, 295–299
  • ISBLANK function, 297–299
  • ISERR function, 296–297
  • ISERROR function, 296–297
  • ISNA function, 296–297
  • ISNONTEXT function, 297–299
  • ISNUMBER function, 297–299
  • ISTEXT function, 297–299
  • TYPE function, 299–301
• Insert Function dialog box
  • about, 37–39
  • arguing with functions, 29–32
  • creating functions, 355–357
  • entering functions using, 40–49
  • function categories, 40
  • help in, 48
• inserting worksheets, 10
• INT function, 131–132, 140–141, 361
• INTERCEPT function, 211–215
• interest payments, calculating, 93–94
• interest rates, 85–86, 90. See also loans and interest rates, calculating
• INTERNAL RATE OF RETURN (IRR) function, 103, 114–118
• internals, measuring, 114–118
• inverse correlation, 182
• IPMT function, 29, 93–94
• IRR (INTERNAL RATE OF RETURN) function, 103, 114–118
• irrational number, 138
• IS function, 295–299
• ISBLANK function, 297–299
• ISERR function, 296–297
• ISERROR function, 296–297
• ISNA function, 296–297
• ISNONTEXT function, 297–299
• ISNUMBER function, 297–299, 364
• ISTEXT function, 297–299

K

• KURT function, 178–179
• kurtosis, 178–179

L

• LARGE function, 193–194
• LCM function, 370
• least common multiple, 370
• LEFT function, 303–305, 307, 321
• LEN function, 307–308, 321, 373
• less than (<), 334
• less than or equal to (<=), 334
• linear model, 210
• linear regression line, 210
• literal, 263
• loan principal, 90
• loans and interest rates, calculating
  • about, 85–86
  • calculating loans, 90–102
  • money values, 86–90
• logical functions. See lookup, logical, and reference functions
• lookup, logical, and reference functions
  • about, 257
  • ADDRESS function, 270–274
  • AND function, 267–269, 333–334
  • CHOOSE function, 263–265, 267–268
  • COLUMN function, 274–276
  • COLUMNS function, 274–276
  • FORMULATEXT function, 286
  • HLOOKUP function, 278–281, 363
  • IF function, 258–263, 362
  • INDEX function, 281–285
  • MATCH function, 281–285
  • NOT function, 265–266
  • NUMBERVALUE function, 286–287
  • OFFSET function, 276–278
  • OR function, 267–269, 333–334
  • ROW function, 274–276
  • ROWS function, 274–276
  • VLOOKUP function, 278–281, 363
  • XOR function, 269–270
• LOWER function, 320–321

M

• marquee, 23
• MATCH function, 281–285
• math functions
  • about, 119
  • ABSOLUTE function, 134
  • COMBIN function, 144–145
  • COS function, 156–157
  • INT function, 131–132, 140–141, 361
  • MOD function, 148–149
  • multiplying multiple numbers, 146–147
  • PERMUT function, 143–144
  • Pi, 138
  • POWER function, 145–146
  • PRODUCT function, 31–32, 42–43, 146–147, 150
  • RAND function, 139–141, 371
  • RANDBETWEEN function, 141–142, 371
  • random numbers, 139–142
  • ROUND function, 124–130, 140–141, 362
  • SIGN function, 133–135
  • SIN function, 156–157
  • SUBTOTAL function, 149–151
  • SUM function, 27–29, 43–48, 57–60, 119–124, 150, 360
  • SUMIF function, 134, 153–156, 235, 266, 364–365
  • SUMIFS function, 153–156
  • SUMPRODUCT function, 58, 151–153, 167
  • TAN function, 156–157
  • trigonometry, 156–157
  • TRUNC function, 132–133
• mathematical model, 209
• mathematical operators, order of, 25–26
• MAX function, 60, 150, 191–193, 364
• mean, 162, 163–164, 221
• MEAN function, 163–167
• measures of central tendency, 163
• measuring
  • internals, 114–118
  • text, 321–326
  • variance, 167–170
• median, 162
• MID function, 303, 306–307
• mid-year depreciation schedule, 112–114
• military time, 249
• MIN function, 60, 150, 191–193, 364
• MINUTE function, 251, 253
• mixed addressing, 23
• mixed reference, 270–271
• MOD function, 148–149
• mode, 163
• MODE function, 163–167
• models
  • about, 209
  • exponential, 210–211
  • linear, 210
• modulus, 148–149
• MONTH function, 234, 236–237
• multiples, rounding to, 127–129
• multiplication, 26, 346
• multiplying multiple numbers, 146–147

N

• #N/A! error, 27

• Name box, 14

• #NAME? error, 27

• Name Manager dialog box, 330
• name setup, 330
• named area
  • about, 351–352
  • creating, 16
  • entering as function arguments, 43–48
• naming ranges, 15–16
• negative deviations, 171
• nested IF functions, 260–263
• nesting functions, 32–35
• NETWORKDAYS function, 242–243
• New Name dialog box, 56, 351–352
• noncumulative probability, 221, 225
• normal distribution, 172–177
• NORM.DIST function, 221–223
• NOT function, 265–266
• NOW function, 29, 254, 363
• Nper (number of payments in period), 96–97
• NPER function, 96–97

• #NULL! error, 27
• #NUM! error, 27

• number of payments in period (Nper), 96–97
• numbers
  • binary, 368–369
  • decimal, 368–369
  • hexadecimal, 368–369
  • irrational, 138
  • octal, 368–369
  • random, 139–142
  • turning into text, 311–314
• NUMBERVALUE function, 286–287
• Numfile argument, 294

O

• octal numbers, 368–369
• ODD function, 126, 130
• odd numbers, rounding to next, 130
• OFFSET function, 276–278
• Open Source button, 72
• operator precedence, 345–346
• OR function, 267–269, 333–334
• order of mathematical operators, 25–26
• ordering items, 143–144
• origin argument, 294
• osversion argument, 294
• outlier, 165

P

• paired data, 201–202
• parameters, for functions, 29
• parentheses, 25, 66–68, 346
• parentheses argument, 290
• Paste Special dialog box, 63–64
• payment, loan, 90–92
• payment period, 90
• PDURATION function, 97–98
• PERCENTILE function, 185–186
• percentiles
  • defined, 183
  • PERCENTILE function, 185–186
  • PERCENTRANK function, 188
  • QUARTILE function, 183–184
• PERCENTRANK function, 188
• period, 86
• PERMUT function, 143–144
• permutation, 143–144
• Pi, 138
• PMT function, 90–92, 95
• Poisson distribution, 223–226
• POISSON.DIST function, 223–226
• population, 162
• POWER function, 145–146
• PPMT function, 94–95
• precedence, operator, 345–346
• precedents, 75–78
• predictions and probability
  • FORECAST function, 215–217
  • GROWTH function, 219–220
  • INTERCEPT function, 211–215
  • modeling, 210–211
  • NORM.DIST function, 221–223
  • POISSON.DIST function, 223–226
  • SLOPE function, 211–215
  • TREND function, 217–219
• prefix argument, 290
• principal
  • calculating, 101–102
  • calculating payments towards, 94–95
  • defined, 86
• probability. See predictions and probability
• PRODUCT function, 31–32, 42–43, 146–147, 150
• PROPER function, 320–321
• protect argument, 291
• PV function, 101–102

Q

• QUARTILE function, 183–184
• question mark (?), 196
• Quick Access Toolbar feature, 13

R

• RADIANS function, 157
• RAND function, 139–141, 371
• RANDBETWEEN function, 141–142, 371
• random numbers, 139–142
• ranges
  • about, 13–17
  • address, 15
  • database, 329
  • entering as function arguments, 43–48
  • naming, 15–16
• RANK function, 186–187
• RATE function, 98–101
• recalc argument, 294

• #REF! error, 27

• reference, lookup, and logical functions
  • about, 257
  • ADDRESS function, 270–274
  • AND function, 267–269, 333–334
  • CHOOSE function, 263–265, 267–268
  • COLUMN function, 274–276
  • COLUMNS function, 274–276
  • FORMULATEXT function, 286
  • HLOOKUP function, 278–281, 363
  • IF function, 258–263, 362
  • INDEX function, 281–285
  • MATCH function, 281–285
  • NOT function, 265–266
  • NUMBERVALUE function, 286–287
  • OFFSET function, 276–278
  • OR function, 267–269, 333–334
  • ROW function, 274–276
  • ROWS function, 274–276
  • VLOOKUP function, 278–281, 363
  • XOR function, 269–270
• references, 22–23
• relative addressing, 23
• relative reference, 270
• release argument, 294
• Remember icon, 2
• renaming worksheets, 10
• repeating text, 314–315
• REPLACE function, 315–317
• REPT function, 314–315
• returned value, 110
• RIGHT function, 303, 305, 307, 321
• ROMAN function, 371
• ROUND function, 124–130, 140–141, 362
• ROUNDDOWN function, 126–127
• ROUNDUP function, 126–127
• row argument, 291
• ROW function, 274–276
• rows, 13–17
• ROWS function, 274–276
• Rumsey, Deborah J. (author)
  • Statistics For Dummies, 2nd Edition, 207

S

• salvage value, 106
• sample, 162, 168
• scatter chart, 210
• SEARCH function, 324–326
• SECOND function, 251, 253–254
• sentence case, 321
• separators, 88–90
• SIGN function, 133–135
• significance tests. See inferential statistics
• signs, ignoring, 135–136
• SIN function, 156–157
• SKEW function, 178
• skewed from the norm, 177–179
• SLN function, 108–109
• SLOPE function, 211–215
• SMALL function, 193–194

• $ (dollar sign), 23, 108–109

• standard deviation, 171, 221
• standard normal distribution, 174
• STANDARDIZE function, 174–177
• statistics, 161–162
• statistics, descriptive
  • AVERAGE function, 27–29, 32–35, 60, 150, 163–167, 171, 360
  • bins, 183–194
  • COUNT function, 27–29, 150, 195–196, 361
  • COUNTA function, 195–196
  • COUNTIF function, 196–198, 235, 236, 252, 364–365
  • MEAN function, 163–167
  • MODE function, 163–167
  • percentiles, 183–194
  • variance, 167–170
• statistics, inferential
  • about, 199–200
  • Chi Square test, 204–207
  • TTEST function, 200–204
• Statistics For Dummies, 2nd Edition (Rumsey), 207
• STDEV.P function, 150, 171–172
• STDEV.S function, 150
• Straight Line depreciation
  • about, 107
  • calculating, 108–109
• string, 303, 319
• Student t-distribution, 200
• SUBSTITUTE function, 315, 317–319
• SUBTOTAL function, 149–151
• subtraction, 26, 346
• SUM function, 22
  • about, 360
  • argument value for SUBTOTAL function, 150
  • arrays, 57–60
  • entering functions, 43–48
  • using, 119–124
  • using in formulas, 27–29
• Sum of Years' Digits depreciation, 107
• SUMIF function, 134, 153–156, 235, 266, 364–365
• SUMIFS function, 153–156
• SUMPRODUCT function, 58, 151–153, 167
• swapping text, 315–319
• SYD function, 109–111
• system argument, 294

T

• tables
  • about, 13–17, 278
  • entering as function arguments, 43–48
• tails, 174
• TAN function, 156–157
• TDIST function, 202–203
• t-distribution, 203
• Technical Stuff icon, 3
• testing on one condition, 258–263
• text
  • case, 320–321
  • changing, 309–321
  • comparing, 321–326
  • converting dates from, 237–239
  • finding, 321–326
  • measuring, 321–326
  • repeating, 314–315
  • swapping, 315–319
  • trimming, 319–320
  • turning numbers into, 311–314
• TEXT function, 311–314
• text functions
  • about, 303
  • CONCATENATE function, 308–309
  • DOLLAR function, 310–311
  • EXACT function, 321–323
  • FIND function, 323–324
  • LEFT function, 303–305, 307, 321
  • LEN function, 307–308, 321
  • LOWER function, 320–321
  • MID function, 303, 306–307
  • PROPER function, 320–321
  • REPLACE function, 315–317
  • REPT function, 314–315
  • RIGHT function, 303, 305, 307, 321
  • SEARCH function, 324–326
  • SUBSTITUTE function, 315, 317–319
  • TEXT function, 311–314
  • TRIM function, 319–320
  • UPPER function, 320–321
• time, formatting, 248–249
• TIME function, 250
• time functions
  • about, 247–248
  • calculating elapsed time over days, 255
  • formatting time, 248–249
  • HOUR function, 251–252
  • MINUTE function, 251, 253
  • NOW function, 29, 254, 363
  • SECOND function, 251, 253–254
  • TIME function, 250
  • TIMEVALUE function, 250–251
• TIMEVALUE function, 250–251
• TINV function, 204
• Tip icon, 2
• TODAY function, 41–42, 239–240, 254, 363
• tracer arrow, 77
• TRANSPOSE function, 61–64
• TREND function, 217–219
• trigonometry functions, 156–157
• TRIM function, 319–320
• trimming text, 319–320
• TRUNC function, 132–133
• TTEST function, 200–204
• t-value, 202
• type argument, 291
• TYPE function, 299–301, 372

U

• Update Values button, 71
• UPPER function, 320–321

V

• #VALUE! error, 27

• variance
  • comparing data sets, 179–183
  • defined, 167
  • deviations, 170–172
  • measuring, 167–170
  • normal distribution, 172–177
  • skewed, 177–179
• VAR.P function, 150, 168–170
• VAR.S function, 150, 168–170
• VBA programming code, 357
• VLOOKUP function, 278–281, 363

W

• Warm Items, 261–262
• Warning! icon, 3
• Watch Window, 78–79
• WEEKDAY function, 240–242
• weighted average, 166–167
• width argument, 291
• workbooks, 8–11
• WORKDAY function, 244
• worksheets
  • about, 8–11
  • deleting, 10–11
  • inserting, 10
  • renaming, 10

X

• XIRR function, 118

• .xls/.xlsx extension, 8

• XOR function, 269–270

Y

• YEAR function, 234, 237
• YEARFRAC function, 372