Home > Store

Formulas and Functions: Microsoft Excel 2010

Register your product to gain access to bonus material or receive a coupon.

Formulas and Functions: Microsoft Excel 2010

eBook (Watermarked)

  • Your Price: $25.59
  • List Price: $31.99
  • Includes EPUB, MOBI, and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    MOBI MOBI The eBook format compatible with the Amazon Kindle and Amazon Kindle applications.

    Adobe Reader PDF The 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 discreetly watermarking it with your name, making it uniquely yours.

Description

  • Copyright 2010
  • Edition: 1st
  • eBook (Watermarked)
  • ISBN-10: 0-7897-4771-5
  • ISBN-13: 978-0-7897-4771-6

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.

Sample Content

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

Updates

Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020