Index
Symbols
& (ampersand) operator, 6
{ } (braces), in array formulas, 27
, (commas), arguments and, 72
$ (dollar sign), in cell references, 13
( ) (parentheses), in order of precedence, 8-9
(‘ ‘) (single quotation marks), in external references, 22
#CALC! error value, 50
#DIV/0! error value, 50
#FIELD! error value, 51
#NULL! error value, 53
#SPILL! error value, 28, 54, 202
#UNKNOWN! error value, 54
A
ABS() function, 183
absolute reference format, 13
ignoring when applying range names, 18
account numbers
accounts payable ratios, 223
accounts payable turnover, 223
days purchases in accounts payable, 223-224
sales to accounts payable, 224
accounts payable turnover, 223
accounts receivable aging worksheet, building, 122-124
accounts receivable ratios, 222
accounts receivable turnover, 222
accounts receivable turnover, 222
ACOS() function, 185
ACOSH() function, 185
ACOT() function, 185
ACOTH() function, 186
adding. See also inserting
constraints in Solver, 426-428
every nth row with MOD() function, 196
formulas to data tables, 402-403
months to dates, 163
with EDATE() function, 163
with EOMONTH() function, 164
scenarios
to Quick Access Toolbar, 416
times, 176
values, 192
with SUMIF() function, 193
with SUMIFS() function, 193-194
add-ins, loading Solver, 423
ADDRESS() function, 122
advertising, analyzing versus sales, 274
AGGREGATE() function, 183
aging overdue invoices, 123-124
aging worksheet (accounts receivable), building, 122-124
algebraic equations, solving, 411-412
allowing principal paydowns, 321-323
amortization schedules, building, 312-315, 320-321
ampersand (&) operator, 6
Analysis ToolPak
Regression tool, 273
analyzing
data. See Goal Seek; scenarios; what-if analysis
anchored cell references, 13
aging overdue invoices, 124
applying conditions across ranges, 116
looking up list values, 118
ANSI character set, 80
ANSI codes, 80
CODE() function, 83
Answer report (Solver), 437-439
applying range names to formulas, 17-20
approximations in Goal Seek, 409-410
AP. See accounts payable ratios
ARABIC() function, 183
arguments
commas and, 72
defined, 71
forms allowed, 72
in Function Arguments dialog box, 75-76
syntax, 71
arithmetic formulas, operators for, 5-6
array formulas
correspondences in, 28
defined, 25
on multiple ranges, 29
arrays
creating, 25
defined, 25
functions using/returning, 31-32
logical functions combined with, 116-121
resizing, 27
selecting, 27
sorting tables into
with SORTBY() function, 364-365
with SORT() function, 364
AR. See accounts receivable ratios
articles, sorting without, 363-364
ASIN() function, 186
ASINH() function, 186
ATAN() function, 186
ATAN2() function, 186
ATANH() function, 186
dependent tracing, 63
error cell tracing, 64
precedent tracing, 63
removing tracer arrows, 64
AutoComplete feature for functions, 73-74
automatic recalculation, 10
average deviation, 240
average difference, 240
AVERAGE() function, 233
AVERAGEA() function, 233
AVERAGEIF() function, 233
AVERAGEIFS() function, 234
averages, calculating, 232
with AVERAGE() function, 233
with AVERAGEA() function, 233
with AVERAGEIF() function, 233
with AVERAGEIFS() function, 234
with MEDIAN() function, 234
with MODE() function, 234
with SUMPRODUCT() function, 235-236
B
BAHTTEXT() function, 79
balloon loan payments, calculating, 308
BASE() function, 183
bell curves, 257-258. See also normal distribution
Bernoulli sampling process, 255
best-fit trendlines, 266
analyzing sales versus advertising, 274
calculating
with LINEST() function, 271-273
with TREND() function, 270-271
regression equation for, 268-269
billable time, rounding, 191-192
bin interval, 242
BINOM.DIST() function, 255
binomial distributions, 255
bins, 242
birthday, returning, 164
blanks, counting in ranges, 131-132
Boolean logic. See logical functions
borrowed amount, calculating, 318-319
braces ({ }), in array formulas, 27
break-even analysis
building. See also creating; entering
loan amortization schedules, 312-315, 320-321
built-in functions. See functions
business forecasting. See regression analysis
business formulas
financial, 213
cost of goods sold, 215
gross margin, 216
net margin, 216
inventory, 218
liquidity, 221
accounts payable ratios, 223-224
accounts receivable ratios, 222-223
pricing, 207
business modeling tools. See Goal Seek; scenarios; what-if analysis
buying equipment, leasing versus, 343-344
C
#CALC! error value, 50
calculated fields
defined, 391
calculated items
defined, 392
calculating
accounts payable turnover, 223
accounts receivable turnover, 222
averages, 232
with AVERAGE() function, 233
with AVERAGEA() function, 233
with AVERAGEIF() function, 233
with AVERAGEIFS() function, 234
with MEDIAN() function, 234
with MODE() function, 234
with SUMPRODUCT() function, 235-236
best-fit trendlines
with LINEST() function, 271-273
with TREND() function, 270-271
cash ratio, 227
cost of goods sold, 215
cost price, 209
cumulative principal and interest, 311-312
current ratio, 226
days purchases in accounts payable, 223-224
deposits for investment goals, 332-333
difference between dates, 169-170
with DAYS() function, 170
with DAYS360() function, 171-172
with NETWORKDAYS() function, 170-171
with YEARFRAC() function, 172
differences between times, 176-177
with MOD() function, 195
discount amount, 210
exponential trend values, 290-291
extreme values, 236
with LARGE() and SMALL() functions, 237-238
with MAX() and MIN() functions, 236
fixed assets to short-term debt, 218
fixed-asset turnover, 217
frequency distributions, 242-244
for investment goals, 334
gross margin, 216
Easter Sunday, 189
initial deposit for investment goals, 333
internal rate of return, 352
with XIRR() function, 353
inventory reorder level, 221
inventory safety stock, 220-221
inventory turnover, 219
inventory turnover rate, 219
Julian dates, 169
list price, 211
logarithmic trend values, 295-296
markup amount, 208
net margin, 216
payback period, 349
discounted, 351
periods for investment goals, 331-332
polynomial regression values, 301-302
preventing calculation errors, 190-191
principal and interest, 309-310
range, 240
rank, 239
return on fixed assets, 217-218
sales per employee, 215
sales to accounts payable, 224
sales to current assets, 214
sales to inventory, 220
sales to short-term debt, 214
selling price, 208
tiered payments, 110
value field summaries, 383
difference calculations, 383-385
percentage calculations, 385-387
running total calculations, 388-389
weighted questionnaire results, 139
working capital turnover, 225
calculation of formulas, controlling, 9-10
carriage returns, removing with CLEAN() function, 90
cash flows, discounting, 344-345
calculating net present value, 345-349
cash-flow analysis, book publishing example, 354-358. See also discount formulas
operating costs and sales, 355-356
per-unit constants, 355
cash ratio, 227
catalog price, 209
categories, consolidating by, 38-39
categorizing by values, 112-113
causation, 252
CEILING.MATH() function, 183, 188-189
cell errors. See error cells
CELL() function, 99-100, 125-127
cell references. See also referencing
absolute format, 13
ignoring when applying range names, 18
changing format of, 14
circular, troubleshooting, 56-57
converting range names to, 52
external, 22
mixed format, 14
in range names, 13
ignoring when applying range names, 18
cells
linking dialog box controls to, 43
padding, 92
changing
cell reference format, 14
cells
in Goal Seek, 406
in scenarios, 414
in Solver, 422
link source, 23
value field summary calculations, 383
difference calculations, 383-385
percentage calculations, 385-387
running total calculations, 388-389
CODE() function, 83
characters
counting with SUBSTITUTE() function, 102
removing extra, 89
with CLEAN() function, 90
removing from strings, 101-102
repeating with REPT() function, 92-93
charts, building text-based, 92-93
check boxes, inserting, 46
day of week, determining, 137
disadvantages of, 140
month of fiscal year, determining, 138-139
option buttons, integration with, 139-140
weighted questionnaire results, calculating, 139
client workbooks, 21
coefficient of determination (R2), 269-270
COGS (cost of goods sold), 215
column letters, extracting, 99-100
column names in range names, 18-20
columns
creating ledger shading, 198
lookup column, specifying, 148-149
COLUMNS() function, 31
COMBIN() function, 184
COMBINA() function, 184
combo boxes
looking up values, 148
commas (,), arguments and, 72
comparison formulas, operators for, 6
complex criteria, filtering with, 366
entering compound criteria, 368-369
filtering with criteria range, 367-368
setting up criteria range, 366-367
compound computed criteria, 369
compound criteria, entering, 368-369
compound interest, 325
computed criteria, 366
CONCAT() function, 80
concatenating text with delimiter, 91
conditional formatting, 114-115
creating ledger shading, 197
conditions
applying across ranges, 116
operations on cells meeting, 117-118
confidence intervals, calculating, 261-262
confidence levels, 262
CONFIDENCE.NORM() function, 262
CONFIDENCE.T() function, 262
consolidating multisheet data, 34-35
constraint cells in Solver, 422
constraints
non-negative constraints in Solver, 428
continuous compounding, 329
continuous random variables, 246
contribution margin, 211
controls. See dialog box controls
converting
between nominal/effective interest rates, 327, 335
dates with text functions, 95
degrees/radians, 185
formulas
to text, 49
range names to cell references, 52
strings
to dates, 158
to times, 174
text, 84
with LOWER() function, 84
with NUMBERVALUE() function, 85
with PROPER() function, 84
to sentence case, 95
with UPPER() function, 84
with absolute references, 13
with mixed references, 14
with relative references, 12-14
CORREL() function, 251-252, 270-271
correlation, calculating, 251-252
correspondences in array formulas, 28
COS() function, 186
COSH() function, 186
cost of goods sold (COGS), 215
cost price, 209
costs, types of, 211
COT() function, 186
COTH() function, 186
COUNT() function, 230
COUNTA() function, 231
COUNTBLANK() function, 131, 231
COUNTIFS() function, 232
counting
characters with SUBSTITUTE() function, 102
errors in ranges, 132
items, 230
with COUNT() function, 230
with COUNTA() function, 231
with COUNTBLANK() function, 231
with COUNTIF() function, 232
with COUNTIFS() function, 232
non-numeric values in ranges, 132
occurrences in ranges, 119-120
covariance, calculating, 250-251
COVARIANCE.P() function, 250-251
COVARIANCE.S() function, 250-251
creating
arrays, 25
custom functions, 70
custom PivotTable calculations, 391
restrictions on, 392
criteria range
CSC() function, 186
CSCH() function, 186
cumulative principal and interest, calculating, 311-312
cumulative totals, calculating, 194-195
current assets, 224
sales to, 214
current date, returning, 157
current liabilities, 224
current ratio, 226
current time, returning, 173
custom functions, creating, 70
customizing PivotTable subtotal calculations, 382-383
custom PivotTable calculations, creating, 391
restrictions on, 392
D
data analysis tools. See Goal Seek; scenarios; what-if analysis
data bars, 94
data tables
editing, 405
data types, referencing, 51, 54
DATE() function, 95, 156, 158, 161
returning birthday, 164
returning future dates, 161
returning last day of month, 164
date functions
DATEVALUE(), 158
DAY(), 159
DAYS(), 170
EDATE(), 163
EOMONTH(), 164
ISOWEEKNUM(), 160
MONTH(), 159
NETWORKDAYS.INTL(), 171
TODAY(), 157
WEEKNUM(), 160
WORKDAY(), 161
YEAR(), 159
YEARFRAC(), 172
dates
adding months to, 163
with EDATE() function, 163
with EOMONTH() function, 164
calculating difference between, 169-170
with DAYS() function, 170
with DAYS360() function, 171-172
with NETWORKDAYS() function, 170-171
with YEARFRAC() function, 172
calculating
Easter Sunday, 189
Julian dates, 169
converting
with text functions, 95
strings to, 158
returning, 157
with DATE() function, 158
with DATEVALUE() function, 158
future dates, 161
with TODAY() function, 157
returning parts of, 159
with DAY() function, 159
with ISOWEEKNUM() function, 160
with MONTH() function, 159
with WEEKDAY() function, 160
with WEEKNUM() function, 160
with WORKDAY() function, 161
with WORKDAY.INTL() function, 162-163
with YEAR() function, 159
DATEVALUE() function, 156, 158
day of week, determining, 137
DAYS360() function, 156, 171-172
days purchases in accounts payable, 223-224
DCOUNT() function, 376
DCOUNTA() function, 376
DECIMAL() function, 184
degree symbol, inserting, 89
degrees, converting to/from radians, 185
deleting. See removing
delimiters, concatenating text with, 91
dependents
defined, 62
tracing, 63
dependent workbooks, 21
deposits, calculating
future value, 329
descriptive statistics, 229-230
averages, 232
with AVERAGE() function, 233
with AVERAGEA() function, 233
with AVERAGEIF() function, 233
with AVERAGEIFS() function, 234
with MEDIAN() function, 234
with MODE() function, 234
with SUMPRODUCT() function, 235-236
counting items, 230
with COUNT() function, 230
with COUNTA() function, 231
with COUNTBLANK() function, 231
with COUNTIF() function, 232
with COUNTIFS() function, 232
extreme values, 236
with LARGE() and SMALL() functions, 237-238
with MAX() and MIN() functions, 236
frequency distributions, 242-244
percentile, 239
range, 240
rank, 239
deseasoning data, 286
destination ranges, 35
Developer tab, displaying, 42
dialog box controls
check boxes, 46
group boxes, 44
inserting, 43
linking to cells, 43
option buttons, 45
difference between dates, calculating, 169-170
with DAYS() function, 170
with DAYS360() function, 171-172
with NETWORKDAYS() function, 170-171
with YEARFRAC() function, 172
differences between times, calculating, 176-177
with MOD() function, 195
difference summary calculations, 383-385
direct costs, 215
direct relationships, 250
disabling data table recalculation, 404
discontinuities, 429
discount amount, 210
discounted payback period, calculating, 351
discount factor, 340
discount formulas. See also cash-flow analysis
discounting cash flows, 344-345
internal rate of return, 352
with XIRR() function, 353
payback period, 349
discounted, 351
relationship to present value/future value/interest earned, 339
discount functions
XIRR(), 353
discounts (on price), 209
discount amount, 210
list price, 211
discrete probability distributions
binomial, 255
hypergeometric, 256
discrete random variables, 246
displaying
Developer tab, 42
formulas, 15
last updated date, 89
#DIV/0! error value, 50
dividing by zero errors, 50
avoiding, 109
DMAX() function, 376
DMIN() function, 376
dollar sign ($), in cell references, 13
dot follower effect, 92
DPRODUCT() function, 376
drop-down lists, in-cell lists and exact-match lookups, 145-146
DSTDEV() function, 376
DSTDEVP() function, 376
due dates, avoiding weekends, 122-123
DVAR() function, 376
DVARP() function, 376
dynamic amortization schedules, building, 313-315
dynamic array functions, list of, 32
E
Easter Sunday, calculating, 168, 189
editing
constraints, 427
data tables, 405
formulas, 5
Edit mode, 5
converting to/from nominal interest, 327, 335
employee time sheets, building, 177-181
employees, sales per, 215
empty cells, counting, 231
engines in Solver, 429
entering
functions into formulas, 73-74
table functions, 376
Enter mode, 4
error actions, choosing, 58-59
error cells
defined, 62
tracing, 64
error checker (for formulas), 58
error indicator (green triangle), 58
error messages for parentheses in formulas, 9
ERROR.TYPE() function, 125, 128-129
error values, 50
#CALC!, 50
#DIV/0!, 50
#FIELD!, 51
#NULL!, 53
#NUM!, 53
#SPILL!, 54
#UNKNOWN!, 54
#VALUE!, 54
errors. See also troubleshooting formulas
counting in ranges, 132
preventing with rounding, 190-191
even rows, adding, 196
EXACT() function, 80
exact undiscounted payback point, calculating, 350-351
with GROWTH() function, 291-292
with LOGEST() function, 292-293
exponentiation operator, 5
extending forecasts
with fill handle, 276
external references, 22
extracting
random samples, 249
substrings, 94
with LEFT() function, 94
with MID() function, 94
with RIGHT() function, 94
extreme values, calculating, 236
with LARGE() and SMALL() functions, 237-238
with MAX() and MIN() functions, 236
F
FACT() function, 184
FACTDOUBLE() function, 184
FALSE results, handling in IF() function, 108
FALSE() function, 106
feasible solutions, 431
#FIELD! error value, 51
field names, 361
field values, 361
fields
defined, 361
hiding, 362
partial fields, sorting on, 362-363
FIELDVALUE() function, 136
fill handle, extending forecasts, 276
with complex criteria, 366-369
with computed criteria, 369-370
with FILTER() function, 370
with UNIQUE() function, 371
financial formulas, 213. See also business formulas; discount formulas; investment formulas; loan formulas
compound interest, 325
continuous compounding, 329
converting between nominal/effective interest rates, 327, 335
cost of goods sold, 215
fixed-asset ratios, 217
fixed assets to short-term debt, 218
fixed-asset turnover, 217
return on fixed assets, 217-218
gross margin, 216
net margin, 216
sales ratios, 214
sales per employee, 215
sales to current assets, 214
sales to short-term debt, 214
simple interest, 325
finding
row numbers of list values, 120-121
fiscal year, determining month of, 138-139
FISHER() function, 229
fixed assets, 217
fixed assets to short-term debt, 218
fixed-asset ratios, 217
fixed assets to short-term debt, 218
fixed-asset turnover, 217
return on fixed assets, 217-218
fixed-asset turnover, 217
fixed costs, 211
fixed-rate amortization schedules, building, 312-313
FLOOR.MATH() function, 184, 188-189
FORECAST() function, 279
forecasts, 274. See also exponential trends; logarithmic trends; power trends; regression analysis
extending
with fill handle, 276
plotting, 275
regression equation for, 277-278
seasonal sales model case study, 280-288
with FORECAST() function, 279
with LINEST() function, 279
with TREND() function, 278
Forecast Sheet, 267
formatting
conditional formatting, 114-115
creating ledger shading, 197
numbers, restoring formatting, 388
text, 85
with DOLLAR() function, 86
with FIXED() function, 87
form controls
check boxes, 46
group boxes, 44
inserting, 43
linking to cells, 43
option buttons, 45
formulas
adding to data tables, 402-403
arithmetic, operators for, 5-6
array
correspondences in, 28
defined, 25
on multiple ranges, 29
comparison, operators for, 6
computed criteria, entering, 369-370
conditional formatting with, 114-115
converting
to text, 49
with absolute references, 13
with mixed references, 14
with relative references, 12-14
discount
calculating internal rate of return, 352-354
calculating payback period, 349-351
calculating present value, 339-344
discounting cash flows, 344-349
relationship to present value/future value/interest earned, 339
displaying, 15
editing, 5
financial, 213
compound interest, 325
continuous compounding, 329
converting between nominal/effective interest rates, 327, 335
cost of goods sold, 215
gross margin, 216
net margin, 216
simple interest, 325
inventory, 218
investment
building investment schedule, 335-337
calculating future value, 328-330
limits on, 4
line breaks in, 3
links in, 21
changing source, 23
external references, 22
liquidity, 221
accounts payable ratios, 223-224
accounts receivable ratios, 222-223
loan
allowing principal paydowns, 321-323
building loan amortization schedules, 312-315, 320-321
cumulative principal and interest, 311-312
principal and interest, 309-310
naming, 20
nesting, limits on, 4
PivotTable results in, 395-397
pricing, 207
range names in, 16
recalculation, controlling, 9-10
reference, operators for, 6
referencing tables in, 371
entering table formulas, 373-374
structure of, 3
in table records, sorting, 362
text, operators for, 6
troubleshooting. See troubleshooting formulas
fractional powers, raising numbers to, 9
frequency distributions, calculating, 242-244
FREQUENCY() function, 243-244, 253
front loading interest costs, 309
functions
ADDRESS(), 122
in Analysis TookPak, 76
COLUMNS(), 31
creating custom, 70
date functions
DATEVALUE(), 158
DAY(), 159
DAYS(), 170
EDATE(), 163
EOMONTH(), 164
ISOWEEKNUM(), 160
MONTH(), 159
NETWORKDAYS.INTL(), 171
TODAY(), 157
WEEKDAY(), 122, 137, 160, 165-167
WEEKNUM(), 160
YEAR(), 159
YEARFRAC(), 172
defined, 70
discount functions
XIRR(), 353
information functions
list of, 125
SHEET(), 130
SHEETS(), 130
Insert Function feature, 74-76
investment functions
FVSCHEDULE(), 334
PV(), 333
loan functions
FV(), 317
INDIRECT(), 310
logical functions
list of, 106
XOR(), 114
lookup functions, 135
list of, 136
LOOKUP(), 31
math functions
EVEN(), 189
ODD(), 189
ROUNDDOWN(), 188
ROUNDUP(), 188
SUMIF(), 193
TRUNC(), 190
MDETERM(), 31
MINVERSE(), 31
MMULT(), 31
nesting, 73
regression analysis
FORECAST(), 279
INTERCEPT(), 273
LINEST(), 31, 271-273, 279, 295, 302, 304
RSQ(), 273
SLOPE(), 273
TREND(), 32, 270-271, 278, 301-304
ROWS(), 31
SINGLE(), 32
statistical functions
AVERAGE(), 233
AVERAGEA(), 233
AVERAGEIF(), 233
AVERAGEIFS(), 234
BINOM.DIST(), 255
CONFIDENCE.NORM(), 262
CONFIDENCE.T(), 262
COUNT(), 230
COUNTA(), 231
COUNTIFS(), 232
FISHER(), 229
HYPGEOM.DIST(), 256
KURT(), 261
MAXA(), 237
MEDIAN(), 234
MINA(), 237
MODE(), 234
MODE.MULT(), 235
MODE.SNGL(), 235
NORM.DIST(), 259
PEARSON(), 229
PERCENTILE.EXC(), 239
PERCENTILE.INC(), 239
PHI(), 229
POISSON.DIST(), 256
PROB(), 254
RANK.AVG(), 239
RANK.EQ(), 239
SKEW(), 260
STDEVA(), 242
STDEV.P(), 242
STDEVPA(), 242
STDEV.S(), 242
T.TEST(), 264
VARA(), 241
VAR.P(), 241
VARPA(), 241
VAR.S(), 241
Z.TEST(), 263
summary functions
changing, 383
difference calculations, 383-385
list of, 383
percentage calculations, 385-387
running total calculations, 388-389
table functions
advantages of, 375
analyzing table of defects, 378-379
DAVERAGE(), 377
DSUM(), 375
entering, 376
list of, 376
TABLE(), 405
text functions
CODE(), 83
DOLLAR(), 86
FIXED(), 87
LEFT(), 94
LEN(), 90
LOWER(), 84
MID(), 94
NUMBERVALUE(), 85
PROPER(), 84
RIGHT(), 94
TEXTJOIN(), 91
UNICHAR(), 81
UPPER(), 84
time functions
HOUR(), 174
list of, 173
MINUTE(), 175
SECOND(), 175
TIMEVALUE(), 174
TRANSPOSE(), 32
trigonometric functions, list of, 185-186
types of, 69
future dates, returning, 161
future times, returning, 175-176
future value, 306
for investment goals, 334
relationship to discounting, 339
FV() function, 71-72, 317, 328-330
FVSCHEDULE() function, 334
G
GCD() function, 184
generating
random letters, 200
random numbers, 198
with RANDARRAY() function, 202
with RANDBETWEEN() function, 201
with SEQUENCE() function, 202-203
Solver reports, 437
Limits report, 441
summary reports for scenarios, 417-419
GETPIVOTDATA() function, 136, 391, 395-397
Goal Seek, 405
iterative methods, 406
optimizing product margin, 408-409
pausing, 407
resuming, 407
solving algebraic equations, 411-412
worksheet setup, 406
Good Friday, calculating, 168
grand totals
of calculated fields, 394
in PivotTables, 381
hiding, 382
GRG Nonlinear engine, 429, 431-432
gross margin, 216
group boxes, inserting, 44
GROWTH() function, 31, 291-292
H
hiding
fields, 362
PivotTable grand totals, 382
PivotTable subtotals, 382
histograms, 93
HLOOKUP() function, 31, 136, 141-142
holidays, calculating, 167-168, 189
hurdle rate, 346
HYPGEOM.DIST() function, 256
hypergeometric distributions, 256
I
aging overdue invoices, 123-124
applying conditions across ranges, 116
avoiding
#DIV/0 errors, 50
division by zero, 109
combining with arrays, 116-121
conditional formatting with, 114-115
counting occurrences in ranges, 119-120
finding row numbers in lists, 120-121
handling FALSE results, 108
multiple logical tests with, 109-115
operations on cells that meet condition, 117-118
IFERROR() function, 57-58, 106
ignoring errors in ranges, 132-133
in-cell drop-down lists, exact-match lookups and, 145-146
income investing, rental properties versus, 342-343
incorrect formula results, troubleshooting, 56
independent variables, 246, 265
index summary calculations, 389-391
INDEX() function, 31, 47, 136, 147
list boxes, looking up values, 148
lookup column, specifying, 148-149
multiple-column lookups, 150-151
row-and-column lookups, 149-150
INDIRECT() function, 310
inferential statistics, 245-246
percentiles, 259
probability distributions, 252
binomial, 255
hypergeometric, 256
random samples, 249
INFO() function, 70, 125, 129-130
information functions
list of, 125
SHEET(), 130
SHEETS(), 130
initial deposit, calculating for investment goals, 333
Insert Function feature, 74-76
inserting
degree symbol, 89
dialog box controls, 43
check boxes, 46
group boxes, 44
option buttons, 45
functions with Insert Function feature, 74-76
integrating CHOOSE() function with option buttons, 139-140
intelligent, defined, 105
INTERCEPT() function, 273
interest costs
front loading, 309
interest earned, relationship to discounting, 339
interest rates
compound interest, 325
converting between nominal/effective rates, 327, 335
simple interest, 325
internal rate of return, calculating, 352
with XIRR() function, 353
inventory, 218
inventory formulas, 218
inventory management, 220
inventory reorder level, 221
inventory safety stock, 220-221
inventory ratios, 218
inventory turnover, 219
inventory turnover rate, 219
sales to inventory, 220
inventory management formulas, 220
inventory reorder level, 221
inventory safety stock, 220-221
inventory ratios, 218
inventory turnover, 219
inventory turnover rate, 219
sales to inventory, 220
inventory reorder level, 221
inventory safety stock, 220-221
inventory turnover, 219
inventory turnover rate, 219
inverse relationships, 250
investment formulas. See also discount formulas
building investment schedule, 335-337
calculating future value, 328-330
investment goals, 330
future value with varying interest rates, 334
initial deposit, 333
investment functions
FVSCHEDULE(), 334
PV(), 333
investment goals, 330
future value with varying interest rates, 334
initial deposit, 333
investment schedules, building, 335-337
ISBLANK() function, 125, 131-132
ISERR() function, 125
ISERROR() function, 125, 132-133
ISEVEN() function, 125
ISFORMULA() function, 125
ISLOGICAL() function, 125
ISNA() function, 125
ISNONTEXT() function, 125
ISODD() function, 125
ISOWEEKNUM() function, 156, 160
ISREF() function, 125
ISTEXT() function, 125
ISxxx() functions, 130, 132-133
iterative methods
in Goal Seek, 406
in Solver, 422
J–K–L
Julian dates, calculating, 169
KURT() function, 261
kurtosis, 261
last day of month, returning
with DATE() function, 164
with EOMONTH() function, 164
last updated date, displaying, 89
LCM() function, 184
leasing equipment, buying versus, 343-344
ledger shading, creating, 197-198
letters, generating
random, 200
limits on formulas, 4
Limits report (Solver), 441
linear data, simple regression on, 266
seasonal sales model case study, 280-288
linear models, 429
linear programming, 421
line breaks in formulas, 3
line feeds, removing
with CLEAN() function, 90
with SUBSTITUTE() function, 103
line of best fit, 266
analyzing sales versus advertising, 274
calculating
with LINEST() function, 271-273
with TREND() function, 270-271
regression equation for, 268-269
LINEST() function, 31, 271-273, 279, 295, 302, 304
linked data types, referencing, 51
linking
dialog box controls to cells, 43
workbooks, 21
links in formulas, 21
changing source, 23
external references, 22
liquid assets, 221
liquidity, 221
liquidity formulas, 221
accounts payable ratios, 223
accounts payable turnover, 223
days purchases in accounts payable, 223-224
sales to accounts payable, 224
accounts receivable ratios, 222
accounts receivable turnover, 222
liquidity ratios, 226
cash ratio, 227
current ratio, 226
liquidity ratios, 226
cash ratio, 227
current ratio, 226
list boxes
looking up values, 148
list values, looking up, 118-119
lists, finding row numbers of values, 120-121
LN() function, 184, 293-295, 298
loading
Solver, 423
Solver models, 433
loan amortization schedules, building, 312-315, 320-321
loan formulas
allowing principal paydowns, 321-323
building loan amortization schedules, 312-315, 320-321
cumulative principal and interest, 311-312
principal and interest, 309-310
loan functions
FV(), 317
INDIRECT(), 310
loan payments, calculating, 306-312
LOG() function, 184
LOG10() function, 184
LOGEST() function, 31, 292-293
logical functions
list of, 106
XOR(), 114
lookup column, specifying, 148-149
lookup functions, 135
list of, 136
lump sum future value, calculating, 328
M
manual recalculation, 10
manufacturer’s suggested retail price (MSRP), 209
manufacturing contribution margin, 212
markup amount, 208
markups, 207
cost price, 209
markup amount, 208
selling price, 208
MATCH() function, 31, 136, 146-147
list boxes, looking up values, 148
lookup column, specifying, 148-149
multiple-column lookups, 150-151
row-and-column lookups, 149-150
math functions
EVEN(), 189
ODD(), 189
RANDARRAY(), 202
RANDBETWEEN(), 201
ROUNDDOWN(), 188
ROUNDUP(), 188
SUMIF(), 193
TRUNC(), 190
MAX() function, 115, 121, 236, 240
MAXA() function, 237
measures of association, 249-250
measures of central tendency, 240
measures of dispersion, 240
measures of location, 240
measures of variation, 240
median, calculating, 234
MEDIAN() function, 234
Memorial Day, calculating, 168
merging scenarios, 417
MIN() function, 115, 121, 236, 240
MINA() function, 237
missing/mismatched parentheses, troubleshooting, 55
mixed reference format, 14
adding every nth row, 196
calculating time differences, 195
creating ledger shading, 197-198
mode, calculating, 234
MODE() function, 234
MODE.MULT() function, 235
MODE.SNGL() function, 235
models (Solver), saving, 433-434
month of fiscal year, determining, 138-139
months
adding to dates, 163
with EDATE() function, 163
with EOMONTH() function, 164
returning last day
with DATE() function, 164
with EOMONTH() function, 164
returning nth occurrence of weekday, 165-167
mortgages
principal paydowns, allowing, 321-323
variable-rate amortization schedules, building, 320-321
with absolute references, 13
with mixed references, 14
with relative references, 12-14
MROUND() function, 184, 187-188, 191-192
MSRP (manufacturer’s suggested retail price), 209
MULTINOMIAL() function, 184
multiple-column lookups, 150-151
multiple internal rates of return, calculating, 353-354
multiple logical tests, performing, 109-115
multiple ranges, array formulas on, 29
multiple regression, 266, 303-304
multisheet data, consolidating, 34-35
multithreaded calculation, 10
MUNIT() function, 184
N
N() function, 125
NA() function, 125
named ranges. See range names
names
of functions, troubleshooting, 51-52
naming formulas, 20
negative values, adding in range, 193
nesting
formulas, limits on, 4
functions, 73
parentheses, 9
SUBSTITUTE() functions, 102
net margin, 216
net present value, calculating, 345-346
NETWORKDAYS() function, 124, 157, 170-171
NETWORKDAYS.INTL() function, 157, 171
converting to/from effective interest, 327, 335
nonlinear data, 266
simple regression on, 288
nonlinear programming, 421
non-negative constraints in Solver, 428
non-numeric values, counting in ranges, 132
nonperiodic cash flows
calculating internal rate of return, 353
net present value with, 348-349
percentiles, 259
NORM.DIST() function, 259
NOT() function, 106
NPER() function, 315-316, 331-332
#NULL! error value, 53
null hypothesis, 263
number formatting, restoring, 388
numbers, formatting as text. See text, formatting
NUMBERVALUE() function, 80, 85
numeric formatting symbols, 87
O
objective cells in Solver, 422
odd rows, adding, 196
extracting periodic samples, 247-248
extracting random samples, 249
one-input data tables, setup, 400-402
operations research, 421
operators
comparison, 6
for constraints, 427
reference, 6
text, 6
optimizing product margin, 408-409
option buttons
inserting, 45
integrating CHOOSE() function with, 139-140
options for error checker, setting, 59-62
looking up list values, 118-119
overdue invoices, aging, 123-124
P
padding cells, 92
parentheses ()
missing/mismatched, troubleshooting, 55
partial fields, sorting on, 362-363
parts of dates, returning, 159
with DAY() function, 159
with ISOWEEKNUM() function, 160
with MONTH() function, 159
with WEEKDAY() function, 160
with WEEKNUM() function, 160
with WORKDAY() function, 161
with WORKDAY.INTL() function, 162-163
with YEAR() function, 159
parts of times, returning, 174
with HOUR() function, 174
with MINUTE() function, 175
with SECOND() function, 175
pasting range names into formulas, 16-17
pausing Goal Seek, 407
payables. See accounts payable ratios
payback period, calculating, 349
discounted, 351
payments
defined, 306
loan payments, calculating, 306-312
mortgage payments, allowing principal paydowns, 321-323
PEARSON() function, 229
percentage summary calculations, 385-387
percentile
defined, 238
PERCENTILE.EXC() function, 239
PERCENTILE.INC() function, 239
periodic samples
defined, 246
periods
calculating for investment goals, 331-332
defined, 306
per-unit constants, 355
PHI() function, 229
PI() function, 184
PivotTables, 381
creating custom calculations, 391
restrictions on, 392
grand totals, 381
hiding, 382
resetting to normal, 389
customizing calculation, 382-383
hiding, 382
value field summary calculations, 383
difference calculations, 383-385
percentage calculations, 385-387
running total calculations, 388-389
in worksheet formulas, 395-397
plotting
forecasts, 275
polynomial regression, 300-301
PMT() function, 70, 307-308, 332-333
Point mode, 4
Poisson distributions, 256-257
POISSON.DIST() function, 256
polynomial regression, 266, 299-300
position, consolidating by, 35-38
positive values, adding in range, 193
POWER() function, 184
PP&E (property, plant, and equipment), 217
precedence. See order of precedence
precedents
defined, 62
tracing, 63
present value, 306
relationship to discounting, 339
price points, setting, 191
pricing formulas, 207
discounts, 209
discount amount, 210
list price, 211
markups, 207
cost price, 209
markup amount, 208
selling price, 208
principal and interest, calculating, 309-310
principal paydowns, allowing, 321-323
probability distributions, 252
binomial, 255
hypergeometric, 256
PROB() function, 254
product margin, optimizing, 408-409
PRODUCT() function, 184
projections. See forecasts
property, plant, and equipment (PP&E), 217
p-value, 263
PV() function, 318-319, 333, 341-344
Q–R
Quick Access Toolbar, adding scenarios to, 416
QUOTIENT() function, 184
R2 (in regression analysis), 269-270
radians, converting to/from degrees, 185
RANDARRAY() function, 32, 184, 202
RANDBETWEEN() function, 184, 201, 249
random letters, generating, 200
random numbers, generating, 198
with RANDARRAY() function, 202
with RANDBETWEEN() function, 201
with SEQUENCE() function, 202-203
random samples
defined, 246
extracting, 249
random variables, 246
range, calculating, 240
range names
cell references in, 13
converting to cell references, 52
in formulas, 16
ranges. See also array formulas; arrays
adding only positive/negative values, 193
applying conditions across, 116
with absolute references, 13
with mixed references, 14
with relative references, 12-14
counting
cells in, 232
errors in, 132
non-numeric values, 132
multiple ranges, array formulas on, 29
recalculation, 10
rank
calculating, 239
defined, 238
RANK.AVG() function, 239
RANK.EQ() function, 239
RATE() function, 317-318, 330-331
recalculating data tables, 404
recalculation of formulas, controlling, 9-10
receivables. See accounts receivable ratios
records, defined, 361
reference formulas, operators for, 6
references. See cell references
referencing. See also cell references
data types, troubleshooting, 54
linked data types, troubleshooting, 51
tables, 371
entering table formulas, 373-374
regression analysis, 265
methods of, 266
polynomial regression, 299-300
simple regression on linear data, 266
seasonal sales model case study, 280-288
simple regression on nonlinear data, 288
Regression tool in Analysis ToolPak, 273
relative reference format, 12-14
ignoring when applying range names, 18
removing
carriage returns with CLEAN() function, 90
characters from strings, 101-102
constraints, 427
extra string characters, 89
with CLEAN() function, 90
line feeds
with CLEAN() function, 90
with SUBSTITUTE() function, 103
scenarios, 419
tracer arrows, 64
Watch Windows, 66
rental properties, income investing versus, 342-343
reorder level, 221
repeating text with REPT() function, 92-93
REPLACE() function, 80, 100-101
reports (Solver), generating, 437
Limits report, 441
reseasoning data, 287
resetting PivotTables to normal, 389
resizing arrays, 27
restoring number formatting, 388
results of formulas, troubleshooting, 56
resuming Goal Seek, 407
retail price, 209
returning
birthday, 164
dates, 157
with DATE() function, 158
with DATEVALUE() function, 158
with TODAY() function, 157
future dates, 161
last day of month
with DATE() function, 164
with EOMONTH() function, 164
nth occurrence of weekday in month, 165-167
parts of dates, 159
with DAY() function, 159
with ISOWEEKNUM() function, 160
with MONTH() function, 159
with WEEKDAY() function, 160
with WEEKNUM() function, 160
with WORKDAY() function, 161
with WORKDAY.INTL() function, 162-163
with YEAR() function, 159
parts of times, 174
with HOUR() function, 174
with MINUTE() function, 175
with SECOND() function, 175
times, 173
with NOW() function, 173
with TIME() function, 174
with TIMEVALUE() function, 174
return on fixed assets, 217-218
Ribbon, displaying Developer tab, 42
risk premium, 306
ROMAN() function, 184
ROUND() function, 184, 186-187
ROUNDDOWN() function, 185, 188
rounding functions
EVEN(), 189
list of, 186
ODD(), 189
preventing calculation errors, 190-191
ROUNDDOWN(), 188
ROUNDUP(), 188
setting price points, 191
TRUNC(), 190
row-and-column lookups, 149-150
row names in range names, 18-20
row numbers, finding for list values, 120-121
rows
adding every nth row with MOD() function, 196
creating ledger shading, 197-198
ROWS() function, 31
RSQ() function, 273
RTD() function, 136
rule of addition, 253
rule of multiplication, 253
running total summary calculations, 388-389
S
saddle points, 257
sales
analyzing versus advertising, 274
book publishing example, 355-356
seasonal sales model case study, 280-288
sales mix, 212
sales per employee, 215
sales ratios, 214
sales per employee, 215
sales to current assets, 214
sales to short-term debt, 214
sales to accounts payable, 224
sales to current assets, 214
sales to inventory, 220
sales to short-term debt, 214
extracting periodic samples, 247-248
extracting random samples, 249
saving
Solver solutions as scenarios, 428
adding
to Quick Access Toolbar, 416
deleting, 419
merging, 417
saving Solver solutions as, 428
worksheet setup, 414
searching for substrings
with COLUMN() and CELL() functions, 99-100
with FIND() and SEARCH() functions, 97-99
seasonal indexes, 284
seasonal sales model case study, 280-288
seasonally adjusting data, 286
SEC() function, 186
SECH() function, 186
second-order polynomial regression equation, 299
selecting arrays, 27
selling price, 208
Sensitivity report (Solver), 439-440
sentence case, converting text to, 95
SEQUENCE() function, 32, 185, 202-203
serial numbers for dates/times, 153-154
Series command, extending forecasts, 276-277
series of deposits, calculating future value, 329
series of letters, generating, 82-83
SERIESSUM() function, 185
server workbooks, 21
shape of curve, calculating, 260-261
short-term debt
fixed assets to, 218
sales to, 214
SIGN() function, 185
significance levels, 262
simple interest, 325
simple regression, 266
on linear data, 266
seasonal sales model case study, 280-288
on nonlinear data, 288
simple undiscounted payback period, calculating, 349-350
Simplex LP engine, 429
SIN() function, 186
SINGLE() function, 32
single quotation marks (‘ ‘) in external references, 22
SINH() function, 186
SKEW() function, 260
slack (in Solver Answer report), 438
SLOPE() function, 273
smooth models, 429
Solver, 421
advantages of, 422
engines in, 429
generating reports, 437
Limits report, 441
loading, 423
non-negative constraints, 428
saving
solutions as scenarios, 428
solving transportation problem, 435-437
solving
transportation problem, 435-437
SORTBY() function, 32, 364-365
sorting
tables, 361
with formulas in, 362
with SORT() function, 364
with SORTBY() function, 364-365
source (of links), changing, 23
source ranges, 35
source workbooks, 21
specifiers. See table specifiers
#SPILL! error value, 28, 54, 202
spill ranges, troubleshooting, 54
spills, 27
spread, calculating, 240
SQRT() function, 185
SQRTPI() function, 185
standard deviation, calculating, 241-242
standard normal distribution, 257-258
standard scores, calculating, 258-259
statistical functions. See also table functions
AVERAGE(), 233
AVERAGEA(), 233
AVERAGEIF(), 233
AVERAGEIFS(), 234
BINOM.DIST(), 255
CONFIDENCE.NORM(), 262
CONFIDENCE.T(), 262
COUNT(), 230
COUNTA(), 231
COUNTBLANK(), 231
COUNTIFS(), 232
FISHER(), 229
HYPGEOM.DIST(), 256
KURT(), 261
MAXA(), 237
MEDIAN(), 234
MINA(), 237
MODE(), 234
MODE.MULT(), 235
MODE.SNGL(), 235
NORM.DIST(), 259
PEARSON(), 229
PERCENTILE.EXC(), 239
PERCENTILE.INC(), 239
PHI(), 229
POISSON.DIST(), 256
PROB(), 254
RANK.AVG(), 239
RANK.EQ(), 239
SKEW(), 260
STDEVA(), 242
STDEV.P(), 242
STDEVPA(), 242
STDEV.S(), 242
T.TEST(), 264
VARA(), 241
VAR.P(), 241
VARPA(), 241
VAR.S(), 241
Z.TEST(), 263
statistics
frequency distributions, 242-244
percentile, 239
range, 240
rank, 239
probability distributions, 252-257
status (in Solver Answer report), 438
STDEVA() function, 242
STDEVPA() function, 242
STDEV.P() function, 242
STDEV.S() function, 242
strings
concatenating with delimiter, 91
converting, 84
to dates, 158
with LOWER() function, 84
with NUMBERVALUE() function, 85
with PROPER() function, 84
to times, 174
with UPPER() function, 84
counting characters in, 102
defined, 79
formatting, 85
with DOLLAR() function, 86
with FIXED() function, 87
removing characters from, 101-102
removing extra characters, 89
with CLEAN() function, 90
repeating with REPT() function, 92-93
substrings
extracting, 94
structured referencing of tables, 371
entering table formulas, 373-374
subproblems, 431
SUBSTITUTE() function, 80, 101-103
substituting substrings
with REPLACE() function, 100-101
with SUBSTITUTE() function, 101-102
substrings
extracting, 94
with LEFT() function, 94
with MID() function, 94
with RIGHT() function, 94
searching for
with COLUMN() and CELL() functions, 99-100
with FIND() and SEARCH() functions, 97-99
substituting
with REPLACE() function, 100-101
with SUBSTITUTE() function, 101-102
SUBTOTAL() function, 185
subtotals in PivotTables, 381-382
customizing calculation, 382-383
hiding, 382
SUM() function, 70, 185, 192, 194-195
counting occurrences in ranges, 119-120
operations on cells that meet condition, 117-118
SUMIFS() function, 185, 193-194
summary functions
changing, 383
difference calculations, 383-385
list of, 383
percentage calculations, 385-387
running total calculations, 388-389
summary reports, generating for scenarios, 417-419
summary statistics. See descriptive statistics
summing. See adding
SUMPRODUCT() function, 31, 185, 235-236
SUMSQ() function, 185
SUMX2MY2() function, 185
SUMX2PY2() function, 185
SUMXMY2() function, 185
syntax (of functions), 71
T
T() function, 80
T.TEST() function, 264
table formulas, entering, 373-374
table functions
advantages of, 375
analyzing table of defects, 378-379
DAVERAGE(), 377
DSUM(), 375
entering, 376
FILTER(), 370
list of, 376
SORT(), 364
UNIQUE(), 371
table of defects, analyzing, 378-379
table ranges, 361
TABLE() function, 405
tables. See also data tables; PivotTables
defined, 361
with complex criteria, 366-369
with computed criteria, 369-370
with FILTER() function, 370
with UNIQUE() function, 371
sorting, 361
with formulas in, 362
with SORT() function, 364
with SORTBY() function, 364-365
structured referencing, 371
entering table formulas, 373-374
TAN() function, 186
TANH() function, 186
target cells in Solver, 422
term of loan, calculating, 315-317
text
CODE() function, 83
concatenating with delimiter, 91
converting, 84
formulas to, 49
with LOWER() function, 84
with NUMBERVALUE() function, 85
with PROPER() function, 84
to sentence case, 95
with UPPER() function, 84
defined, 79
formatting, 85
with DOLLAR() function, 86
with FIXED() function, 87
repeating with REPT() function, 92-93
text formulas, operators for, 6
text functions
CODE(), 83
DOLLAR(), 86
FIXED(), 87
LEFT(), 94
LEN(), 90
LOWER(), 84
MID(), 94
NUMBERVALUE(), 85
PROPER(), 84
RIGHT(), 94
TEXTJOIN(), 91
UNICHAR(), 81
UPPER(), 84
text values. See strings
text-based charts, building, 92-93
third-order polynomial regression equation, 300
threads, 10
tiered payments, calculating, 110
time factor, 314
time formatting symbols, 88
time functions
HOUR(), 174
list of, 173
MINUTE(), 175
NOW(), 173
SECOND(), 175
TIMEVALUE(), 174
time sheets, building, 177-181
returning future times, 175-176
times
adding, 176
calculating differences between, 176-177
with MOD() function, 195
converting strings to, 174
returning, 173
with NOW() function, 173
with TIME() function, 174
with TIMEVALUE() function, 174
returning parts of, 174
with HOUR() function, 174
with MINUTE() function, 175
with SECOND() function, 175
TODAY() function, 157
toggling difference calculations, 385
totals, calculating cumulative, 194-195
circular references, troubleshooting, 57
removing, 64
tracing
dependents, 63
error cells, 64
precedents, 63
transportation problem, solving, 435-437
TRANSPOSE() function, 32
TREND() function, 32, 270-271, 278, 301-304
trendlines, 266
analyzing sales versus advertising, 274
calculating
with LINEST() function, 271-273
with TREND() function, 270-271
with GROWTH() function, 291-292
with LOGEST() function, 292-293
for forecasts, 274
extending with fill handle, 276
extending with Series command, 276-277
with FORECAST() function, 279
with LINEST() function, 279
plotting, 275
regression equation for, 277-278
seasonal sales model case study, 280-288
with TREND() function, 278
polynomial regression, 299-300
regression equation for, 268-269
trigonometric functions, list of, 185-186
troubleshooting formulas, 49
dependent tracing, 63
error cell tracing, 64
precedent tracing, 63
removing tracer arrows, 64
converting to text, 49
error checker, 58
error values, 50
#CALC!, 50
#DIV/0!, 50
#FIELD!, 51
#NULL!, 53
#NUM!, 53
#SPILL!, 54
#UNKNOWN!, 54
#VALUE!, 54
incorrect results, 56
missing/mismatched parentheses, 9, 55
TRUE() function, 106
two-input data tables, setup, 403-405
TYPE() function, 125
typing functions into formulas, 73-74
U
undiscounted payback period, calculating, 349-351
UNICODE() function, 80
#UNKNOWN! error value, 54
unknowns in Solver, 422
V
value field summary calculations in PivotTables, 383
difference calculations, 383-385
percentage calculations, 385-387
running total calculations, 388-389
VALUE() function, 80
values
adding, 192
with SUM() function, 192, 194-195
with SUMIF() function, 193
with SUMIFS() function, 193-194
VARA() function, 241
variable cells in Solver, 422
variable costs, 211
variable interest rates, calculating future value for investment goals, 334
variable-rate mortgage amortization schedules, building, 320-321
variables
defined, 246
measures of association, 249-250
types of, 246
variance, calculating, 240-241
VARPA() function, 241
VAR.P() function, 241
VAR.S() function, 241
varying cash flows, net present value with, 347-348
viewing
current input mode, 5
formulas, 15
VLOOKUP() function, 32, 136, 140-141
W
WEEKDAY() function, 122, 137, 157, 160, 165-167
weekdays, returning nth occurrence in month, 165-167
weekend due dates, avoiding, 122-123
weighted averages, 390
weighted mean, calculating, 235-236
weighted questionnaire results, calculating, 139
data tables
editing, 405
wildcard characters in MATCH() function, 147
workbooks
displaying last updated date, 89
linking, 21
WORKDAY() function, 123, 157, 161
WORKDAY.INTL() function, 162-163
WORKDAY.INTO() function, 157
working capital ratio, 226
working capital turnover, 225
worksheets
account receivable aging worksheet, building, 122-124
dependent tracing, 63
error cell tracing, 64
precedent tracing, 63
removing tracer arrows, 64
dialog box controls
check boxes, 46
group boxes, 44
inserting, 43
linking to cells, 43
option buttons, 45
time sheets, building, 177-181
X
XIRR() function, 353
x-values, 265
Y
years
y-values, 265
Z
Z-scores, calculating, 258-259
Z.TEST() function, 263