Overview

MASTER CORE EXCEL 2010 TOOLS FOR BUILDING POWERFUL, RELIABLE SPREADSHEETS!   Excel expert Paul McFedries shows how to use Excel 2010’s core features to solve problems and get the answers you need! Using real-world examples, McFedries helps you get the absolute most out of breakthrough Excel 2010 improvements–from Sparklines to the brand-new version of Solver. Along the way, you’ll discover the fastest, best ways to handle essential day-to-day tasks ranging from generating account numbers to projecting the impact of inflation.Becoming an Excel expert has never been easier! You’ll find crystal-clear instructions… insider insights… even complete step-by-step projects for building timesheets, projecting cash flow, aging receivables, analyzing defects, and more.   • Create more powerful formulas • Use conditional formatting to instantly reveal anomalies, problems, or opportunities • Analyze your data with standard tables and PivotTables • Use complex criteria to filter data in lists • Understand correlations between data   • Perform sophisticated what-if analyses • Use regression to track trends and make forecasts • Build loan, investment, and discount formulas • Troubleshoot problems with formulas, ranges, and functions   About MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excelskills, and presents focused tasks and examples for performing them rapidly and effectively. Selectedby Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will:   • Dramatically increase your productivity–saving you 50 hours a year, or more • Present proven, creative strategies for solving real-world problems • Show you how to get great results, no matter how much data you have • Help you avoid critical mistakes that even experienced users make   Paul McFedries, president of Logophilia Limited, is a Microsoft Office expert and full-time technical writer. Paul has been authoring computer books since 1991 and has more than 70 books to his credit, which combined have sold more than three million copies worldwide. His recent titles include Using the Microsoft Office Web Apps, and Microsoft Windows 7 Unleashed. He is also proprietor of Word Spy (www.wordspy.com), a website that tracks new words and phrases as they enter the English language.  

Full Product Details

Author:   Paul McFedries
Publisher:   Pearson Education (US)
Imprint:   Que Corporation,U.S.
Dimensions:   Width: 23.10cm , Height: 2.70cm , Length: 17.80cm
Weight:   0.796kg
ISBN:  

9780789743060

ISBN 10:   078974306
Pages:   512
Publication Date:   27 May 2010
Audience:   Professional and scholarly ,  Professional & Vocational
Format:   Paperback
Publisher's Status:   Out of Print
Availability:   In Print  
Limited stock is available. It will be ordered for you and shipped pending supplier's limited stock.

Table of Contents

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

Reviews

Author Information

Paul McFedries, president of Logophilia Limited, is a full-time technical writer, passionate computer tinkerer, and Windows expert. He has authored 60+ computer books that have sold 3,000,000+ copies. His recent titles include Microsoft Windows 7 Unleashed, Microsoft Home Server Unleashed, Tweak It and Freak It: A Killer Guide to Making Windows Run Your Way, Formulas and Functions with Microsoft Excel 2007; andTricks of the Microsoft Office 2007 Gurus. He is also proprietor of Word Spy (www.wordspy.com) a website that tracks new words and phrases as they enter the English language.     Editor: Bill Jelen has made more than 75 guest appearances on TechTV with Leo Laporte and was voted guest of the year on the Computer America radio show. He has produced more than 600 episodes of his daily video Learn Excel podcast. Before founding MrExcel.com in 1998, Jelen spent 12 years “in the trenches” as a financial analyst for the accounting, finance, marketing, and operations departments of a publicly held company. Since then, his company has automated Excel reports for hundreds of clients around the world. The website answers more than 30,000 questions a year–for free–for readers all over the world.

Tab Content 6

Author Website:  

Customer Reviews

Recent Reviews

No review item found!

Add your own review!

Countries Available

All regions