` (accent grave) key, 619 copyrighted material

791

Index

{ } (curly brackets)array constants, 372-374, 378array formulas, 98, 190, 379performing operations on array, 383

- (dash), displaying zero with, 786-787#Data row identifier, 266#DIV/0! Error value, 56, 607-608/ (division operator)

#DIV/0! Error value, 607-608converting decimal hours, minutes, or seconds

to time, 182general discussion, 39precedence, 42, 612

$ (dollar sign), 775… (dots)

leading, 788as range reference operator, 109

= (equal sign)assignment statements, VBA code, 674comparison operators, 272entering formulas, 34named array constants, 378performing operations on array, 383

! (exclamation point), 720-721^ (exponentiation operator), 40-41, 43, 612> (greater than operator), 272>= (greater than or equal to operator), 272# (hash mark)

cells filled with, 57, 604displaying fractions, 782formatting dates, 152ISLIKE function, VBA code, 720number formats, 772, 775specifying dates and times in VBA code, 674

#Headers row identifier, 266- (hyphen), 770-771, 786-787< (less than operator)

close-to-zero rounding errors, 615comparison operators, 272

Symbols and Numerics` (accent grave) key, 619+ (addition operator), 39, 43, 612#All row identifier, 266‘ (apostrophe)

comments, VBA code, 668formatting numbers, 124-125, 770

* (asterisk symbol)filling cell with repeating character, 788filtering table by single criterion, 272-273lookup functions, 224number formatting codes, 776padding numbers, 135-136wildcard characters, 140

@ (at symbol)entering formulas, 34Lotus 1-2-3, 109number formatting codes, 776referencing data within table, 266

\ (backslash character)extracting filename from path specification, 144number formatting codes, 775

[ ] (brackets)formatting numbers, 787ISLIKE function, VBA code, 720-721number formatting codes, 776R1C1 notation, 48

: (colon character)number formatting codes, 775as range reference operator, 109time formats, 770

, (comma)argument separators, 108number formatting codes, 775two-dimensional arrays, 375

& (concatenation operator), 130-131, 438, 612

39_9781118490440-bindex.indd 79139_9781118490440-bindex.indd 791 3/5/13 11:15 AM3/5/13 11:15 AM

COPYRIG

HTED M

ATERIAL

Index792

“ ” (quotation marks)displaying fractions, 782displaying text in, 783formatting numbers, 776, 787

: (range operator), 40, 80#REF! error value, 57, 610; (semicolon symbol), in arrays, 374-375/ (slash character), 770, 775- (subtraction operator)

calculating difference between times, 178-179calculating number of days between

two dates, 163general discussion, 39versus negation operator, 42precedence, 43, 612-613

& (text concatenation operator) 39, 43~ (tilde), 140#Totals row identifier, 266_ (underscore character)

function names, 647number formatting codes, 776

, (union operator), 40#VALUE! Error value, 57, 610.zip file extension, 12

0 (zeros) appending to value, 780-781close-to-zero rounding errors, 615computing average excluding, 398-399displaying everything except, 788displaying items with no data in pivot table, 523displaying leading, 781displaying with dashes, 786-787distinguishing from blank cells, 229#DIV/0! error value, 607-608hiding, 781leading, 781number formatting codes, 775

3 Arrows icon set, 556-55720th Century dates, 15821st Century dates, 1581900, dates before, 728-7291900 date system, 150-1541904 date system, 150, 154, 179

<= (less than or equal to operator), 272- (minus sign)

contracting projects, VB Editor, 635number formatting codes, 775removing trailing, 142, 446VBA code, 675

* (multiplication operator), 39, 43, 612#N/A error value

in cell containing formula, 57debugging, 608determining whether cell contains, 193in line chart, 468as placeholder for missing element, 377

#NAME? error valuedebugging, 608-609deleted names, 67general discussion, 56inserting names in formulas, 77

- (negation operator)precedence, 43, 612-613versus subtraction operatorVBA code, 675

- (negative sign), 783<> (not equal to operator), 272#NULL! error value, 57, 609#NUM! error value

debugging, 609general discussion, 57returning list of unique items in range, 415sorting range of values dynamically, 414

% (percent operator), 39-40, 43, 612. (period)

In names, 68Number formatting codes, 775

+ (plus sign)conditional sums with multiple criteria, 219expanding projects, VB Editor, 635number formatting codes, 775

? (question mark)displaying fractions, 782filtering table by single criterion, 272-273ISLIKE function, VBA code, 720lookup functions, 224number formatting codes, 775wildcard characters, 140


Index 793

Advanced Text Import Settings dialog box, 446ages, calculating, 166AGGREGATE function

counting visible data rows, 265–266general discussion, 188summing range containing errors, 211, 396

AIA (Appraisal Institute of America), 328algorithms, for determining credit card number

validity, 594–599Allow drop-down list, Data Validation dialog

box, 572alternate-row shading, 560–561American National Standards Institute (ANSI)

character set, 126–127amortization schedules

credit card calculations, 351–352dynamic, 348–351example, 346–349general discussion, 315–316

Amount range, 215analysis

auditing, 30database features, 29pivot tables, 29–30Scenario Manager, 29Solver add-in, 30

Analysis ToolPak, 206–207And criteria, 196–198, 217–219AND function, 41AND operator

criteria range, 269filtering table by multiple criteria, 273–275

And operator, VBA code, 675angles, of right triangles, 294–296annuities, 323–325ANSI (American National Standards Institute)

character set, 126–127Any Value option, Data Validation dialog box, 573apostrophes (‘)

comments, VBA code, 668formatting numbers, 124–125, 770

Application object, VBA code, 705Application.Caller property, VBA code, 704Apply Names dialog box, 81

AA1 notation, 48abbreviations, for units, 284above average rule, 545–546ABS function

balance sheets, 359–360calculating difference between times, 178

absolute referencesconditional formatting, 558–559data validation, 577debugging, 611general discussion, 46–48

