Formulas and Functions: Microsoft Excel 2010

Introduction ............................................................................................................................................................................................ 1What’s in the Book ............................................................................................................................................................................. 2This Book’s Special Features ......................................................................................................................................................... 2I MASTERING EXCEL RANGES AND FORMULAS1 Getting the Most Out of Ranges ............................................................................................................................... 5Advanced Range-Selection Techniques................................................................................................................................. 5Mouse Range-Selection Tricks ............................................................................................................................................ 6Keyboard Range-Selection Tricks ..................................................................................................................................... 7Working with 3D Ranges ....................................................................................................................................................... 7Selecting a Range Using Go To ........................................................................................................................................... 8Using the Go To Special Dialog Box ................................................................................................................................. 9Data Entry in a Range .................................................................................................................................................................... 13Filling a Range ................................................................................................................................................................................... 14Using the Fill Handle ...................................................................................................................................................................... 14Using AutoFill to Create Text and Numeric Series ................................................................................................ 14Creating a Custom AutoFill List ....................................................................................................................................... 16Filling a Range........................................................................................................................................................................... 17Creating a Series ............................................................................................................................................................................... 17Advanced Range Copying ........................................................................................................................................................... 18Copying Selected Cell Attributes ............................................................................................................................................. 19Combining the Source and Destination Arithmetically ..................................................................................... 20Transposing Rows and Columns .................................................................................................................................... 21Clearing a Range ............................................................................................................................................................................... 22Applying Conditional Formatting to a Range .................................................................................................................. 22Creating Highlight Cells Rules ......................................................................................................................................... 22Creating Top/Bottom Rules ............................................................................................................................................... 24Adding Data Bars ..................................................................................................................................................................... 26Adding Color Scales ................................................................................................................................................................ 28Adding Icon Sets ...................................................................................................................................................................... 31From Here .................................................................................................................................................................................... 322 Using Range Names ............................................................................................................................................................ 33Defining a Range Name ............................................................................................................................................................... 34Working with the Name Box ............................................................................................................................................ 34Using the New Name Dialog Box ................................................................................................................................... 35Changing the Scope to Define Sheet-Level Names ............................................................................................. 37Using Worksheet Text to Define Names ..................................................................................................................... 37Naming Constants .................................................................................................................................................................. 39Working with Range Names ..................................................................................................................................................... 41Referring to a Range Name ............................................................................................................................................... 41Working with Name AutoComplete............................................................................................................................. 43Navigating Using Range Names ..................................................................................................................................... 43Pasting a List of Range Names in a Worksheet...................................................................................................... 44Displaying the Name Manager ....................................................................................................................................... 44Filtering Names ........................................................................................................................................................................ 44Editing a Range Name’s Coordinates ........................................................................................................................... 45Adjusting Range Name Coordinates Automatically ............................................................................................ 45Changing a Range Name .................................................................................................................................................... 47Deleting a Range Name ....................................................................................................................................................... 47Using Names with the Intersection Operator.......................................................................................................... 47From Here .................................................................................................................................................................................... 493 Building Basic Formulas................................................................................................................................................. 51Understanding Formula Basics ................................................................................................................................................ 51Formula Limits in Excel 2007 and Excel 2010 ......................................................................................................... 52Entering and Editing Formulas ....................................................................................................................................... 52Using Arithmetic Formulas ................................................................................................................................................ 53Using Comparison Formulas ............................................................................................................................................. 54Using Text Formulas .............................................................................................................................................................. 54Using Reference Formulas ................................................................................................................................................. 55Understanding Operator Precedence ................................................................................................................................... 55The Order of Precedence ..................................................................................................................................................... 55Controlling the Order of Precedence ............................................................................................................................ 56Controlling Worksheet Calculation ........................................................................................................................................ 58Copying and Moving Formulas ................................................................................................................................................ 59Understanding Relative Reference Format............................................................................................................... 60Understanding Absolute Reference Format ............................................................................................................. 62Copying a Formula Without Adjusting Relative References .......................................................................... 63Displaying Worksheet Formulas ............................................................................................................................................. 63Converting a Formula to a Value ............................................................................................................................................ 63Working with Range Names in Formulas ........................................................................................................................... 64Pasting a Name into a Formula ...................................................................................................................................... 64Applying Names to Formulas ........................................................................................................................................... 65Naming Formulas .................................................................................................................................................................... 68Working with Links in Formulas ............................................................................................................................................. 69Understanding External References .............................................................................................................................. 69Updating Links .......................................................................................................................................................................... 71Changing the Link Source .................................................................................................................................................. 72Formatting Numbers, Dates, and Times ............................................................................................................................. 72Numeric Display Formats .................................................................................................................................................... 72Date and Time Display Formats ...................................................................................................................................... 80Deleting Custom Formats ................................................................................................................................................... 83From Here .................................................................................................................................................................................... 834 Creating Advanced Formulas ................................................................................................................................... 85Working with Arrays ...................................................................................................................................................................... 85Using Array Formulas ............................................................................................................................................................ 86Understanding Array Formulas ................................................................................................................................................ 87Array Formulas That Operate on Multiple Ranges ............................................................................................... 88Using Array Constants ................................................................................................................................................................... 89Functions That Use or Return Arrays ............................................................................................................................ 90Using Iteration and Circular References .............................................................................................................................. 91Consolidating Multisheet Data ................................................................................................................................................ 93Consolidating by Position ................................................................................................................................................... 93Consolidating by Category ................................................................................................................................................. 97Applying Data-Validation Rules to Cells .............................................................................................................................. 98Using Dialog Box Controls on a Worksheet .................................................................................................................... 101Displaying the Developer Tab ....................................................................................................................................... 101Using the Form Controls .................................................................................................................................................. 101Adding a Control to a Worksheet ............................................................................................................................... 101Linking a Control to a Cell Value ................................................................................................................................. 102Understanding the Worksheet Controls .................................................................................................................. 103From Here ................................................................................................................................................................................. 1085 Troubleshooting Formulas ...................................................................................................................................... 109Understanding Excel’s Error Values .................................................................................................................................... 110#DIV/0! ................................................................................................................................................................................... 110#N/A ............................................................................................................................................................................................ 111#NAME? ...................................................................................................................................................................................... 111Case Study: Avoiding #NAME? Errors When Deleting Range Names ..................................................... 112#NULL! ...................................................................................................................................................................................... 113#NUM! ......................................................................................................................................................................................... 113#REF! ......................................................................................................................................................................................... 113#VALUE! ................................................................................................................................................................................... 114Fixing Other Formula Errors .................................................................................................................................................... 114Missing or Mismatched Parentheses ......................................................................................................................... 114Erroneous Formula Results ............................................................................................................................................. 115Fixing Circular References ............................................................................................................................................... 116Handling Formula Errors with IFERROR() ................................................................................................................... 117Using the Formula Error Checker ......................................................................................................................................... 118Choosing an Error Action ................................................................................................................................................. 119Setting Error Checker Options ....................................................................................................................................... 119Auditing a Worksheet................................................................................................................................................................. 122Understanding Auditing .................................................................................................................................................. 123Tracing Cell Precedents ..................................................................................................................................................... 123Tracing Cell Dependents .................................................................................................................................................. 124Tracing Cell Errors ................................................................................................................................................................. 124Removing Tracer Arrows .................................................................................................................................................. 124Evaluating Formulas ........................................................................................................................................................... 124Watching Cell Values.......................................................................................................................................................... 125From Here ................................................................................................................................................................................. 126II HARNESSING THE POWER OF FUNCTIONS6 Understanding Functions ......................................................................................................................................... 127About Excel’s Functions ............................................................................................................................................................. 128The Structure of a Function ..................................................................................................................................................... 128Typing a Function into a Formula ....................................................................................................................................... 130Using the Insert Function Feature ...................................................................................................................................... 131Loading the Analysis ToolPak ................................................................................................................................................ 134From Here ................................................................................................................................................................................. 1347 Working with Text Functions ............................................................................................................................... 137Excel’s Text Functions ................................................................................................................................................................. 137Working with Characters and Codes ................................................................................................................................. 137The CHAR() Function ........................................................................................................................................................ 139The CODE() Function ........................................................................................................................................................ 141Converting Text .............................................................................................................................................................................. 142The LOWER() Function ..................................................................................................................................................... 142The UPPER() Function ..................................................................................................................................................... 143The PROPER() Function.................................................................................................................................................. 143Formatting Text ............................................................................................................................................................................. 143The DOLLAR() Function.................................................................................................................................................. 144The FIXED() Function ..................................................................................................................................................... 144The TEXT() Function ........................................................................................................................................................ 145Displaying When a Workbook Was Last Updated ............................................................................................. 145Manipulating Text ........................................................................................................................................................................ 146Removing Unwanted Characters from a String ........................................................................................................... 146The TRIM() Function ........................................................................................................................................................ 146The CLEAN() Function ..................................................................................................................................................... 147The REPT() Function: Repeating a Character .................................................................................................... 147Padding a Cell ......................................................................................................................................................................... 147Building Text Charts............................................................................................................................................................ 148Extracting a Substring ................................................................................................................................................................ 149The LEFT() Function ........................................................................................................................................................ 149The RIGHT() Function ..................................................................................................................................................... 150The MID() Function ........................................................................................................................................................... 150Converting Text to Sentence Case ............................................................................................................................... 150A Date-Conversion Formula ........................................................................................................................................... 151Searching for Substrings ........................................................................................................................................................... 151The FIND() and SEARCH() Functions ................................................................................................................... 151Case Study: Generating Account Numbers ......................................................................................................................152Extracting a First Name or Last Name ...................................................................................................................... 153Extracting First Name, Last Name, and Middle Initial .................................................................................... 154Determining the Column Letter .................................................................................................................................. 154Substituting One Substring for Another.......................................................................................................................... 155The REPLACE() Function .............................................................................................................................................. 155The SUBSTITUTE() Function ..................................................................................................................................... 156Removing a Character from a String ......................................................................................................................... 156Removing Two Different Characters from a String ........................................................................................... 157Case Study: Generating Account Numbers, Part 2 ..................................................................................................... 157Removing Line Feeds ......................................................................................................................................................... 158From Here ................................................................................................................................................................................. 1588 Working with Logical and Information Functions......................................................................... 159Adding Intelligence with Logical Functions ................................................................................................................... 159Using the IF() Function ................................................................................................................................................. 160Performing Multiple Logical Tests .............................................................................................................................. 163Combining Logical Functions with Arrays .............................................................................................................. 168Case Study: Building an Accounts Receivable Aging Worksheet ...................................................................... 173Getting Data with Information Functions ....................................................................................................................... 176The CELL() Function ........................................................................................................................................................ 176The ERROR.TYPE() Function ..................................................................................................................................... 179The INFO() Function ........................................................................................................................................................ 180The IS Functions .................................................................................................................................................................... 181From Here ................................................................................................................................................................................. 1839 Working with Lookup Functions ........................................................................................................................ 185Understanding Lookup Tables .............................................................................................................................................. 186The CHOOSE() Function .......................................................................................................................................................... 187Determining the Name of the Day of the Week ................................................................................................. 187Determining the Month of the Fiscal Year ............................................................................................................. 188Calculating Weighted Questionnaire Results ....................................................................................................... 189Integrating CHOOSE() and Worksheet Option Buttons ............................................................................... 189Looking Up Values in Tables ................................................................................................................................................... 190The VLOOKUP() Function .............................................................................................................................................. 190The HLOOKUP() Function .............................................................................................................................................. 191Returning a Customer Discount Rate with a Range Lookup ....................................................................... 192Returning a Tax Rate with a Range Lookup .......................................................................................................... 193Finding Exact Matches ...................................................................................................................................................... 193Advanced Lookup Operations ....................................................................................................................................... 195From Here ................................................................................................................................................................................. 20010 Working with Date and Time Functions.................................................................................................... 201How Excel Deals with Dates and Times ............................................................................................................................ 201Entering Dates and Times ............................................................................................................................................... 202Excel and Two-Digit Years .............................................................................................................................................. 203Using Excel’s Date Functions .................................................................................................................................................. 204Returning a Date ................................................................................................................................................................... 205Returning Parts of a Date ................................................................................................................................................ 207Calculating the Difference Between Two Dates ................................................................................................. 216Using Excel’s Time Functions ................................................................................................................................................. 220Returning a Time .................................................................................................................................................................. 220Returning Parts of a Time ............................................................................................................................................... 221Calculating the Difference Between Two Times ................................................................................................. 224Case Study: Building an Employee Time Sheer ............................................................................................................ 224From Here ................................................................................................................................................................................. 22811 Working with Math Functions ............................................................................................................................. 229Understanding Excel’s Rounding Functions ................................................................................................................... 232ROUND() Function .............................................................................................................................................................. 232MROUND() Function ........................................................................................................................................................... 233ROUNDDOWN() and ROUNDUP() Functions .......................................................................................................... 233CEILING() and FLOOR() Functions ...................................................................................................................... 234Determining the Fiscal Quarter in Which a Date Falls .................................................................................... 235Calculating Easter Dates ................................................................................................................................................... 235EVEN() and ODD() Functions..................................................................................................................................... 236INT() and TRUNC() Functions ................................................................................................................................. 236Using Rounding to Prevent Calculation Errors..................................................................................................... 237Setting Price Points ............................................................................................................................................................. 237Case Study: Rounding Billable Time .................................................................................................................................. 238Summing Values ............................................................................................................................................................................ 238SUM() Function .................................................................................................................................................................... 238Calculating Cumulative Totals ...................................................................................................................................... 239Summing Only the Positive or Negative Values in a Range ........................................................................ 240MOD() Function ............................................................................................................................................................................. 240Better Formula for Time Differences ......................................................................................................................... 241Summing Every nth Row ................................................................................................................................................ 241Determining Whether a Year Is a Leap Year ......................................................................................................... 242Creating Ledger Shading ................................................................................................................................................. 242Generating Random Numbers............................................................................................................................................... 244RAND() Function ................................................................................................................................................................. 244RANDBETWEEN() Function............................................................................................................................................ 246From Here ................................................................................................................................................................................. 24712 Working with Statistical Functions ................................................................................................................ 249Understanding Descriptive Statistics ................................................................................................................................ 249Counting Items with the COUNT() Function ................................................................................................................ 252Calculating Averages ................................................................................................................................................................... 253AVERAGE() Function ........................................................................................................................................................ 253MEDIAN() Function ........................................................................................................................................................... 253MODE() Function ................................................................................................................................................................. 254Calculating the Weighted Mean ................................................................................................................................. 254Calculating Extreme Values .................................................................................................................................................... 256MAX() and MIN() Functions ........................................................................................................................................ 256LARGE() and SMALL() Functions ............................................................................................................................ 256Performing Calculations on the Top k Values ...................................................................................................... 258Performing Calculations on the Bottom k Values ............................................................................................. 258Calculating Measures of Variation ...................................................................................................................................... 258Calculating the Range ....................................................................................................................................................... 258Calculating the Variance .................................................................................................................................................. 259Calculating the Standard Deviation ........................................................................................................................... 260Working with Frequency Distributions ............................................................................................................................. 261FREQUENCY() Function .................................................................................................................................................. 262Understanding the Normal Distribution and the NORMDIST() Function ............................................. 263Shape of the Curve I: The SKEW() Function ......................................................................................................... 264Shape of the Curve II: The KURT() Function ....................................................................................................... 265Using the Analysis ToolPak Statistical Tools .................................................................................................................. 267Using the Descriptive Statistics Tool .......................................................................................................................... 270Determining the Correlation Between Data ......................................................................................................... 272Working with Histograms ............................................................................................................................................... 274Using the Random Number Generation Tool ....................................................................................................... 276Working with Rank and Percentile ............................................................................................................................. 279From Here ................................................................................................................................................................................. 281IIIBUILDING BUSINESS MODELS13 Analyzing Data with Tables ................................................................................................................................... 283Converting a Range to a Table .............................................................................................................................................. 285Basic Table Operations ............................................................................................................................................................... 286Sorting a Table ................................................................................................................................................................................ 287Performing a More Complex Sort ............................................................................................................................... 288Sorting a Table in Natural Order ................................................................................................................................. 289Sorting on Part of a Field ................................................................................................................................................. 290Sorting Without Articles ................................................................................................................................................... 291Filtering Table Data...................................................................................................................................................................... 292Using Filter Lists to Filter a Table ................................................................................................................................ 292Using Complex Criteria to Filter a Table .................................................................................................................. 296Entering Computed Criteria ........................................................................................................................................... 299Copying Filtered Data to a Different Range .......................................................................................................... 300Referencing Tables in Formulas ........................................................................................................................................... 301Using Table Specifiers ........................................................................................................................................................ 301Entering Table Formulas .................................................................................................................................................. 303Excel’s Table Functions .............................................................................................................................................................. 305About Table Functions ...................................................................................................................................................... 305Table Functions That Don’t Require a Criteria Range ...................................................................................... 305Table Functions That Accept Multiple Criteria ..................................................................................................... 307Table Functions That Require a Criteria Range ................................................................................................... 309Case Study: Applying Statistical Table Functions to a Defects Database ..................................................... 313From Here ................................................................................................................................................................................. 31414 Analyzing Data with PivotTables..................................................................................................................... 315What Are PivotTables? ............................................................................................................................................................... 315How PivotTables Work ...................................................................................................................................................... 316PivotTable Terms .................................................................................................................................................................. 317Building PivotTables .................................................................................................................................................................... 318Building a PivotTable from a Table or Range ....................................................................................................... 319Building a PivotTable from an External Database ............................................................................................. 322Working with and Customizing a PivotTable ....................................................................................................... 323Working with PivotTable Subtotals ................................................................................................................................... 323Hiding PivotTable Grand Totals ................................................................................................................................... 324Hiding PivotTable Subtotals .......................................................................................................................................... 324Customizing the Subtotal Calculation ...................................................................................................................... 324Changing the Data Field Summary Calculation ........................................................................................................... 325Using a Difference Summary Calculation................................................................................................................ 326Using a Percentage Summary Calculation ............................................................................................................. 327Using a Running Total Summary Calculation ....................................................................................................... 330Using an Index Summary Calculation ....................................................................................................................... 331Creating Custom PivotTable Calculations ........................................................................................................................ 332Creating a Calculated Field ............................................................................................................................................. 334Creating a Calculated Item ............................................................................................................................................. 335Case Study: Budgeting with Calculated Items.............................................................................................................. 337Using PivotTable Results in a Worksheet Formula .................................................................................................... 339From Here ................................................................................................................................................................................. 34015 Using Excel’s Business-Modeling Tools ....................................................................................................... 341Using What-If Analysis .............................................................................................................................................................. 341Setting Up a One-Input Data Table ............................................................................................................................ 342Adding More Formulas to the Input Table ............................................................................................................. 344Setting Up a Two-Input Table ...................................................................................................................................... 345Editing a Data Table ............................................................................................................................................................ 346Working with Goal Seek............................................................................................................................................................ 347How Does Goal Seek Work? ........................................................................................................................................... 347Running Goal Seek .............................................................................................................................................................. 347Optimizing Product Margin ............................................................................................................................................ 349Note About Goal Seek’s Approximations ................................................................................................................ 351Performing a Break-Even Analysis .............................................................................................................................. 352Solving Algebraic Equations .......................................................................................................................................... 352Working with Scenarios ............................................................................................................................................................ 354Understanding Scenarios ................................................................................................................................................. 354Setting Up Your Worksheet for Scenarios .............................................................................................................. 355Adding a Scenario ................................................................................................................................................................ 355Displaying a Scenario ......................................................................................................................................................... 357Editing a Scenario ................................................................................................................................................................ 358Merging Scenarios ............................................................................................................................................................... 358Generating a Summary Report .................................................................................................................................... 359Deleting a Scenario ............................................................................................................................................................. 360From Here ................................................................................................................................................................................. 36116 Using Regression to Track Trends and Make Forecasts ............................................................ 363Setting Up and Performing a Find ...................................................................................................................................... 363Choosing a Regression Method ............................................................................................................................................ 364Using Simple Regression on Linear Data ......................................................................................................................... 364Analyzing Trends Using Best-Fit Lines ..................................................................................................................... 365Making Forecasts .................................................................................................................................................................. 372Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model ..................................................... 377Using Simple Regression on Nonlinear Data ................................................................................................................. 384Working with an Exponential Trend .......................................................................................................................... 384Working with a Logarithmic Trend ............................................................................................................................ 388Working with a Power Trend ........................................................................................................................................ 391Using Polynomial Regression Analysis ..................................................................................................................... 394Using Multiple Regression Analysis ................................................................................................................................... 396From Here ................................................................................................................................................................................. 39917 Solving Complex Problems with Solver ..................................................................................................... 401Some Background on Solver .................................................................................................................................................. 401The Advantages of Solver ................................................................................................................................................ 402When Do You Use Solver? ............................................................................................................................................... 402Loading Solver ................................................................................................................................................................................ 403Using Solver ...................................................................................................................................................................................... 403Adding Constraints ....................................................................................................................................................................... 406Saving a Solution as a Scenario ............................................................................................................................................ 408Setting Other Solver Options .................................................................................................................................................. 408Selecting the Method Solver Uses ............................................................................................................................... 409Controlling How Solver Works...................................................................................................................................... 409Working with Solver Models ......................................................................................................................................... 412Making Sense of Solver’s Messages ................................................................................................................................... 413Case Study: Solving the Transportation Problem ....................................................................................................... 415Displaying Solver’s Reports ..................................................................................................................................................... 417The Answer Report .............................................................................................................................................................. 417The Sensitivity Report ........................................................................................................................................................ 418The Limits Report ................................................................................................................................................................. 420From Here ................................................................................................................................................................................. 420IV BUILDING FINANCIAL FORMULAS18 Building Loan Formulas ............................................................................................................................................. 421Understanding the Time Value of Money ....................................................................................................................... 421Calculating the Loan Payment .............................................................................................................................................. 422Loan Payment Analysis ..................................................................................................................................................... 423Working with a Balloon Loan ....................................................................................................................................... 424Calculating Interest Costs, Part 1 ................................................................................................................................ 424