call options
defined, 513
parameters, changing, 518–519
parameters determining value, 515
capital budgeting with Excel Solver, 241–243
card games, modeling, 501–505
cash flow, comparing in today's dollars. See net present value (NPV)
cells
blank, counting. See COUNTBLANK function
comments in. See comments
converting to columns, 35–36
copying, visible only, 343
counting number of meeting certain criterion. See COUNTIF function
dependents. See dependents
formatting, conditional. See conditional formatting
freezing, 77
highlighting, conditionally, 164
linking, 99
naming, 2.
See also named ranges
precedents. See precedents
references, changing independently from formulas. See INDIRECT function
shading, to indicate value differences. See color scales
sorting by color, 188–189
sorting by icon, 189
text in, dividing into multiple columns, 32–35
unlocking, 520
Central Limit Theorem, 468–469
Change Constraint dialog box, 228
CHAR function, 32
charts
modifying, 368
updating automatically, 153–155
check boxes
conditional formatting, 212
creating, 211
defined, 210
circular references
defined, 69
example of, 69–70
and pro forma statements, 83
resolving, 69–71
cities, calculating distances between, 19–20
cohort, 526
color scales, 164–165, 170–172.
See also conditional formatting
colors
filtering by, 348
sorting by, 188–189
column index, 11
columns
clearing filters, 343
converting into rows, 98, 574
freezing, 77
hiding, 14, 114–115
last number in, returning, 151–152
named ranges for, 7
referencing entire. See INDEX function
selecting all data in, 100
summing all entries in, 140
unhiding, 115
combo boxes, 213–214
comments
editing, 507
hiding, 507
inserting, 507
printing, 507
computed criteria, 335
CONCATENATE function, 31, 33
conditional formatting
with check boxes, 212
with color scales, 164–165, 170–172
custom format for, creating, 166
with data bars, 164–165, 169–170
for dates, 179–180
defined, 163
deleting, 165, 176
formulas and, 177–179
highlighting cells with, 166–167
with icon sets, 164–165, 172–173
options, 164–165
in PivotTables, 313–314
for ranges, 178
rules, editing, 176
rules, managing, 165, 167–168
selecting all cells with, 176
Stop If True option, 182
in tables, 203
Top/Bottom rules, 165–166
confidence interval, 482
Consolidate command, 355
Consolidate dialog box, 356
consolidating data, 355–358
constraints
binary changing cells as, 241
for capital budgeting models, 243–244
in Excel Solver, adding, 224–225
in Excel Solver, changing, 228–229
continuous random variables
Beta. See Beta random variable
defined, 447
modeling as random variables, 486
probability density function of, 447–448, 463–464
Weibull. See Weibull random variable
controls, form. See form controls
copying
data, without formulas, 97–98
form controls, 208
formulas, 78
formulas, with IF formulas, 85
visible cells only, 343
CORREL function, 386
Correlation dialog box, 384
correlation matrix, 386
correlations
defined, 383
negative, 384
positive, 383
R2 value and, 387
for stock prices, 384–386
weak, 384
cost as function of order quantity, 74–75
COUNT function, 132
example of, 135
COUNTA function, 132, 136
COUNTBLANK function, 132, 136
COUNTIF function
for bootstrapping, 494
dates and, 135
defined, 132
example of, 133
finding results not equal to criteria, 134
syntax, 132
wildcard characters in, 134–135
COUNTIFS function
defined, 132
examples of, 135
syntax, 132
craps games, modeling, 79, 501–503
Create PivotTable dialog box, 299
credit-card customers, estimating profitability of, 525–527
CRITBINOM function, 485
criteria ranges, 336
CUMIPMT function, 63
CUMPRINC function, 63
customers
retention rate, calculating, 526
valuing, 525–528