Product Cover Image

Formulas and Functions: Microsoft Excel 2010

By Paul McFedries

Published by Que

Published Date: May 14, 2010

Description

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.

 

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

 

Purchase Info

ISBN-10: 0-7897-4306-X

ISBN-13: 978-0-7897-4306-0

Format: Book

$39.99 | Free Ground Shipping.

Add to Cart

Digital Choices

eBook (Watermarked) ?

Includes EPUB, MOBI, and PDF

This eBook includes the following formats, accessible from your Account page after purchase:

ePubEPUBThe open industry format known for its reflowable content and usability on supported mobile devices.

MOBIMOBIThe eBook format compatible with the Amazon Kindle and Amazon Kindle applications.

Adobe ReaderPDFThe popular standard, used most often with the free Adobe® Reader® software.

This eBook requires no passwords or activation to read. We customize your eBook by discretely watermarking it with your name, making it uniquely yours.

Add to CartFormulas and Functions: Microsoft Excel 2010

$31.99 $25.59 | ISBN-13: 978-0-7897-4771-6