accent grave (`) key, 619Access files, 423account types, totaling in pivot tables, 504Accounting number format, 771–772accounts, trial balances, 357–360accounts payable, 358Accounts Receivable Turnover ratio, 363ACRONYM function, VBA code, 719Add Subtotal To list box, Subtotal dialog box, 280Add This Data to the Data Model check box,

Create PivotTable dialog box, 502add-ins

customizing, 28for functions, 662–664locating Function procedures, 649

Add-Ins dialog box, 663–664addition (+) operator

general discussion, 39precedence, 43, 612

ADDRESS function, 402Address property, VBA code, 693, 712addresses, of values within ranges, 238adjustable bins, 208–209Advanced Filter dialog box, 270advanced filtering

applying, 270–271clearing, 271criteria range, 269overview, 268specifying criteria, 271–277

Advanced tab, Ribbon, 21


Index794

compatibility with earlier versions of Excel, 211converting megaformula to, 596–597counting cells meeting multiple criteria, 197counting error values in range, 193counting occurrences of specific text in

range, 201counting text cells, 192counting unique values, 201–203disadvantages, 381editing, 379–380entering, 35, 379entry errors, 602frequency distributions, 203–205general discussion, 2–3, 190inserting, 98–99normal distribution, 302overview, 369–370, 395returning string’s last space character position,

591–594selecting range, 379simultaneous equations, 300summing top n values, 213two-column lookups, 238

array formulas, multi-cellcreating array constant from values in

range, 382creating array from values in range, 381–382displaying calendar in range, 416–418example, 370–371expanding or contracting, 380generating array of consecutive integers,

386–387overview, 381, 411–412performing operations on array, 383–384returning list of unique items in range, 414–415returning nonblank cells from range, 413returning only positive values from range,

412–413reversing order of cells in range, 413–414sorting range of values dynamically, 414–415transposing array, 384–385using functions with array, 384

array formulas, single-cellcomputing average excluding zeros, 398–399counting characters in range, 387–388

Apply These Changes to All Other Cells with the Same Setting check box, Data Validation dialog box, 574

Appraisal Institute of America (AIA), 328APR (annual percentage rate)

dynamic amortization schedule, 350general discussion, 314

ARABIC functionconverting roman numeral to value, 176general discussion, 286

area units, 284–285areas, of shapes, 296–298Arglist element, Function procedure

declarations, 647arguments

AGGREGATE function, 211arrays as, 111calculating loan information, 309–312columns or rows as, 109–110COMMISSION function, VBA code, 716–717CONVERT function, 284depreciation functions, 341descriptions, 654–655displaying list, 112–113expressions as, 110–111function, 650Function procedures with indefinite number of,

742–747literal, 110MROUND function, 289names as, 109optional, 740–742returning formatting information, 706–707ROUND function, 288SUBTOTAL function, 260SUM function, 210worksheet functions, 108–111

array argument, INDEX function, 228array constants

creating from values in range, 382general discussion, 372–374naming, 377–378

array formulasarray constants, 372–374, 377–378case-sensitive lookups, 232–233


Index 795

returning from function, 735–737single-cell array formulas, 387–393transposing, 736–737

arrows3 Arrows icon set, 556–557identifying dependents, 622identifying precedents, 621–622tracing error values, 622

asset use ratios, 363–364assets

balancing, 359–360current, 362general discussion, 357

assignment statements, 674–675AssignToFunctionCategory procedure,

VB Editor, 653asterisks (*)

filling cell with repeating character, 788filtering table by single criterion, 272–273lookup functions, 224number formatting codes, 776padding numbers, 135–136wildcard characters, 140

At Each Change In drop-down list, Subtotal dialog box, 279–280

at symbol (@)entering formulas, 34number formatting codes, 776

auditinggeneral discussion, 30tools, 617–625

AutoCorrect featurematching parentheses, 44symbols, 127

AutoFill featurecopying cell to adjacent cells, 55filling range with times, 183inserting series of dates, 161–162

AutoFilter featurecounting and summing, 189summing top n values, 213

AutoSums feature, 115Average Age of Inventory ratio, 363–364Average Collection Period ratio, 363–364

counting error values in range, 397counting text cells in range, 389–390determining closest value in range, 409determining whether range contains valid

values, 403–404determining whether value appears in range,

399–400eliminating intermediate formulas, 391–392examples, 371–372, 395finding row of particular occurrence of value in

range, 402overview, 387, 395removing nonnumeric characters from

string, 409returning last value in column, 410returning last value in row, 411returning location of maximum value in range,

401–402returning longest text in range, 402–403summing digits of integer, 404–407summing every nth value in range, 407–408summing largest values in range, 397–398summing range containing errors, 396summing rounded values, 406–407summing three smallest values in range,

388–389using array instead of range reference, 393

Array function, VBA code, 736–737arrays

as arguments, 111array formulas, 369–374, 378–381Boolean, 218converting range references to, 452creating array constant from values in

range, 382creating from values in range, 381–382dimensions, 369, 374–377, 384–385Function procedures with indefinite number of

arguments, 743multicell array formulas, 381–387in named formulas, 98–99of nonduplicated random integers, 737–739overview, 369programming, VBA code, 676–677


Index796

borrowingamortization schedules, 315–316calculating loan information, 308–318data tables, 353–357general discussion, 308mortgage calculator megaformula, 584–586time value of money, 307–308

box plots, 463–465brackets ([ ])

formatting numbers, 776, 787ISLIKE function, VBA code, 720–721R1C1 notation, 48

brackets, curly ({ })array constants, 372–374array formulas, 98, 190performing operations on array, 383

branches, pivot tables, 504breakpoints, 661–662bugs, 655Byte data type, 670

CCalculate Now control, 45Calculate Sheet control, 45calculated columns, 246, 261–263calculated fields, 524–528, 536–537calculated items, 524–525, 528–530, 536–537calculating

areas and perimeters, 296–297compound interest, 319–322dates, 163–167, 172–175, 726–728 formulas versus megaformulas, 590incomplete, 602loans with irregular payments, 316–318multicell array formulas, 370–371normal distributions, 301–303overview, 283periodic payment amount for loan, 312–313pivot tables, 506predicted values, 481–482recalculating formulas, 613rounding numbers, 287–294R-squared value, 483sales commissions, 715–717

AVERAGE functioncomputing average excluding zeros, 398–399eliminating intermediate formulas, 391–392simplifying formulas, 106

Average option, Total row drop-down list, 259AVERAGEIF function, 399axis labels, 460axis titles, 454

Bbackground colors, 249background error checking

debugging, 623–624numbers formatted as text, 125

backslash character (\)extracting filename from path specification, 144number formatting codes, 775

backsolving, 58–60Backstage View, 16–17balance sheets, 357–360Banded Columns control, Ribbon interface, 249Banded Rows control, Ribbon interface, 249bar charts, 551BASE function, 286, 436bases of right triangles, calculating, 294–296bell curves, 301Between dialog box, 547bin ranges

frequency distributions, 203–204histograms, 208–209

BIN2DEC function, 286, 436BIN2OCT function, 286blank cells

versus cells containing only space character, 604–605

distinguishing zeros from, 229Blank Row control, PivotTable Styles dialog

box, 505Boolean arrays, 218Boolean data type, 670Boolean values, 391borders

animated, when copying cells, 52zooming worksheets, 82–83


Index 797

CELLCOUNT function, 694CELLFORMULA function, VBA code, 702–703CELLHASFORMULA function, VBA code, 702CELLHASTEXT function, VBA code, 722–723CELLISHIDDEN function, VBA code, 703cells

above computed criteria, 276accepting larger value than previous, 578accepting only nonduplicate entries, 578activating named, 69adding text, 445–446blank, 229characters in, 123containing date or time, 155containing only space character, 604–605converting with formulas, 54copying or moving ranges, 50copying those containing conditional

formatting, 568copying to adjacent with AutoFill feature, 55counting blank, 191counting nonblank, 192counting nontext, 192counting number of words in, 148counting numeric, 192counting occurrences of specific text, 200–201counting pattern-matched, 725counting specific characters within, 141counting substring occurences, 141counting text, 192counting those meeting multiple criteria,

195–198counting total number, 191counting with COUNTIF function, 194–195creating divider between, 134creating names from adjacent text, 70–71determining data type, 708–709determining if particular word is contained,

721–722determining whether formula contained in, 702determining whether hidden, 703determining whether text contained in, 126,

722–723displaying number format for specified, 788drop-down lists, 574–575

simple interest, 318–319simultaneous equations, 300–301solving right triangles, 294–296surface and volume, 298–299total interest, 584–585unit conversions, 283–287with worksheet functions, 106

Calculation mode, 45Calculation section, Excel Options dialog box, 60calculators, Formula bar, 39calendars, 416–418case sensitivity

capitalizing, 340changing case, 136–137, 433–434counting occurrences of specific text, 201counting occurrences of substring in cell, 141custom functions, 646EXACT function, 129extracting characters from string, 138function names, 113, 647–648lookup functions, 232–233in names, 68user names, 741–742VBA code, 640

cash flowsinflows, 327–328, 331–332initial investment, 329–331irregular, 338–340outflows, 327–328, 333–334Quick Ratio, 363sequence, 328–329

categories, for functions, 652–654category fields, pivot tables, 496–497category_labels argument, SERIES formula, 450CEILING.MATH function, 288, 290cell argument, SHEETOFFSET function, 733cell formatting, 22–24CELL function, 118cell references

data validation, 576–577general discussion, 34literal values versus, 44in named formulas, 91from other sheet or workbook, 49–50range references and, 46–50


Index798

returning name with VBA Name property, 694–695

returning nonblank from range, 413returning number format, 707returning number in range, 693returning used range, 698–699reversing order in range, 413–414selecting, 21, 714–715single-cell array formulas, 371–372summing all in range, 209–210text, 389–391tracing relationships, 620–622typing in missing values, 266–267

Cells property, VBA code, 691CELLSINCOMMON function, VBA code, 697CellToRight formula, 93–94CELLTYPE function, VBA code, 708–709Central Standard Time (CST), 183–184Change PivotTable Data Source dialog box, 517CHAR function

general discussion, 126–129joining multiple cells, 48

character codes, 126–129characters

in cells, 123checking for specific, 579counting in range, 387–388counting in string, 133–134counting number in name, 645counting specific, within cell, 141determining if pattern is matched, 720extra spaces, 605extracting from string, 137–138filling cell with repeating, 788function names, 647number formatting codes, 775removing nonnumeric from string, 409removing nonprinting, 133removing strange, 435repeating, 134replacing or removing text in cells, 444returning formatting information, 707returning last space position in string, 590–594separator, 723special, 127, 787

cells (continued)duplicate values, 252editing those containing formulas, 611filled with hash marks, 604filling gaps in imported report, 442–443filling with repeating character, 788forcing to accept only text, 578formatting as date, 150formatting with time format, 177goal seeking, 58–60hiding contents, 788identifying those containing dates, 565identifying those containing formulas, 564–565implicit intersections, 78–79INDIRECT function with named range, 96–97joining multiple, 130–131links to, 453–455locating particular type, 617–618locating those containing conditional

formatting, 569looping Function procedure through range of,

689–690maintaining names, 83–84multicell array formulas, 370–371, 379–381named, 62, 67–73named formulas, 87–88navigating and selecting in table, 249–250precedent, 606pre-formatting, 775referencing in formulas within tables, 263referencing single cell in multicell range, 80, 690referencing those within pivot tables, 533–535referencing those within Total row, 265relative sheet references, 734repeating entry, 783replacing or removing text, 444–445returning formatting information, 706returning formula, 693, 702returning last nonempty in column or row,

730–731returning last value in column containing

empty, 410returning last value in row containing

empty, 411


Index 799

checklist, 446–447classifying values, 436–438converting values, 436data collection, 421duplicate rows, 426–428filling gaps in imported report, 442–443joining columns, 438matching text in list, 439–440overview, 421, 426randomizing rows, 439rearranging columns, 439removing extra spaces, 434–435removing strange characters, 435replacing or removing text in cells, 444spell checker, 443splitting text, 428–433trailing minus signs (-), 446

Clear Rules command, Conditional Formatting drop-down list, 547

Clipboardscopying VBA code, 642megaformulas, 588Windows and Office, 51

clock charts, 475–477CODE function

CHAR function versus, 129general discussion, 126–127

code windows, VB Editorgeneral discussion, 634minimizing and maximizing, 638overview, 637–638storing VBA code, 639

coefficient of determination, 480coefficients, simultaneous equations, 300col_index_num argument, VLOOKUP

function, 224colons (:)

number formatting codes, 775as range reference operator, 109time formats, 770

color scale rule, 546Color Scales command, Conditional Formatting

drop-down list, 547color scales conditional format, 551–554

chart sheets, 13charts

box plots, 463–465cell links, 453–455comparative histogram, 459–460data bars instead of, 551displaying conditional colors in column chart,

458–459with dynamic names, 208–209frequency distributions, 204–205, 208Gantt, 461–463general discussion, 26–27identifying maximum and minimum values,

466–468interactive, 486–492missing data, 462–463names in, 83overview, 449pivot, 531–532, 541–544plotting every nth data point, 465–466SERIES formula, 449–453single data point, 455–458timeline, 468–478trendlines, 478–486

checkerboard shading, 562Choose Data Source dialog box, 500CHOOSE function, 222Christmas Day, calculating date of, 174Circle Invalid Data option, Data Validation dialog

box, 574circle plotting, 473–475circles, measuring, 297circular references

columns as arguments, 110debugging, 602, 617fixing, 622–623general discussion, 57–58Sum12Cells formula, 95

Class modules, VB Editor, 638CLEAN function, 133, 435cleaning data

adding text to cells, 445–446changing case of text, 433–434changing vertical data to horizontal data,

440–442


Index800

selecting, 249sorting table by, 254–256, 509–510splitting text, 428–433two-column lookups, 237–238two-way lookups, 237VBA EntireColumn property, 695–696VBA Hidden property, 696VBA Union function, 698width, 604

COM Add-Ins dialog box, 620Comma Style button, Ribbon interface, 771Commands functions, 121commas (,)

argument separators, 108number formatting codes, 775two-dimensional arrays, 375

Comment Block button, VB Editor, 668comments, VBA code, 666, 668COMMISSION function, VBA code, 716–717COMMISSION2 function, VBA code, 717commissions, sales, 715–717common size financial statements, 360–361comparative histogram charts, 459–460comparison operators

filtering table, 272, 513–514general discussion, 39–40precedence, 43VBA code, 675

Compatibility category, worksheet functions, 751–752Compatibility functions, 119compatibility mode, 13compound interest, 319–322compressed folders, 12computed criteria

advanced filtering, 275–277average excluding zeros, 398–399

CONCATENATE function, 131concatenation formulas, 40concatenation operator (&)

general discussion, 130–131joining columns, 438precedence, 612

conditional execution, 105, 107conditional formatting

copying cells containing, 568deleting, 568

colorsconditional, 458–459data bars, 550editing cells containing formulas, 611negative values, 783number formats, 774, 776, 784–785sorting table by, 254

Columbus Day, calculating date of, 174column absolute references, 46column charts, 458–459COLUMN function

alternate-column shading, 560charts using data in specific range, 491

column labels, pivot tables, 503column_num argument, INDEX function, 228ColumnACount formula, 92columns

adding new, 250as arguments, 109–110calculated, 246, 261–263changing vertical data to horizontal data,

440–442combining references with row references, 266computed criteria, 276converting rows to, 384–385converting single into multiple, 146copying to another location, 270creating new data from multiple, 433data bars, 550deleting, 84, 251determining column letter for column

number, 143dynamic named formulas, 99–100filtering tables, 256inserting within named range, 83–84joining, 438listing names, 76naming, 71–72for pivot table, 494–495plotting every nth data point, 463–465rearranging, 439referencing data within table, 264removing duplicate rows, 427returning last nonempty cell in, 730–731returning last value in, 410


Index 801

COUNT functiongeneral discussion, 188pivot table, 511–512VBA Count property versus, 694

Count Numbers option, Total row drop-down list, 259

Count option, Total row drop-down list, 259Count property, VBA code, 693–694COUNTA function

counting nonblank cells, 192, 691dynamic named formulas, 100general discussion, 188returning last value in column, 410

COUNTBLANK function, 188, 191COUNTIF function

accepting text beginning with specific character, 579

computing average excluding zeros, 399counting cells, 194–195, 197–198counting error values in range, 193general discussion, 188

COUNTIFS functioncounting cells meeting multiple criteria, 196–198general discussion, 188

counting formulasblank cells, 191cells meeting multiple criteria, 195–198cells with COUNTIF function, 194–195error values in range, 193frequency distributions, 203–209general discussion, 187–189logical values, 193most frequently occurring value, 198–199nonblank cells, 192nontext cells, 192numeric cells, 192occurrences of specific text, 199–201overview, 187pattern-matched cells, 725sheets in workbook, 725–726text cells, 192total number of cells, 191unique values, 201–203words in range, 726

COUNTSHEETS function, VBA code, 725–726

dynamic amortization schedule, 350–351formula-based rules, 557–567general discussion, 545–546graphics, 549–557locating cells containing, 569numbers, 784–785overview, 545pivot table, 510–511rules, 545–546, 548–549, 567–568specifying, 547–549

Conditional Formatting feature, 784Conditional Formatting Rules Manager dialog box,

567–568conditional sums, 214–219cones, measuring, 299Connect with Line option, Hidden and Empty Cell

Settings dialog box, 462–463consecutive integers, 386–387Const statement, VBA code, 672constants

array, 372–374, 377–378, 382general discussion, 672–673naming, 88–90overview, 669returning error value from custom function, 735simultaneous equations, 300text, 89–90

continuous compounding, 322controls, 26conversion factors, 287CONVERT function, 283–287Convert software, 287Convert Text to Columns Wizard

general discussion, 429–430splitting strings without using formulas, 146

converting units, 283–287Copy to Another Location option, Advanced Filter

dialog box, 270copying data

cells containing conditional formatting, 568general discussion, 426from pivot table, 509text from formulas, 586VBA code, 642

cost argument, depreciation functions, 341


Index802

Current Ratio, 362–363curves, hypocycloid, 477–478Custom category, Format Cells dialog box, 773Custom format, for numbers, 772Custom Lists dialog box, 537Custom option, Data Validation dialog box, 573customizing

add-ins, 28display, 21macros, 28UI, 19–20

Customizing functions, 121CVErr function, VBA code, 734cylinders, measuring, 299

Ddashes (-), displaying zero with, 786–787Data Bars command, Conditional Formatting

drop-down list, 547data bars conditional format, 549–551data bars rule, 546data collection, 421Data Field Setting dialog box, 522data fields, pivot tables, 496–497Data form, 252Data Interchange Format (DIF) files, 423, 448Data Model feature, 538–541data points, 487–489data rows, 265Data Table dialog box, 354, 356data tables, 353–357data types

determining, 708–709errors in, 661general discussion, 669–670worksheet functions, 711

data validationcell references, 576–577criteria, 572–574drop-down lists, 574–575formulas, 576, 578–581general discussion, 571–572identifying invalid data, 566–567

Create Names from Selection dialog box, 70–71Create PivotTable dialog box, 501–502Create Relationship dialog box, 539–540Create Table dialog box, 247credit cards

amortization schedule, 351–352determining validity, 594–599payments, 313–315

criteria, single-criterion sum formulas, 214–216criteria argument

COUNTIF function, 194SUMIF function, 214

criteria rangesapplying advanced filtering, 270database functions, 277–279general discussion, 269specifying criteria, 271–277

CST (Central Standard Time), 183–184CSV files

exporting data to, 447–448general discussion, 423inserting into range, 424–426

Cube category, worksheet functions, 753Cube functions, 119cube root formulas, 41cubes, measuring, 298CUMIPMT function, 311CUMPRINC function, 309cumulative interest, 311cumulative normal distributions, 301–302cumulative principal, 309cumulative sums, 211–212curly brackets ({ })

array constants, 372–374, 378array formulas, 98, 190, 379performing operations on array, 383

Currency data type, 670Currency format, 771–772currency values

displaying as text, 132–133displaying leading dots, 788displaying text with numbers, 786

current assets, 362current liabilities, 362


Index 803

Date data type, 670, 674determining date of most recent Sunday, 169determining day of week, 168determining day of year, 166–168determining first day of week after, 169determining for particular occurrence of

weekday, 169–170determining quarter, 175determining week of month for date, 728determining week of year, 168–169determining whether year is leap year, 175displaying any, 160–161displaying calendar in range, 416–418displaying current, 160entering, 151–152entry formats, 151–152expressing as ordinals, 171–172filtering tables, 275, 508, 532–533formatting, 149, 155–157functions related to, 159Gantt charts, 462general discussion, 149generating series, 161–162grouping items in pivot tables by, 519–521identifying cells containing, 565inconsistent entries, 158invalid, 604leap years, 157–158number formatting codes, 776offsetting using only work days, 165overview, 149, 726pre-1900, 158range of supported, 150searching for, 152serial numbers, 150summing values based on comparison, 216systems of, 150XIRR function, 339–340XNPV function, 338–339

DATEVALUE functiondisplaying any date, 161general discussion, 159

DAVERAGE function, 277DAY function, 159DAYS worksheet function, 163

Data Validation dialog boxinserting drop-down list box, 97specifying validation criteria, 572–575

Database category, worksheet functions, 753database features, 29database file formats, 423database functions, 118, 277–279databases, summarizing, 495Date & Time category, worksheet functions, 754Date category, Format Cells dialog box, 157Date data type, 670, 674Date format, 771–772DATE function

calculating dates of holidays, 172–174determining day of year, 166–168displaying any date, 160filtering table by multiple criteria, 275general discussion, 159summing values based on date comparison, 216

date functions, 117Date option, Data Validation dialog box, 573DATEDIF function

calculating time between two dates, 166–167depreciation, 343general discussion, 159

datesbefore 1900, 728–729accepting by day of week, 580calculating for holidays, 172–174calculating last day of month, 174–175calculating next day of week, 727–728calculating next Monday, 726–727calculating number of days between, 163calculating number of work days between,

164–165calculating number of years between, 165–166calculating person’s age, 166calculating time between, 167choosing start date for interactive chart,

488–489combining times and, 154–155, 178converting non-date string to, 162–163converting year to roman numerals, 176counting occurrence of day of week, 170–171custom formats, 785


Index804

decision-making capabilitiesgeneral discussion, 105worksheet functions, 107

declaration lines, 666declarations, VBA code, 639declaring variables, 670–672declining balance depreciation, 341–343Decrease Decimal button, Ribbon interface,

771, 776degrees, XY charts, 473–474DEGREES function

converting radians to degrees, 295general discussion, 286

delimited dataCSV files, 447–448general discussion, 429–430

dependent lists, 580–581dependents, 621–622deposits, future value of, 318–325depreciation formulas, 327, 340–343descriptions

for arguments, 654–655for functions, 650–652

designs, hypocycloid curves, 477–478detail schedules, 345Developer tab, Ribbon interface, 26, 630DEVSQ function, 188DGET function, 277dialog box launchers, 16dialog sheets, 14diameters, of circles, 297DIF (Data Interchange Format) files

exporting data to, 448general discussion, 423

Difference range, 215Dim keyword, VBA code, 666, 670–671, 676–677dimensions, arrays

general discussion, 369one-dimensional, 369, 374–375, 384–385two-dimensional, 369, 375–377, 385

direct dependents, 621–622direct precedents, 621–622Disable All Macros with Notification option, Trust

Center dialog box, 630–631

DAYS*** function, 159DAYS360 function, 159DB function, 341–343dBase files, 423DCOUNT function, 188, 277DCOUNTA function, 188, 277DDB function, 341–343DDE/External functions, 121Debt Ratio, 364Debt-to-Equity Ratio, 364debugging

absolute and relative references, 611actual versus displayed values, 613–614auditing tools, 617–625cells containing only space character, 604–605cells filled with hash marks, 604circular reference errors, 617extra space characters, 605floating-point number errors, 614–615formulas returning error, 605–611functions, 655–662general discussion, 601–603logical value errors, 616–617mismatched parentheses, 603–604operator precedence, 612–613overview, 601phantom link errors, 615–616recalculating formulas, 613

Debug.Print statements, VBA code, 658DEC2BIN function, 286DEC2HEX function, 286DEC2OCT function, 286Decimal option, Data Validation dialog box, 573decimals

converting decimal hours, minutes, or seconds to time, 182

floating-point number errors, 614–615fractional dollars, 291–292number-formatting keyboard shortcuts, 771rounding, 289scaling values, 777–781summing rounded values, 406–407time values, 153truncating, 293


Index 805

DRAWONE2 function, VBA code, 714drop-down arrows, 253drop-down lists

data validation, 574–575dependent, 580–581inserting, 97pivot tables, 495–496selecting series from, 486–487

DSTDEV function, 277DSTDEVP function, 277DSUM function, 188, 277–278Duplicate Entry dialog box, 578–579duplicate rows

identifying, 427–428removing, 251–253, 426–427tables and lists, 246

duplicate values rule, 545–546DVAR function, 277DVARP function, 277dynamic amortization schedules, 348–349dynamic arrays, VBA code, 677dynamic named formulas

charts, 208–209general discussion, 99–101

dynamic schedules, 345

EEaster, calculating date of, 173EDATE function, 159Edit Formatting Rule dialog box, 554–557edit mode

eliminating manual editing with worksheet functions, 106–107

exact copies of formulas, 52–53switching to point mode, 67

Edit Name dialog box, 66Edit toolbar, VB Editor, 668elapsed time, 179Else clauses, VBA code, 679–680Enable Automatic Percent Entry check box, Excel

Options dialog box, 770Enable Background Error Checking check box,

623–624

DISCOUNT function, VBA code, 680–682discount rates, 328discounting formulas

IRR function, 333–338irregular cash flows, 338–340NPV function, 327–334

displays, customizing, 21dividers, between cells, 134DIVIDETWO function, VBA code, 688division operator (/)

#DIV/0! error value, 607–608converting decimal hours, minutes, or seconds

to time, 182general discussion, 39precedence, 43, 612

DMAX function, 277DMIN function, 277Do Until loops, VBA code, 686–687Do While loops, VBA code, 685–686document themes, 24DOLLAR function, 132dollar sign ($), 775dollar values

displaying fractions, 782displaying leading dots, 788displaying text with numbers, 786number-formatting keyboard shortcuts, 771pivot table, 512rounding, 289–292SPELLDOLLARS function, VBA code, 724–725

DOLLARDE function, 288, 291–292DOLLARFR function, 288, 291–292dots (...)

displaying leading, 788as range reference operator, 109

Double data type, 670double-declining balance depreciation, 341–343DPRODUCT function, 277drawing layers

charts, 26–27controls, 26shapes and illustrations, 26Sparkline graphics, 26

DRAWONE function, VBA code, 714


Index806

incomplete calculation, 602incorrect reference, 602logical, 602MYSUM function, VBA code, 747semantic, 602in spreadsheets, 602syntax, 602–603

Evaluate Formula dialog box, 408, 625Evaluate method, VBA code, 712EVEN function, 288, 293EXACT function, 129EXACTWORDINSTRING function, VBA code,

721–722Excel 4.0 macro sheets, 14Excel 5.0 dialog sheets, 14Excel 2013 Power Programming with VBA, 630Excel for Mac, 150Excel for Windows, 150Excel Macro-Enabled Workbook (*.xlsm) file

format, 632Excel Options dialog box

changing UserName property in VBA code, 642customizing UI, 19

exclamation points (!), 720–721execution of VBA code, controlling, 679execution speed, 669Exit For statements, VBA code, 684Exit Function element, Function procedure

declarations, 647expenditures, 340explicit intersection, 78exponential format, 770exponential trendlines, 483–485exponentiation operator (^)

general discussion, 40–41precedence, 43, 612

exporting data, 447–448expressions

as arguments, 110–111VBA code, 674

extended date functions, VBA code, 728–729eXtensible Markup Language (XML), 424external databases, 29EXTRACTELEMENT function, 654–655, 723–724

Enable Iterative Calculation setting, Excel Options dialog box, 58

ENCODEURL function, VBA code, 678Encrypt Document dialog box, 32End Function element, Function procedure

declarations, 647, 667Engineering category, worksheet functions,

755–756Engineering functions, 119Enhanced Data Form add-in, 252EntireColumn property, VBA code, 695–696EntireRow property, VBA code, 695–696EOMONTH function, 159equal sign (=)

assignment statements, VBA code, 674comparison operators, 272entering formulas, 34named array constants, 378performing operations on array, 383

equationslinear trendlines, 479–480simultaneous, 300–301trendlines, 485–486

equitybalancing, 359–360general discussion, 357

Eqv operator, VBA code, 675Error Alert tab, Data Validation dialog box, 573error checking

debugging, 623–624invalid data, 566–567numbers formatted as text, 125

Error Checking dialog box, 623–624error values

counting in range, 193, 397formulas returning, 605–611general discussion, 56–57handling with VBA code, 687–688printing, 606returning, 734–735summing range containing, 211, 396tracing, 606, 622

errors. See also debuggingarray formula entry, 602circular reference, 602


Index 807

present value of series of payments, 323–324Rule of 72, 322–323time value of money, 307–308

financial ratiosasset use, 363–364liquidity, 362–363overview, 361–362profitability, 364–365solvency, 364

financial schedulesamortization, 346–352data tables, 353–357financial ratios, 361–365financial statements, 357–361general discussion, 345indices, 365–366overview, 345

financial statements, 357–361Find and Replace dialog box

locating cells containing specific formatting, 569searching for cells containing only space

character, 605searching for dates, 152

FIND functioncounting occurrences of specific text, 201determining if cell contains particular word, 721finding and searching within string, 139–140

First Column control, Ribbon interface, 249FirstChar name, cell references, 91–92FirstInColumn formula, 96FirstInRow formula, 96fixed-length strings, 673fixed-width data, 429Flash Fill feature, 430–433, 438floating-point number errors, 614–615FLOOR.MATH function, 288, 290folders

compressed, 12trusted, 632

font colors, clearing, 249Font property, VBA code, 695For Each statement, VBA code, 732For Each-Next construct, VBA code, 689–690FORECAST function, 482

Ffactor argument, depreciation functions, 341FALSE value, in array formula, 391features, new, 10–11feet, measurements, 291field buttons, pivot charts, 542, 544fields

calculated, 524–528, 536–537in pivot tables, 508

file formatsspreadsheet, 422text, 423–426, 447–448XLSM, 632

File tab, Backstage View, 16–17FileList formula, 101filenames, extracting from path specification,

144, 433files

exporting data to, 447–448HTML, 424importing data from, 422–424inserting text file into specified range, 424–426not supported by Excel, 423–424XML, 424

FILES function, 101fill handles

editing cells containing formulas, 611general discussion, 55

FILLCOLOR function, VBA code, 707Filter button, column headers, 245Filter Button control, Ribbon interface, 249, 253filtering

advanced, 267–277counting and summing methods, 189names with errors, 82tables, 256–258, 508, 513, 515, 531–533

Financial category, worksheet functions, 755–758financial functions

amortization schedules, 315–316calculating loan information, 308–318data tables, 353–357future value of deposits, 318–322, 324–325mortgage calculator megaformula, 584–586overview, 307, 318


Index808

arrays in named, 98–99for box plots, 464calculated fields and calculated items, 525calculating, 45calculating compound interest, 319–322calculating loans with irregular payments,

317–318calculating total interest, 584–585cell and range references in named, 91circular references, 57–58clock charts, 475–476computed criteria, 276conditional formatting, 557–567converting pivot table to, 540converting strings to, 76converting to text, 38converting to values, 53–55copying or moving, 50–52copying text from, 586data validation, 576, 578–581displaying current time, 176–177dynamic named, 99–101editing, 38editing cells containing, 611elements, 34eliminating intermediate, 391–392entering, 34–36entering worksheet functions into, 112–117errors, 56–57evaluating, 624–625exact copies, 52–53finding incorrect, 48frequency distributions, 205–206functions in, 648–649goal seeking, 58–60hiding, 31, 55–56limits, 37linear interpolation, 241–242link, 49–50lookup, 221–222named, 87–88names in, 76–77operators, 39–44overview, 33pasting names, 36

forecasting, linear, 482–483Format All Cells Based on Their Values rule

type, 549Format as Table command, 247Format Cells dialog box

changing Hidden and Locked status, 56conditional formatting, 548date and time formatting, 155–156formatting cell as date, 150formatting numbers, 771–772general discussion, 18–19

format codes, for numbers, 773–774Format Only Cells That Contain rule type, 549Format Only Top or Bottom Ranked Values rule

type, 549Format Only Unique or Duplicate Values rule

type, 549Format Only Values That Are Above or Below

Average rule type, 549Format Picture task pane, 20–21Format Trendline task pane, 478–479formatting. See also conditional formatting;

number formatscells, 22–24, 706numbers, 287tables, 245, 505–507

Formula AutoComplete featuredisabling, 112entering worksheet function into formula, 112inserting names in formulas, 36, 77referencing data within table, 264–265

Formula AutoCorrect, 603Formula bar

as calculator, 39creating array constant from values in range, 382date formats, 151displaying more than one line, 37SERIES formula, 449–450

formula cells, 58–60Formula Evaluator, 408, 624–625Formula property, VBA code, 693Formula view, 619formulas. See also lookup formulas

amortization schedules, 315–316applying names, 80–81


Index 809

function descriptions, 650–652functions in formulas, 648–649Insert Function dialog box, 650–655naming function, 647–648overview, 643passive, 646testing and debugging functions, 655–662

functions. See also lookup functions; worksheet functions

adding hours, minutes, or seconds to time, 183automatically inserting, 115calculating dates of holidays, 172–174calculating difference between times, 178–179calculating last day of month, 174–175calculating loan information, 309–312calculating number of days between two

dates, 163calculating number of work days between two

dates, 164–165calculating number of years between two dates,

165–166calculating person’s age, 166categories, 117–121conditional formatting formulas with custom,

564–567converting between time zones, 183–184converting decimal hours, minutes, or seconds

to time, 182converting military time, 182converting non-date string to date, 162–163converting year to roman numerals, 176counting occurrence of day of week, 170–171database, 277–279depreciation, 341determining date for particular occurrence of

weekday, 169–170determining date of most recent Sunday, 169determining date’s quarter, 175determining day of week, 168determining day of year, 166–168determining first day of week after date, 169determining week of year, 168–169determining whether year is leap year, 175displaying any date, 160–161displaying any time, 177–178

protecting, 30recalculating, 45, 613referencing cells within pivot tables, 534referring to cell containing date or time, 155relative references with named, 92–96removing middle names, 587rounding numbers, 288–289sample, 37–38simplifying, 106spaces and line breaks, 36–37within tables, 261–263transforming data, 137worksheet functions in named, 90–91XLM macros in named, 101–102

FORMULATEXT function, 702For-Next loops, VBA code, 667, 683–684, 692fraction formats, 770, 772fractional dollars, 291–292fractions, displaying, 781–782frequency distributions

Analysis ToolPak, 206–207creating with adjustable bins, 208–209creating with REPT function, 134–135formulas, 205–206FREQUENCY function, 203–205pivot tables, 208, 522–524

FREQUENCY functionfrequency distributions, 203–205general discussion, 188

function arguments, 34Function Arguments dialog box

argument descriptions, 654–655entering function into formula, 113–117

Function element, Function procedure declarations, 647

Function Library commands, 113Function Name list, Insert Function dialog box, 117Function procedures

add-ins for functions, 662argument descriptions, 654–655custom functions, 643–644declaring function, 646–647examples, 644–646, 666–668function arguments, 650function categories, 652–654


Index810

goal seeking, 58–60GPAs (grade point averages), 234–235gradients, color scale conditional format, 553–554grading tests, 233–234Grand Totals control, PivotTable Styles dialog

box, 505grand totals, pivot tables, 503, 529graphics

color scales, 551–554data bars, 549–557icon sets, 554–557

greater than operator (>)comparison operators, 272general discussion, 39precedence, 612

greater than or equal to (>=) operator, 40, 272greater than ten rule, 545–546Greenwich Mean Time (GMT), 183–184GREETME function, VBA code, 680, 682Gross Profit Margin ratio, 364Grouping dialog box, 512, 515, 520, 522–524grouping items, in pivot tables

automatically, 518–522calculated items, 530general discussion, 503manually, 516–517overview, 515viewing grouped data, 518

groups, Ribbon interface, 15growth rates

general discussion, 336–337indices, 365–366

guess argument, financial functions, 309

HHasFormula property, VBA code, 693hash marks (#)

cells filled with, 57, 604displaying fractions, 782formatting dates, 152ISLIKE function, VBA code, 720number formats, 772number formatting codes, 775specifying dates and times in VBA code, 674

Header Row control, Ribbon interface, 249

functions (continued)displaying current date, 160expressing date as ordinal, 171–172FREQUENCY, 203–205general discussion, 3, 34generating series of dates, 161–162HLOOKUP, 225–226LOOKUP, 226–227nested, 111non-time-of-day values, 185–186offsetting dates using only work days, 165related to dates, 159related to times, 176returning array, 387rounding numbers, 288rounding time values, 184–185summing times exceeding 24 hours, 179–181unit conversion, 286using with array, 384VBA code, 677–679VLOOKUP, 223–225worksheet, 24, 90–91

FV (future value)continuous compounding, 322general discussion, 308of series of deposits, 324–325of single deposit, 318–322

fv argument, financial functions, 309FV function, 319–321

Ggalleries, 16Gantt charts, 461–463Gap Width value, 459Gaps option, Hidden and Empty Cell Settings

dialog box, 462–463General formatting code, 775General number format, 769, 771–772geometric growth rates, 336–337GETPIVOTDATA function, 534–535GMT (Greenwich Mean Time), 183–184Go To dialog box, 569Go To Special dialog box

identifying precedents and dependents, 621–622

locating particular type of cell, 617–618


Index 811

hundreds, displaying values in, 778hurdle rates, 328hyphens (-)

displaying zero with dashes, 786–787number formatting, 770–771

hypocycloid curves, 477–478hypotenuses of right triangles, calculating, 294–296

Iicon sets, 546–547, 554–557identity operations, 391IF function

arrays as arguments, 111avoiding #DIV/0! error value, 607calculating loans with irregular payments,

317–318choosing among multiple lookup tables, 233combining LOOKUP and TREND function, 242counting error values in range, 397counting text cells in range, 390decision-making capabilities, 107displaying calendar in range, 417distinguishing zeros from blank cells, 229finding row of particular occurrence of value in

range, 402hiding cumulative sums for missing data, 212lookup formulas, 222–223returning location of maximum value in range,

401–402summing every nth value in range, 408summing times exceeding 24 hours, 180

IFERROR functionavoiding #DIV/0! error value, 607–608determining column letter for column

number, 143extracting first word of string, 144extracting last word of string, 145looking up exact values, 230–231lookup formulas, 222megaformula to remove middle names, 587returning list of unique items in range, 414returning only positive values from range, 413summing digits of integer, 405summing range containing errors, 396

headerscalculated columns, 262changing vertical data to horizontal data,

441–442general discussion, 244–245

heights of right triangles, calculating, 294–296Help on This Function link, Insert Function dialog

box, 116Help system

general discussion, 22–23Help on This Function link, Insert Function

dialog box, 116VB Editor, 634

HEX2BIN function, 286HEX2DEC function, 286, 436HEX2OCT function, 286Hidden and Empty Cell Settings dialog box,

462–463hidden names, listing, 72–73Hidden property, VBA code, 696hidden rows

ignoring, 264SUBTOTAL function, 260

Hidden status, 56hierarchies, objects, 12, 705Highlight Cell Rules command, Conditional

Formatting drop-down list, 547highlighting rows, 560–561Histogram dialog box, Analysis ToolPak, 206–207Histograms. See text histogramsHLOOKUP function

general discussion, 222, 225–226looking up exact value, 230–231looking up value by closest match, 239

holidays, calculating dates of, 172–174horizontal arrays

converting to vertical array, 384–385general discussion, 374–375naming array constants, 378

horizontal data, 440–442HOUR function, 176HTM (Web Page) files, 448HTML format

general discussion, 29importing, 424


Index812

Information category, worksheet functions, 758–759

Information functions, 119initial investments, 329–331initial values, 333input cells, 58–60Input Message tab, Data Validation dialog box, 572Inquire add-in, 620Insert Calculated Field dialog box, 527Insert Calculated Item dialog box, 529Insert Function dialog box, 114–117, 648–652Insert shortcut menu, 250Insert Slicers dialog box, 531Insert Symbol dialog box, 787INT function, 288, 292–293Integer data type, 670integers

array of consecutive, 386–387generating random, 713returning array of nonduplicated random,

737–739summing digits, 404–407

interactive charts, 486–492interest

calculating periodic payment amount for loan, 312–313

calculating total, 584–585compound, 319–322credit card payments, 314future value of series of deposits, 324–325IPMT function, 309–310simple, 318–319time value of money, 307–308

Interest column, amortization schedules, 348interest rates

amortization schedule, 348, 350compound interest, 319–322general discussion, 308one-way data tables, 353–354periodic, 312present value of series of payments, 323–324RATE function, 311Rule of 72, 322–323simple interest, 318–319two-way data tables, 355

IFNA function, 608If-Then constructs, VBA code, 679–681If-Then-Else constructs, VBA code, 680–681Ignore Blank check box, Data Validation dialog

box, 574Ignore Relative/Absolute check box, Apply Names

dialog box, 81illustrations, 25Immediate window, VB Editor

Debug.Print statements, 658function categories, 652general discussion, 634, 661

Imp operator, VBA code, 675implicit intersections, 78–79Import Data dialog box, 425importing data, 421–426inches, measurements, 291income, IRR function, 335income statements, 357, 359–360Increase Decimal button, Ribbon interface, 771, 776incremental times, 183increments, plotting circles, 474indenting, VBA code, 639Independence Day, calculating date of, 173INDEX function

accessing individual elements from array, 378combining with MATCH function, 227–229looking up values to left, 232lookup formulas, 222referencing single cell in multicell named

range, 80returning longest text in range, 403two-way lookups, 236

indicesgeneral discussion, 365–366VBA code, 676–677

indirect dependents, 621–622INDIRECT function

general discussion, 91–92generating array of consecutive integers, 387looking up value by closest match, 240with named range, 96–97summing largest values in range, 398

indirect precedents, 621–622INFO function, 118


Index 813

LLabor Day, calculating date of, 174LARGE function, 108, 213, 397–398Last Column control, Ribbon interface, 249LASTINCOLUMN function, VBA code, 730–731LASTINROW function, VBA code, 730–731leading dots (...), 788leading spaces, 133, 434–435, 605leading zeros (0), 781leap years, 157–158, 175, 318LEFT function

extracting characters from string, 137–138extracting first word of string, 144general discussion, 91literal values as arguments, 110

LEN function, 133–134, 387–388Len function, VBA code, 667lending, 308less than operator (<)

close-to-zero rounding errors, 615comparison operators, 272general discussion, 39, 41precedence, 612

less than or equal to operator (<=)comparison operators, 272general discussion, 40–41precedence, 612

letter grades, 233–235letters

column, 143in names, 68

liabilities, 357, 359–360, 362life argument, depreciation functions, 341Like operator, VBA code, 720–721LIKE operator, VBA code, 725line breaks

forcing between strings, 130general discussion, 36–37VBA code, 639–640, 657

line charts, 456–457, 466–468linear interpolation, 240–242

internal rates of return, 339–340Internet features, 28–29interpolation, linear, 240–242Intersect function, VBA code, 697intersection operator, 40, 77–79, 81invalid data. See data validationINVALIDPART function, 566–567Inventory Turnover ratio, 363investment formulas

future value of deposits, 318–322, 324–325overview, 307, 318present value of series of payments, 323–324Rule of 72, 322–323time value of money, 307–308

IPMT function, 309–310IRR function, 334–338irregular cash flows, 338–340irregular payments, 316–318ISERROR function, 193ISFORMULA function, 564–565ISLIKE function, VBA code, 720–721IsMissing function, VBA code, 742ISNA function, 193ISNONTEXT function, 192ISODD function, 576–577ISOWEEKNUM function, 159, 168ISTEXT function, 126items, calculated, 524–525, 528–530, 536–537items, pivot tables, 503iteration, IRR function, 335

Jjoining columns, 438

Kkey names, 3keyboard shortcuts

formatting numbers, 771names, 67–73navigating Ribbon interface, 16recalculating formulas, 45selecting objects and cells, 21

keyboards, navigating Ribbon interface, 16


Index814

general discussion, 39–40greater than (>), 39, 612greater than or equal to (>=), 40, 272less than (<), 39, 41, 272, 612less than or equal to (<=), 40–41, 272, 612not equal to (<>), 40–41, 272precedence, 39–40, 612

logical errorsdebugging, 602VBA code, 655

logical formulascomputed criteria, 276conditional formatting, 558data validation, 576

Logical functions, 119logical operators, VBA code, 675logical values

as arguments in SUM function, 210counting, 193errors, 616–617

Long data type, 670Long Date format, 155Lookup & Reference category, worksheet

functions, 760lookup formulas. See lookup functionsLOOKUP function

combining with TREND function, 242general discussion, 222, 226–227letter grades for test scores, 234looking up values to left, 231

lookup functions. See also INDEX function; MATCH function; VLOOKUP function

case-sensitive lookups, 232–233choosing among multiple lookup tables, 233determining address of value within range, 238general discussion, 118, 222–223HLOOKUP function, 222, 225–226, 230–231, 239INDEX function, 227–229letter grades for test scores, 233–234looking up exact values, 230–231looking up value by closest match, 239–240looking up value with linear interpolation,

240–242looking up values to left, 231–232

linear trendlinescalculating predicted values, 481–482calculating R-squared value, 483calculating slope and y-intercept, 481equation, 485forecasting, 482–483overview, 479–480

LINEST function, 481link formulas, 49–50links

add-in files, 663to cells, 453–455date systems in workbooks, 150phantom, 615–616pivot tables and pivot charts, 544updating, 50

liquidity ratios, 362–363List option, Data Validation dialog box, 573lists

advanced filtering, 267–277converting table to, 267database functions, 277–279example, 244general discussion, 244inserting subtotals, 279–282matching text, 439–440overview, 243tables versus, 245–246

literal values, 44, 110Live Preview, 16loan formulas

calculating loan information, 308–318time value of money, 307–308

loansamortization schedules, 315–316data tables, 353–357mortgage calculator megaformula, 584–586

local constants, 672Locked status, 56logarithmic trendlines, 483–485Logical category, worksheet functions, 759logical comparison operators

equal to (=), 39, 612filtering table by multiple criteria, 273–275


Index 815

Manual Recalculation mode, 118Martin Luther King, Jr. Day, calculating date of, 173MATCH function

combining with INDEX function, 227–229general discussion, 222looking up values to left, 232returning longest text in range, 403returning string’s last space character position,

591–592two-way lookups, 236

match_type argument, MATCH function, 227matches

looking up value by closest, 239–240text, 439–440, 720–721

Math & Trig category, worksheet functions, 760–762

math functions, 117, 124, 383, 469–473MAX function, 106, 402–403Max option, Total row drop-down list, 259MAXALLSHEETS function, VBA code, 731–733measurements

feet and inches, 291unit conversion, 283–287, 436

megaformulasconverting to array formulas, 596–597determining validity of credit card number, 594general discussion, 583generating random names, 599–600mortgage loan calculator example, 584–586overview, 583pros and cons, 600removing middle names example, 586–590returning string’s last space character position,

590–594Memorial Day, calculating date of, 173memory, reserving with data types, 670menu bar, VB Editor, 634menus, shortcut, 17–18message boxes, VBA code, 656–657metric units, 284Microsoft Excel Objects node, VB Editor, 635MID function, 137–138, 405, 592military time, 182millions, displaying values in, 779–780

lookup formulas, 221–222LOOKUP function, 222, 226–227, 231, 234MATCH function, 227–229#N/A error value, 608overview, 221two-column lookups, 237–238two-way lookups, 235–237VLOOKUP function, 223–225

lookup tablescalculating sales commissions, 715–716choosing among multiple, 233classifying values, 437–438using array instead of range reference, 393

lookup_array argument, MATCH function, 227lookup_value argument

HLOOKUP function, 225LOOKUP function, 226MATCH function, 227VLOOKUP function, 224

lookup_vector argument, LOOKUP function, 226–227

looping constructs, VBA code, 683–687Lotus 1-2-3, 109LOWER function, 136–137, 433–434lump sum payouts, 323–324

MMacro Control functions, 121Macro Options dialog box, 651–652Macro Settings section, Trust Center dialog box,

630–631macro sheets, 14macros

customizing, 28recorder, 641–642saving workbooks containing, 632security features, 630–632VBA code, 6XLM, in named formulas, 101–102

Madison, Josh, 287Manage Relationships dialog box, 540Manage Rules command, Conditional Formatting

drop-down list, 547Manual Calculation mode, 45


Index816

generating array of consecutive integers, 386–387

overview, 381, 411–412performing operations on array, 383–384returning list of unique items in range, 414–415returning nonblank cells from range, 413returning only positive values from range,

412–413reversing order of cells in range, 413–414sorting range of values dynamically, 414–415transposing array, 384–385using functions with array, 384

multidimensional arrays, VBA code, 677multiples, rounding values to nearest, 289multiplication operator (*)

general discussion, 39precedence, 43, 612

multipliers, CONVERT function, 284MYSUM function, VBA code, 744–747

Nn argument, VBA EXTRACTELEMENT function, 723N/A, displaying for text, 782Name box, Formula bar, 62, 68–69Name element, Function procedure

declarations, 647Name Manager, 65–67, 82, 86–87Name property, VBA code, 694–695named formulas, 488names

#NAME? error value, 608–609Application object, VBA code, 705applying automatically when creating

formula, 81applying to existing formulas, 80–81as arguments, 109arrays in named formulas, 98–99automatically created, 72–73changing for tables, 264in charts, 83charts with dynamic, 208–209computed criteria and field names, 276conflicting, 64–65constants, 88–90conventions, 65

MIN function, 402, 458Min option, Total row drop-down list, 259mini toolbar, 17–18minus sign (-)

contracting projects, VB Editor, 635number formatting codes, 775removing trailing, 142, 446VBA code, 675

MINUTE function, 176missing data, 462–463mixed references

changing vertical data to horizontal data, 442cumulative sums, 211–212general discussion, 46–48with named formulas, 95–96

Mod 10 check-digit algorithm, 594–595MOD function, 408, 560Mod operator, VBA code, 675modal dialog boxes, 18–19MODE function, 198–199modeless dialog boxes, 18–19modules, 632, 636–637Mondays, calculating next, 726–727money, time value of, 307–308month argument, depreciation functions, 341MONTH function, 90, 159MonthName formula, 90–91MonthNames formula, 98MONTHNAMES function, VBA code, 736Months option, Grouping dialog box, 520MONTHWEEK function, VBA code, 728More Functions option, Total row drop-down

list, 259Moving Average option, Format Trendline task

pane, 484MROUND function, 288–290MsgBox statement, VBA code, 656–657multi-cell array formulas

creating array constant from values in range, 382

creating array from values in range, 381–382displaying calendar in range, 416–418editing, 379–380expanding or contracting, 380general discussion, 369–371


Index 817

Names with Errors filter, Name Manager dialog box, 82

negation operator (-)precedence, 43, 612–613versus subtraction operator, 42VBA code, 675

negative sign (-), 783negative values

in comparative histogram chart, 460displaying negative sign on right, 783number-formatting keyboard shortcuts, 771removing trailing minus signs, 142, 446rounding, 292–294summing, 215time, 150, 178–179

nested functions, 111, 223nested loops, VBA code, 684nested parentheses [()], 43–44net present value, 338–339Net Profit Margin ratio, 364Net Working Capital ratio, 362–363NETWORKDAYS function, 159, 164–165NETWORKDAYS.INTL function, 159, 165New Formatting Rule dialog box, 548, 550, 552–553New Name dialog box

array constants, 377–378general discussion, 66multisheet names, 73–74new names, 67–68

New Rule command, Conditional Formatting drop-down list, 547

New Year’s Day, calculating date of, 172NEXTDAY function, VBA code, 727–728NEXTMONDAY function, VBA code, 726–727nickels, rounding values to, 2901900, dates before, 728–7291900 date system, 150, 1541904 date system, 150, 154, 179no switch argument, depreciation functions, 341nonbreaking spaces, 435noncontiguous ranges, 67None option, Total row drop-down list, 259nonlinear trendlines, 483–485nonprinting characters, 133normal distributions, 301–303

counting characters, 645dynamic named formulas, 99–101with errors, 82extracting first, middle, and last, 145–147in formulas, 76–77functions, 647–648general discussion, 25, 61–62generating random, 599–600hidden, 72–73INDIRECT function with named range, 96–97internal, 68intersection operators, 77–79invalid, 69, 71listing, 75–76maintaining, 83–84megaformulas, 589misspelled variable, 670multiple, 67multisheet, 73–75Name Manager, 65–67named formulas, 87–88objects, 86–87overview, 61pasting into formulas, 36problems with, 84–87projects, 636range operator, 80ranges, 608–609referencing, 63–64referencing single cell in multicell named

range, 80removing middle, 586–590removing titles, 148rules, 68scope, 63–66in SERIES formula, 451–452shortcuts for creating new, 67–73of tables, 245unapplying, 82user, 741–742VBA module, 636–637viewing named ranges, 82–83workbooks, 704–705worksheets, 704XLM macros in named formulas, 101–102


Index818

Format Cells dialog box, 771–772general discussion, 769hiding certain types of entries, 787–788hiding zeros, 781overview, 769padding numbers, 136parts of string, 774–775repeating cell entry, 783returning, 707with Ribbon interface, 770–771scaling values, 777–781shortcut keys, 771special symbols, 787testing custom, 783with TEXT function, 787

Number tab, Format Cells dialog box, 771–773NumberFormat property, VBA code, 695numbers

array constant elements, 374column, 143converting Boolean values to, 391converting year to roman numerals, 176counting numeric cells, 192custom formats, 157displaying text with, 786expressing as ordinals, 142–143expressing date as ordinal, 171–172extracting from text, 431floating-point, 614–615generating random, 712–715#NUM! error value, 609odd, 576–577padding, 135–136rounding, 287–294spelling out, 724–725as text, 124–125

numeric formatting, 22–23

OObject data type, 670object hierarchies, 12object model concept, 11–12object variables, 696object-oriented programming (OOP), 11–12

Normal style, changing in Style dialog box, 775Normal view, 619normalized data, 497–499not equal to operator (<>), 40–41, 272Not operator, VBA code, 675notebook tab metaphors, 18–19NOW function

displaying current time, 176–177displaying time on clock chart, 476displaying values as text, 132general discussion, 159volatile functions, 118

nper argument, financial functions, 309NPER function, 311NPV function

checking results, 337–338future outflows, 333–334general discussion, 328–329initial cash inflow, 331–332initial investment, 329–331IRR function and, 335–336overview, 327–328terminal values, 332–333

num variable, VBA code, 683Number Format control, 155Number Format drop-down list, Format Cells

dialog box, 155–156number formats

automatic, 770codes, 775–777coloring values, 784–785conditional formatting, 784–785dates and times, 785displaying for specified cell, 788displaying fractions, 781–782displaying leading dots, 788displaying leading zeros, 781displaying N/A for text, 782displaying negative sign on right, 783displaying text in quotes, 783displaying text with numbers, 786displaying values in, 131–133displaying zero with dashes, 786–787filling cell with repeating character, 788


Index 819

Ppadding numbers, 135–136Page Break between Groups check box, Subtotal

dialog box, 280Panko, Raymond, 602ParamArray argument, Function procedures,

650, 743Parent property, VBA code, 694, 705parentheses [()]

general discussion, 34matching, 44in megaformulas, 585, 594mismatched, 603–604nested, 43–44number formatting codes, 775operator precedence, 612overriding operator precedence, 42

passive Function procedures, 646passwords

general discussion, 663hiding formulas, 55for workbooks, 32

Paste Name dialog box, 36, 75–77pasting data

converting formulas to values, 54general discussion, 426from pivot table, 509

path specifications, 144pattern-matched cells, 720, 725Payment column, amortization schedules, 347payment periods, 311–313payments (PMT)

additional, 350amortization schedule, 347–348calculating periodic payment amount for loan,

312–313credit cards, 313–315, 351–352determining number of payment periods, 311dynamic amortization schedule, 348–350general discussion, 308irregular, 316–318PMT function, 309present value of series of, 323–324two-way data tables, 355

objectsgeneral discussion, 11–12hierarchy, 705naming, 86–87selecting, 21

OCT2BIN function, 286OCT2DEC function, 286, 436OCT2HEX function, 286ODD function, 288, 293ODS (OpenDocument Spreadsheet) files, 422, 448Office Clipboard, 51offset argument, SHEETOFFSET function, 733OFFSET function

dynamic named formulas, 100lookup formulas, 222returning last value in column, 410volatile functions, 118

Offset property, VBA code, 691–692On Error GoTo statement, VBA code, 688On Error statement, VBA code, 687–688one-dimensional arrays, 369, 374–375, 384–385one-way data tables, 353–355onscreen display, 21OnTime method, Application object, 477OOP (object-oriented programming), 11–12Open tab, Backstage View, 17OpenDocument Spreadsheet (ODS) files, 422, 448Operating Cycle ratio, 364operations, performing on arrays, 383–384operators

general discussion, 34, 39–44precedence, 41–44, 612–613reference, 40–41VBA code, 640, 675

Option Explicit statements, VBA code, 670, 672optional arguments, 740–742Or criteria, 197–198, 218–219OR function, 41, 111OR operator, 273–275Or operator, VBA code, 675order argument, SERIES formula, 451ordinals, 142–143, 171–172outlines, 281–282overtime, calculating, 180–181


Index820

referencing cells within, 533–535reverse, 499specifying data, 501–502specifying location, 502–504summarizing data, 281–282terminology, 503–504

PivotChart Fields task pane, 544PivotChart Tools tab, Ribbon interface, 543PivotTable Fields task pane, 500, 502, 504–509,

539–540PivotTable Options dialog box, 506placeholders, number formatting codes, 775plotting

box plots, 463–465circle, 473–475data points, 465–466, 487–488math functions, 469–473

plus sign (+)conditional sums with multiple criteria, 219expanding projects, VB Editor, 635number formatting codes, 775

PMT (payments)additional, 350amortization schedule, 347–350calculating periodic payment amount for loan,

312–313credit cards, 313–315, 351–352determining number of payment periods, 311general discussion, 308irregular, 316–318PMT function, 309present value of series of, 323–324two-way data tables, 355

pmt argument, financial functions, 309PMT function, 309, 312–313, 347point mode

applying names automatically when creating formula, 81

general discussion, 35referencing cells within pivot tables, 534switching to edit mode, 67

polynomial trendlines, 483–484, 486population data, 489–490Portable Document Format (PDF) files, 448

payoff periods, credit card payments, 313–315PDF (Portable Document Format) files, 448pennies, rounding values to, 289–290per argument, financial functions, 309percent operator (%)

general discussion, 39–40precedence, 43, 612

Percent Style button, Ribbon interface, 771Percentage format, 772percentages

frequency distributions, 203, 205–206indices, 365–366number formats, 770–771pivot table, 512

perimeters, of shapes, 296–297period argument, depreciation functions, 341periodic interest rates, 312, 320–321periods (.)

in names, 68number formatting codes, 775

periods, for interest, 308phantom link errors, 615–616pictures, 452–453, 455pie charts, 456–458pivot charts, 30, 208, 531–532, 541–544pivot tables

appropriate data, 496–499automatically creating, 499–501calculated fields and calculated items, 524–530calculations, 506copying content, 509counting and summing, 189Data Model feature, 538–541examples, 494–496, 509–515, 535–537filtering, 531–533formatting, 505–507frequency distribution, 208, 522–524general discussion, 29–30, 493–494grouping items, 515–522layout, 504–505manually creating, 501–509modifying, 507–509overview, 493pivot charts and, 542–544


Index 821

Public keyword, VBA code, 672, 676–677PV (present value), 308, 323–324PV function, 311–312, 337–338pyramids, measuring, 299Pythagorean theorem, 294

Qquarters, determining for date, 175quartile plots, 463question marks (?)

displaying fractions, 782filtering table by single criterion, 272–273ISLIKE function, VBA code, 720lookup functions, 224number formatting codes, 775wildcard characters, 140

Quick Access toolbar, 15, 19, 252Quick Ratio, 363quotation marks (“ “)

displaying fractions, 782displaying text in, 783formatting numbers, 776, 787

RR1C1 notation, 48radians

general discussion, 111trigonometric functions, 117XY charts, 473–474

RADIANS functionconverting degrees to radians, 117, 295general discussion, 111unit conversion, 286

RAND function, 108, 118RANDOMINTEGERS function, VBA code, 737–739randomizing

names, 599–600numbers, 712–715range, 739–741returning array of nonduplicated random

integers, 737–739rows, 439text, 718–719

positive values, 412–413power trendlines, 483–485Power Utility Pak, 6, 445PPMT function, 309precedence, of operators, 612–613, 675precedent cells, 606, 621–622precision, adjusting, 60Precision as Displayed option, Excel options dialog

box, 769present value (PV), 308, 311–312, 323–324Presidents’ Day, calculating date of, 173principal

additional payments, 350amortization schedules, 348calculating periodic payment amount for

loan, 313general discussion, 308PPMT function, 309

printing error values, 606Private element, Function procedure

declarations, 647Private keyword, Function procedures, 650PRN files, 423, 447–448procedures

general discussion, 3macro recorder, 641VBA code, 639

profitability ratios, 364–365programming, VBA code

arrays, 676–677assignment statements, 674–675built-in functions, 677–679comments, 668controlling execution, 679–688example Function procedure, 666–668overview, 665ranges, 689–699

Project window, VB Editor, 634–637projects, VB Editor, 635–636, 642PROPER function, 106–107, 136–137, 433–434Properties window, VB Editor, 634Protect Sheet dialog box, 31protection options, 30–32, 55Public element, Function procedure declarations, 647


Index822

dots and colons as reference operators, 109editing array formula, 379–380expanding or contracting multicell array

formula, 379–380finding particular occurrence of value in, 402forcing to accept only text, 578Function procedures with indefinite number of,

746–747general discussion, 3INDIRECT function with named, 96–97inserting text file into, 424–426intersection operator, 78IRR function, 334LASTINCOLUMN and LASTINROW functions, VBA

code, 730maintaining names, 83–84named, 62, 67–73performing operations on array, 383programming, VBA code, 689–699randomizing, 739–741referencing data within table, 264referencing single cell in multicell named, 80returning list of unique items in, 414–415returning location of maximum value in,

401–402returning longest text in, 402–403returning nonblank cells from, 413returning only positive values from, 412–413reversing order of cells in, 413–414selecting array formula, 379SERIES formula, 451–452sorting dynamically, 414–415source, 50, 52source data for pivot tables, 517summing all cells in, 209–210summing every nth value in, 407–408summing largest values in, 397–398summing those containing errors, 211, 396summing three smallest values in, 388–389summing values based on different, 215transposing array stored in, 385two-column lookups, 237unapplying names, 82

range argumentCOUNTIF function, 194SUMIF function, 214

range operator (:)general discussion, 40names, 80

Range property, VBA code, 690–691range references

cell references and, 46–50converting to arrays, 452in named formulas, 91SERIES formula, 451–452using array instead of, 393

range_lookup argumentHLOOKUP function, 225VLOOKUP function, 224

RANGERANDOMIZE function, VBA code, 739–741ranges

#NAME? error value, 608–609activating named, 69applying name to existing formulas, 80–81charts using data in specific, 491copying or moving, 50counting characters in, 387–388counting error values in, 193, 397counting occurrences of specific text, 201counting text cells in, 389–391counting unique values, 201–203counting words in, 726creating array constant from, 382creating array from, 381–382creating names from text in adjacent cells,

70–71creating table from empty, 248criteria, 269, 271–273cutting and pasting, 84data validation, 576–577determining address of value within, 238determining closest value in, 409determining whether valid values contained in,

403–404determining whether value appears in, 399–400displaying calendar in, 416–418


Index 823

Refers to property, 66–67, 87–88refreshing, pivot tables, 503relationships

among cells, 620–622among tables, 538–540

relative referencesconditional formatting, 558–559data validation, 576debugging, 611general discussion, 46mixed and absolute references versus, 46–47with named formulas, 92–96sheets, 733–734

Remove All button, Subtotal dialog box, 280Remove Duplicates dialog box, 251–253, 426–427REMOVESPACES function, 652–654, 666–668Replace Current Subtotals check box, Subtotal

dialog box, 280REPLACE function, 138–140Replace function, VBA code, 678report filters, 513–515Report Layout control, PivotTable Styles dialog

box, 505REPT function, 134–135Require Variable Declaration option, VB Editor

Options dialog box, 672reserved words, VBA code, 669resize handles, 250result_vector argument, LOOKUP function,

226–227retained earnings, 359Return on Assets ratio, 364Return on Equity ratio, 365revenue, 360–361reverse pivot tables, 499REVERSETEXT function

calling function from Sub procedure, 659VBA code, 718, 734–735

Ribbon interfaceadding commands, 15, 19–20contextual tabs, 16customizing, 19–20dialog box launchers, 16

unlinking chart series from data, 452–453viewing named, 82–83

rate argument, financial functions, 309RATE function, 309, 311, 366rates of return, 335–340ratios, financial

asset use, 363–364liquidity, 362–363overview, 361–362profitability, 364–365solvency, 364

recalculatingformulas, 45VBA custom functions, 713

Recommended PivotTables dialog box, 499–500rectangles, measuring, 297rectangular solids, measuring, 299ReDim statement, VBA code, 677Redo command, 22Reference functions, 118reference operators, 40–41, 43references

calculated columns, 262cell, 46–50, 155, 533–535, 576–577circular, 57–58, 622–623computed criteria, 277conditional formatting, 558–559to data within tables, 263–267debugging, 611dots and colons as range reference operators, 109erroneous, 86–87errors, 602invalid, 82locating Function procedures, 648–649megaformulas, 597–598mixed, 442names, 63–64range, 46–50, 393range, VBA code, 690–692relative, 92–96, 733–734structured, 246

References dialog box, 649References node, VB (Visual Basic) Editor, 635


Index824

counting in table, 265criteria range, 269deleting, 84, 251duplicate, 426–428filtering tables, 256–257finding particular occurrence of value in

range, 402hidden, 260, 264implicit intersections, 78–79inserting within named range, 83–84naming, 71–72plotting every nth data point, 463–465randomizing, 439removing duplicate, 251–253returning last nonempty cell in, 730–731returning last value in, 411returning location of maximum value in range,

401–402selecting, 249shading groups, 562–563two-way lookups, 237VBA EntireRow property, 695–696VBA Hidden property, 696

RSQ function, 483R-squared values, 479–480, 483Rule of 72, 322–323running totals, 211runtime errors

calling function from Sub procedure, 659–660VBA code, 655–656

Ssales commissions, 715–717salvage argument, depreciation functions, 341Sample box, Format Cells dialog box, 783Save As dialog box, 447–448scaling values, 777–781scatter charts, 468–469, 473–478Scenario Manager, 29schedules, financial

amortization, 346–352data tables, 353–357financial ratios, 361–365financial statements, 357–361

Ribbon interface (continued)formatting numbers, 770–771galleries and Live Preview, 16navigating, 16number-formatting buttons, 771overview, 14–15resizing, 15tabs, groups, and tools, 15

RIGHT function, 137–138right triangles, 294–296Rnd function, VBA code, 712roman numerals, 176ROUND function, 288–289, 407, 615ROUNDDOWN function, 288–289rounding

avoiding when entering credit card numbers, 595

to even or odd integer, 293floating-point number errors, 614–615general discussion, 287–294INT and TRUNC functions, 292–293to number of significant digits, 293–294summing rounded values, 406–407

ROUNDUP function, 288–289row absolute references, 46ROW function

alternate-row shading, 560general discussion, 102generating array of consecutive integers,

386–387summing digits of integer, 404–405

row labels, pivot tables, 504, 507row_index_num argument, HLOOKUP

function, 225row_num argument, INDEX function, 228ROWOFLARGEST function, VBA code, 684–685rows

adding new, 250as arguments, 109–110changing vertical data to horizontal data,

440–442combining references with column

references, 266converting to columns, 384–385


Index 825

shapes, 25SharePoint, 246SHEET function, 704SHEETNAME function, VBA code, 704SHEETOFFSET function, VBA code, 733–734sheets

chart, 13counting those in workbook, 725–726dialog, 14general discussion, 12macro, 14multisheet names, 73–75name problems, 84–87referencing cells in other, 49–50referencing names, 63–64relative references, 733–734worksheets, 12–13

Short Date format, 151–152, 155shortcut keys, formatting numbers, 771shortcut menus

general discussion, 17–18VB Editor, 634

shortcutsgeneral discussion, 20names, 67–73navigating Ribbon, 16recalculating formulas, 45selecting objects and cells, 21

Show group, PivotTable Tools dialog box, 505Show Values As tab, Value Field Settings dialog

box, 506simple interest, 318–319SIMPLESUM function, VBA code, 743–747simultaneous equations, 300–301SIN function, 469–470single data point charts, 455–458Single data type, 670single-cell array formulas

computing average excluding zeros, 398–399counting characters in range, 387–388counting error values in range, 397counting text cells in range, 389–391determining closest value in range, 409determining whether range contains valid

values, 403–404

general discussion, 345indices, 365–366overview, 345

Scientific format, 770–772scopes, 63–66, 672scoring tests, 233–234ScreenTips, 112scrolling, synchronized, 619SEARCH function, 139–140, 200–201Search in Value option, Find and Replace dialog

box, 769SECOND function, 176second order polynomial trendlines, 486security features, 630–633Security Notice dialog box, 631Security Warning, 630–631Select a Function list box, Insert Function dialog

box, 114Select Arguments dialog box, 117Select Case constructs, VBA code, 681–682, 716Selection Pane, 86semantic errors, 602semicolons (;), in arrays, 374–375sentence case, 138separator argument, VBA EXTRACTELEMENT

function, 723separator characters, 723, 771, 775serial numbers

dates, 150, 727times, 153–154

SERIES formulacharts with dynamic names, 208–209choosing start date and number of points, 489names, 83, 451–452overview, 449–450pivot chart, 544plotting last n data points, 488unlinking chart series from data range, 452–453

Series Overlap value, 459series_name argument, SERIES formula, 450Set keyword, VBA code, 696Set Precision as Displayed check box, Excel Options

dialog box, 614shading, 560–563


Index826

spacescells containing only, 604–605in character counts, 133general discussion, 36–37intersection operator, 40, 77–79, 81nonbreaking, 435number formatting codes, 775removing extra, 133, 434–435, 605removing strange characters, 435returning last character position in string,

590–594as separator characters, 723between two strings, 130VBA code, 640

Sparkline graphics, 27special characters, 127Special format, 772spell checker, 443SPELLDOLLARS function, VBA code, 724–725spheres, measuring, 298Split function, VBA code, 724splitting text, 428–433spreadsheet errors, 602spreadsheet file formats, 422spreadsheetpage.com, 445Sqr function, VBA code, 678SQRT function, 110–111SQUARE function, VBA code, 691square roots, 384, 678squares, measuring, 296–297SSR (Spreadsheet Research) website, 602standard deviations, 301–302statements

assignment, 674–675converting to comment, 668executing multiple in Immediate window, 661financial, 357–361VBA code, 639–640, 652–653

Statements element, Function procedure declarations, 647

STATFUNCTION function, VBA code, 709–711STATFUNCTION2 function, VBA code, 711–712static charts, 452–453

single-cell array formulas (continued)determining whether value appears in range,

399–400eliminating intermediate formulas, 391–392examples, 395finding row of particular occurrence of value in

range, 402general discussion, 369–372overview, 387removing nonnumeric characters from

string, 409returning last value in column, 410returning last value in row, 411returning location of maximum value in range,

401–402returning longest text in range, 402–403summing digits of integer, 404–407summing every nth value in range, 407–408summing largest values in range, 397–398summing range containing errors, 396summing rounded values, 406–407summing three smallest values in range,

388–389using array instead of range reference, 393

sizes argument, SERIES formula, 451slash character (/), 770, 775Slicers, 257–258, 466, 531–532, 540–541sliding scales, 715–717SLN function, 341–343slope, 481SMALL function, 213, 388–389, 398solvency ratios, 364Solver add-in, 30Sort dialog box, 255sorting

custom sort list, 537pivot tables, 537pre-1900 dates, 158range of values dynamically, 414–415tables, 253–256, 280

Source control, Data Validation dialog box, 575source data, pivot tables, 504, 517source ranges, 50, 52


Index 827

SUBSTITUTE function, 138–141, 148, 444, 668substrings, 141Subtotal command, 279–282Subtotal control, PivotTable Styles dialog box, 505Subtotal dialog box, 279–280SUBTOTAL function

automatically inserting, 115counting visible data rows, 265–266general discussion, 188inserting subtotals, 279–281selecting summary function for column, 260

subtotals, pivot tables, 504–505, 517subtraction (-) operator

calculating difference between times, 178–179calculating number of days between two

dates, 163general discussion, 39versus negation operator, 42precedence, 43, 612–613

SUM formula, 596SUM function

actual versus displayed values, 613–614array constants, 373automatically inserting, 115Boolean values in array formula, 391counting characters in range, 387–388counting text cells in range, 390determining whether value appears in

range, 400displaying totals only when all values

entered, 563Function procedures with indefinite number of

arguments, 742–747general discussion, 188logical value errors, 616–617single-cell array formulas, 372summing all cells in range, 209–210summing digits of integer, 405summing largest values in range, 397–398summing range containing errors, 396summing three smallest values in range, 389summing top n values, 213

sum of year’s digits depreciation, 341–343Sum option, Total row drop-down list, 259

Static element, Function procedure declarations, 647static schedules, 345STATICRAND function, VBA code, 712–713Statistical category, worksheet functions, 763–766Statistical functions, 118status bar, 21, 189StdDev option, Total row drop-down list, 259Step values, VBA code, 683–684straight-line depreciation, 341–343String data type, 670strings

argument descriptions, 654converting to formulas, 76converting to value, 706counting characters in, 133–134determining whether identical, 129extracting all but first word, 145extracting characters, 137–138extracting first word, 144extracting last word, 144–145extracting nth element, 723–724finding and searching for text within, 139–140general discussion, 34, 673number formats, 774–775removing nonnumeric characters, 409removing spaces at end, 434–435repeating, 134returning address of range as, 693returning error value if not contained in

function, 734returning last space character position, 590–594reversing, 718searching and replacing within, 140splitting without using formulas, 146VBA Replace function, 678

StrReverse function, VBA code, 718structured references, 246Style Gallery, 770stylistic formatting, 23–24Sub procedures

argument descriptions, 655calling function from, 659–660function categories, 652VBA code, 639


Index828

Ttable filters, pivot tables, 504TABLE function, 354, 356Table Styles group, Ribbon interface, 248Table Tools contextual tab, Ribbon interface,

245, 247table_array argument

HLOOKUP function, 225VLOOKUP function, 224

tables. See also pivot tablesadding rows or columns, 250advanced filtering, 267–277automatically inserting functions, 115choosing among multiple lookup, 233converting to list, 267creating pivot tables with Data Model feature,

538–539customizing appearance, 248–249data, 353–357database functions, 277–279deleting rows or columns, 251example, 244–246formulas within, 261–263general discussion, 24, 221limitations, 246lists versus, 245–246lookup, 393, 437–438moving, 251multicell array formulas, 380navigating and selecting in, 249–250new, 247–248overview, 243pivot, 281–282referencing data within, 263–267removing duplicate rows, 251–253sorting and filtering, 253–258Subtotal dialog box, 280terminology, 243–244Total rows, 258–261

tabscontextual, 16Ribbon interface, 15

task panes, 20–21

sum_range argument, SUMIF function, 214Sum12Cells formula, 94–95SUMIF function, 188, 214–215, 350SUMIFS function, 188, 217–219Summarize Values By tab, Value Field Settings

dialog box, 506summarizing data, 495, 511Summary below Data check box, Subtotal dialog

box, 280summary formulas, 259–260, 347, 349–350summary rows, 245summary schedules, 345summary tables, 499summing formulas

all cells in range, 209–210all values in table, 263–264conditional sums with multiple criteria,

216–219conditional sums with single criterion,

214–216cumulative sums, 211–212general discussion, 187–188overview, 187, 209–210ranges containing errors, 211top n values, 213VBA code, 683–684

SUMOFSQUARES function, VBA code, 689SUMPRODUCT function

counting cells meeting multiple criteria, 197general discussion, 188single-cell array formulas, 372

Sundays, determining date of most recent, 169surface charts, 471–473surfaces, of shapes, 298–299SYD function, 341–343SYLK (Symbolic Link) files, 423, 448Symbol dialog box, 127symbols

in names, 68number formats, 787

synchronized scrolling, 619syntax errors

general discussion, 602–603VBA code, 640, 655, 669


Index 829

extracting last word of string, 144–145extracting nth element in string, 723–724finding and searching within string, 139–140forcing cell or range to accept only, 578joining multiple cells, 130–131linked text boxes, 454–455matching in list, 439–440numbers as, 124–125overview, 123, 718padding numbers, 135–136removing excess spaces and nonprinting

characters, 133removing from cell, 444removing titles from names, 148removing trailing minus signs, 142repeating character or string, 134replacing, 138–139, 444returning acronym, 719returning longest in range, 402–403reversing string, 718scrambling, 718–719searching and replacing within string, 140shortening, 134spelling out numbers, 724–725splitting, 428–433splitting strings without using formulas, 146summing values based on comparison, 216transformed, 137, 434

text boxes, linked, 454–455Text category, worksheet functions, 766–767text cells, 389–391text concatenation operator (&)

general discussion, 39precedence, 43

text constants, 89–90text file formats

exporting data to, 447–448general discussion, 423inserting into specified range, 424–426

Text format, 772TEXT function

converting military time, 182displaying values as text, 131–133formatting numbers, 787

telephone numbers, 784Temp variable, VBA code, 667terminal values, 332–333terms, for interest, 308, 320test scores, 233–234testing functions

calling from Sub procedure, 659–660Debug.Print statements, 658overview, 655setting breakpoint in function, 661–662VBA MsgBox statement, 656–657

textadding in cells, 445–446changing case, 136–137, 433–434character codes, 126–127characters in cells, 123checking for specific character, 579comparison operators, 272–273copying from formulas, 586counting characters in string, 133–134counting number of words in cell, 148counting occurrences of specific, 199–201counting occurrences of substring in cell, 141counting specific characters within cell, 141counting text cells, 192creating names from adjacent cells, 70–71determining column letter for column

number, 143determining if cell contains, 126, 722–723determining if cell contains particular word,

721–722determining if pattern is matched, 720–721determining whether strings are identical, 129displaying in quotes, 783displaying N/A for, 782displaying values as, 131–133displaying with numbers, 786displaying without values, 787expressing number as ordinal, 142–143extracting all but first word of string, 145extracting characters from string, 137–138extracting filename from path specification, 144extracting first, middle, and last names, 145–147extracting first word of string, 144


Index830

combining dates and, 154–155, 178converting between time zones, 183–184converting decimal hours, minutes, or seconds

to, 182converting military, 182custom formats, 785Date data type, 674displaying any, 177–178displaying current, 176–177entering, 154–155exceeding 24 hours, 155formatting, 155–157functions related to, 176general discussion, 149grouping items in pivot tables by, 522invalid, 604negative values, 150non-time-of-day values, 185–186number formatting codes, 776overview, 149rounding values, 184–185serial numbers, 153–154summing series exceeding 24 hours,

179–181units of, 153

Times Interest Earned Ratio, 364TIMEVALUE function

converting military time, 182displaying any time, 178general discussion, 176

titleslinked, 453–455removing from names, 148

TODAY functiondisplaying current date, 160general discussion, 90, 159volatile functions, 118

toolbarsmini, 17–18VB Editor, 634

tools, Ribbon interface, 15Top Bottom Rules command, Conditional

Formatting drop-down list, 547Total Row control, Ribbon interface, 249

Text functions, 119text histograms

Analysis ToolPak, 206–207creating with adjustable bins, 208–209creating with REPT function, 134–135formulas, 205–206FREQUENCY function, 203–205pivot tables, 208, 522–524

Text Import Wizard, 423Text Length option, Data Validation dialog box, 573text strings, 34Text to Columns method, 429–430Text Tools, Power Utility Pak add-in, 445Thanksgiving Day, calculating date of, 174themes

customizing table appearance, 249data bars, 550general discussion, 24pivot tables and pivot charts, 544

Then clause, VBA code, 681third order polynomial trendlines, 486thousands, displaying values in, 777–7783 Arrows icon set, 556–557tildes (~), 140Time category, Format Cells dialog box, 157Time format, 770–772TIME function, 176–178, 183time functions

general discussion, 117VBA code, 679–680

Time option, Data Validation dialog box, 573time stamps, 177time value, of money, 307–308time zones, 183–184timelines

circle plotting, 473–475clock charts, 475–477designs, 477–478filtering pivot table, 532–533overview, 468–469plotting mathematical functions, 469–473

timesadding hours, minutes, or seconds to, 183calculating difference between, 178–179


Index 831

21st Century dates, 158two-dimensional arrays

general discussion, 369, 375–377transposing, 385

two-way data tables, 355–357txt argument, VBA EXTRACTELEMENT function, 723TXT files

exporting data to, 447–448general discussion, 423

type argument, financial functions, 309Type element, Function procedure

declarations, 647TYPE function, 126TypeName function, VBA code, 714, 746

UUDF. See user-defined functions UI (user interface)

Backstage View, 16–17customizing, 19–20dialog boxes, 18–19keyboard shortcuts, 20onscreen display, 21overview, 14Ribbon, 14–16selecting objects and cells, 21shortcut menus and mini toolbar, 17–18task panes, 20

Uncomment Block button, VB Editor, 668underscore character (_)

function names, 647number formatting codes, 776

Undo command, 22Unicode character set, 126, 448Union function, VBA code, 698union operator (,), 40unit conversions

cleaning data, 436general discussion, 283–287

UpdateClock procedure, 477UPPER function, 136–137, 433–434URLs (Uniform Resource Locators), 433Use a Formula to Determine Which Cells to Format

rule type, 549

Total rowsadding new row to column, 250general discussion, 258–261referring to cell within, 265summarizing values in table column, 261–263

totalsaccepting only values not exceeding, 580displaying only when all values entered, 563

tracer arrows, 58tracing cell relationships, 620–622tracing error values, 622trailing minus signs (-), 142, 446trailing spaces, 133, 434–435, 605TRANPOSE function, 408transforming data, 137, 434Transition Formula Entry option, Excel Options

dialog box, 82TRANSPOSE function

inserting horizontal array into vertical range of cells, 378

transposing array, 384–385, 736–737trapezoids, measuring, 298TREND function, 242, 482trendlines

equations, 485–486linear, 479–483nonlinear, 483–485overview, 478

trial balances, 357–360triangles

calculating area and perimeter, 298right, 294–296

trigonometric functionsclock charts, 475–476general discussion, 117solving right triangles, 294–296

TRIM functioncounting number of words in cell, 148removing extra spaces, 133, 435, 605

TRUE value, 391TRUNC function, 288, 292–293Trust Center dialog box, 630–632trusted folders, 63220th Century dates, 158


Index832

conditional colors, 458–459constants, 90converting, 436converting formulas to, 53–55converting nonnumber data to, 124counting logical, 193counting most frequently occurring, 198–199counting unique, 201–203creating array constant from, 382creating multicell array formula from, 381–382declaring as constant, 673determining address within range, 238determining closest in range, 409determining if pattern is matched, 720determining maximum across multiple

worksheets, 731–733determining whether range contains valid,

403–404determining whether value appears in range,

399–400displaying as text, 131–133displaying multiple, 531displaying text with numbers, 786displaying totals only when all entered, 563displaying without text or zeros, 787duplicate, 252duplicate rows, 427–428error, 605–611, 622filling in missing, 266–267filtering tables, 256finding particular occurrence in range, 402formatting versus rounding, 287general discussion, 34goal seeking, 58–60highlighting rows based on, 560–561identifying maximum and minimum in chart,

466–468initial, 333logical, 210, 616–617looking up by closest match, 239–240looking up exact, 230–231looking up those to left, 231–232looking up those with linear interpolation,

240–242

Use Function drop-down list, Subtotal dialog box, 279–280

Use in Formula command, 77Use Row and Column Names check box, Apply

Names dialog box, 81UsedRange property, VBA code, 698–699USER function, 644–646user input, amortization schedules, 347, 349user interface. See UI user-defined functions (UDFs)

add-ins for functions, 662argument descriptions, 654–655custom functions, 643–644declaring function, 646–647examples, 644–646, 666–668function arguments, 650function categories, 652–654function descriptions, 650–652functions in formulas, 648–649Insert Function dialog box, 650–655naming function, 647–648overview, 643passive, 646testing and debugging functions, 655–662

UserForm objects, VB Editor, 638UserName property, VBA code, 641–642

Vvalidation, of data

cell references, 576–577criteria, 572–574drop-down lists, 574–575formulas, 576, 578–581general discussion, 571–572identifying invalid data, 566–567

Value Field Settings dialog box, 506–507values

accepting larger than previous cell, 578accepting only those not exceeding total, 580actual versus displayed, 613–614bin ranges, 204calculating predicted, 481–482classifying, 436–438comparison operators, 272–273


Index 833

displaying list of VBA functions, 677–678entering VBA code, 639–642Help system, 635Immediate window, 634menu bar, 634overview, 632Project window, 634–637Properties window, 634saving projects, 642shortcut menus, 634toolbars, 634

VBA (Visual Basic for Applications)Developer tab, Ribbon interface, 630general discussion, 629–630macro security, 630–632overview, 629saving workbooks containing macros, 632VB Editor, 632–642

VBA code. See also Function procedures; programming, VBA code

custom functions in conditional formatting formulas, 564–567

displaying number format for specified cell, 788entering, 639–642general discussion, 3logical value errors, 616–617macros, 6recalculating formulas, 613storing, 639

VBA custom functionscalculating sales commissions, 715–717CELLFORMULA, 702–703CELLHASFORMULA, 702CELLISHIDDEN, 703counting, 725–726dates, 726–729determining cell’s data type, 708–709generating random numbers, 712–715with indefinite number of arguments, 742–747multifunctional, 709–712multisheet, 731–734optional arguments, 740–742overview, 701randomizing range, 739–741

monitoring as Function procedure executes, 656–657

monitoring inside loop, 658negative, 460, 783non-time-of-day, 185–186returning last in column, 410returning last in row, 411returning location of maximum in range,

401–402returning only positive from range, 412–413rounding, 184–185, 287–294scaling, 777–781sorting range dynamically, 414–415summing based on comparison, 216summing every nth value in range, 407–408summing largest in range, 397–398summing negative, 215summing rounded, 406–407summing those based on different range, 215summing three smallest in range, 388–389summing top n, 213terminal, 332–333totaling in pivot table, 504

Values area, pivot tables, 504, 526–527values argument, SERIES formula, 451Var option, Total row drop-down list, 259variable-declining balance depreciation, 341–343variable-length strings, 673variables

dates and times, 674declaring, 666general discussion, 670–672misspelled names, 670monitoring value as Function procedure

executes, 656–657object, 696overview, 669simultaneous equations, 300VBA code, 640

Variant data type, 670–671, 746VARIANT_DEMO Function procedure, 671VB (Visual Basic) Editor

activating, 633code windows, 634, 637–639


Index834

Wweather data, 490–492Web category, worksheet functions, 766–767Web functions, 121Web Page (HTM) files, 448websites, 6WEEKDAY function

accepting dates by day of week, 580determining day of week, 168general discussion, 159

WeekDay function, VBA code, 727weekend days, identifying, 560WEEKNUM function, 159, 168Whole Number option, Data Validation dialog

box, 573wildcard characters

accepting text beginning with specific character, 579

filtering table by single criterion, 272–273general discussion, 140ISLIKE function, VBA code, 720lookup functions, 224

windowshiding, 12viewing multiple, 619

Windows Clipboard, 51WORDCOUNT function, VBA code, 726words

counting number in cell, 148counting those in range, 726extracting from string, 144–145

words that contain X rule, 546workbook-level names

general discussion, 63problems when copying sheets, 84–85

WORKBOOKNAME function, VBA code, 704–705workbooks

chart sheets, 13counting sheets, 725–726creating add-in from, 663date systems in linked, 150names in SERIES formula, 451–452online, 6

VBA custom functions (continued)recalculating, 713returning Application object name, 705returning array from function, 735–737returning array of nonduplicated random

integers, 737–739returning error value, 734–735returning Excel version name, 705–706returning formatting information, 706–707returning last nonempty cell in column or row,

730–731SHEETNAME, 704text manipulation, 718–725WORKBOOKNAME, 704–705

VBA modules, 632, 636–637VBA window, 631VBAProject default project name, 649vbNewLine constant, VBA code, 657VDB function, 341–343version numbers, 705–706vertical arrays

converting to horizontal array, 384–385general discussion, 375naming array constants, 378

vertical data, 440–442Veterans Day, calculating date of, 174Visual Basic Editor. See VB EditorVLOOKUP function

charts using data in specific range, 491choosing among multiple lookup tables, 233classifying values, 437letter grades for test scores, 234looking up exact values, 230–231looking up value by closest match, 239lookup formulas, 221–225

volatile functions, 118Volatile method, VBA code, 713volume

of cone, 299of cube, 298of cylinder, 299of pyramid, 299of rectangular solid, 299of sphere, 298


Index 835

worksheetsapplying name to all formulas in, 81databases, 29determining maximum value across multiple,

731–733determining number of nonempty cells, 691eliminating intermediate formulas, 391–392filling gaps in imported report, 442–443formulas and functions, 24–25functions, 34, 387general discussion, 12–13moving pivot chart between, 544moving table to, 251multisheet names, 73–75name problems, 84–87naming entire rows or columns, 71–72Parent property, VBA code, 694protecting, 30–31referencing cells in other, 49–50returning name with VBA SHEETNAME

function, 704returning used range, 698–699size of, 12–13zooming, 82–83

XXDATEADD(xdate1,days,fmt) function, VBA

code, 728XDATEDAY(xdate1) function, VBA code, 729XDATEDIF(xdate1,xdate2) function, VBA code, 728XDATEDOW(xdate1) function, VBA code, 729XDATEMONTH(xdate1) function, VBA code, 729XDATE(y,m,d,fmt) function, VBA code, 728XDATEYEARDIF(xdate1,xdate2) function, VBA

code, 729XDATEYEAR(xdate1) function, VBA code, 729XIRR function, 339–340XLA files, 422XLM files, 422XLM macros, 101–102XLS files, 422XLSM (Excel Macro-Enabled Workbook) file

format, 632XLT files, 422

overview, 12password protection, 32protecting structure, 31references to, 649referencing cells in other, 49–50referencing names from other, 64returning name with VBA WORKBOOKNAME

function, 704–705saving those containing macros, 632volatile functions, 118worksheets, 12–13

WORKDAY function, 159, 165WORKDAY.INTL function, 159, 165working days, 164–165worksheet databases, 118worksheet functions

arguments, 108–111automatically inserting, 115categories, 117–121Compatibility category, 751–752Cube category, 753custom, 636data types, 711Database category, 753Date & Time category, 754Engineering category, 755–756entering into formulas, 112–117Financial category, 755–758general discussion, 105–108Information category, 758–759Logical category, 759Lookup & Reference category, 760Math & Trig category, 760–762in named formulas, 90–91overview, 105Statistical category, 763–766Text category, 766–767in VBA code, 678–679Web category, 766–767

worksheet-level namesgeneral discussion, 63with Name box, 68–69problems with, 84–85referencing with INDIRECT function, 97


Index836

ZZero option, Hidden and Empty Cell Settings

dialog box, 462–463zeros (0)

#DIV/0! error value, 607–608appending to value, 780–781close-to-zero rounding errors, 615computing average excluding, 398–399displaying everything except, 788displaying items with no data in pivot

table, 523displaying leading, 781displaying with dashes, 786–787distinguishing from blank cells, 229hiding, 781number formatting codes, 775

ZIP codes, 784.zip file extension, 12zooming worksheets

general discussion, 82–83locating particular type of cell, 618

XML (eXtensible Markup Language) files, 424XNPV function, 338–339Xor operator, VBA code, 675XPS (XML Paper Specification Document) files, 448XY charts, 468–469, 473–478

YYEAR function

calculating number of years between two dates, 166

general discussion, 159YEARFRAC function

calculating loans with irregular payments, 318calculating number of years between two

dates, 166calculating person’s age, 166general discussion, 159

yearscalculating number between two dates, 165–166converting to roman numerals, 176determining day of, 166–168determining week of, 168–169determining whether leap year, 175problems with entering dates, 157–158

y-intercept, 481


` (accent grave) key, 619 copyrighted material

Documents