Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

A1 notation, 45

ABS function, 411, 736

absolute references, 45-47, 545-546, 589-590

accidental circular references, correcting, 430-431

Accounting Number Format button, 747, 748

ACCRINT function, 731

ACCRINTM function, 731

accumulated amounts, using NPV function to calculate

calculating future value, 330-331

smoothing payments, 331-332

ACOS function, 736

ACOSH function, 736

acronyms, returning, 696-697

ActiveX controls, 23

Add Level button, Tables menu, 247

Add Subtotal To list box, Subtotal dialog box, 275

add-in programs, 26-27

adding. See summing

Add-Ins dialog box, VB Editor, 642-643

Add-Ins tab, Options dialog box, 24-25

additional payment table, amortization schedules, 354-356

ADDRESS function, 230, 408, 735

address of value within a range, determining, 228

Address property, 672-673

adjustable bins, using to create histogram, 199-200

Advanced Filter dialog box, Tables menu, 261-263

Advanced tab, 17, 746

age, calculating, 156-157

Alignment group, Home tab, 11-12

Alignment tab, 12

All Chart Types option, 446

Allow drop-down list, Settings tab, 560

AMORDEGRC function, 731

AMORLINC function, 732

amortization schedules

credit card calculations, 356-357

dynamic amortization schedule, 351-354

payment and interest tables, 354-356

simple amortization schedule, 348-351

analysis tools

auditing capabilities, 30

database access, 27-28

outlines, 29

pivot tables, 29-30

scenario management, 29

Solver add-in, 30

AnalysisToolPak add-in, 21, 195-199

And criteria

conditional sums using multiple criteria, 208-209

counting cells that meet multiple criteria using, 187-188

AND function, 403-404, 735

And operator, 655

Annual effective rate, 314

annual percentage rates (APRs), 308

annual return calculation, 342-343

annuity with lump sum, present value of, 301-302

ANSI (American National Standards Institute) character set, 116, 122

Any Value option, Settings tab, 556

Apple II, 3

Apple III, 3

Apply Names dialog box, 75

Apply These Changes to All Other Cells with the Same Settings check box, Settings tab, 557

Appraisal Institute of America (AIA), 323

area

of circles, 282-283

of rectangles, 282

of squares, 282

of trapezoids, 283

of triangles, 283

AREAS function, 111, 735

arguments

arrays as, 105

expressions as, 104-105

full-column or full-row as, 103-104

indefinite number of, 719-723

literal values as, 104

names as, 103

optional, 718-719

other functions as, 105

array constant, 378-379, 382-383

Array formula entry error, 582-583

array formulas. See also arrays; multicell array formulas; single-cell array formulas

array constant elements, 379

creating array constants, 378-379

editing, 384-386

entering, 383-384

expanding or contracting multicell array formula, 386

selecting array formula range, 384

arrays. See also array formulas

dimensions of, 380-383

naming array constants, 382-383

of nonduplicated random integers, returning, 714-715

overview, 656-657

returning from a function, 712-713

using in named formulas, 93-94

ASIN function, 736

ASINH function, 736

asset use ratios, 368

assignment expressions, 654-656

asterisk (*) symbol, 128, 766

At Each Change In drop-down table, Subtotal dialog box, 274

"at" symbol (@), 32

ATAN function, 736

ATAN2 function, 736

ATANH function, 736

auditing capabilities, 30

auditing tools

fixing circular reference errors, 601

identifying cells of particular type, 596-597

tracing cell relationships, 599-600

tracing error values, 600

using background error checking, 601-603

using Formula Evaluator, 603-604

viewing formulas, 597-598

AutoCalculate feature, 178

AutoComplete feature, 251, 256

AutoFill feature, 49, 150, 171

AutoFiltering, 247

Automatic Except for Data Tables option, 362

automatic grouping of pivot tables, 505-510

automatic number formatting, 746

Automatic setting, Calculation mode, 42

AutoSum button, 107

AVEDEV function, 739

Average formula, Tables menu, 249

AVERAGE function, 98, 107, 251, 395-396, 405-406, 624, 687, 739

AVERAGEA function, 739

AVERAGEIF function, 739

AVERAGEIFS function, 739

Axis Labels option, Chart Tools, 454

Axis Options tab, Chart Tools, 454

axis title links, 448