C


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




Microsoft Press - Microsoft Office Excel 2007. Data Analysis and Business Modeling
MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)
ISBN: 0735623961
EAN: 2147483647
Year: 2007
Pages: 200

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net