Formulas and Functions: Microsoft Excel 2010 (MrExcel Library)

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 Contents......Page 5 Introduction......Page 18 This Book’s Special Features......Page 19 Advanced Range-Selection Techniques......Page 22 Data Entry in a Range......Page 30 Using the Fill Handle......Page 31 Creating a Series......Page 34 Advanced Range Copying......Page 35 Copying Selected Cell Attributes......Page 36 Applying Conditional Formatting to a Range......Page 39 2 Using Range Names......Page 50 Defining a Range Name......Page 51 Working with Range Names......Page 58 Understanding Formula Basics......Page 68 Understanding Operator Precedence......Page 72 Controlling Worksheet Calculation......Page 75 Copying and Moving Formulas......Page 76 Converting a Formula to a Value......Page 80 Working with Range Names in Formulas......Page 81 Working with Links in Formulas......Page 86 Formatting Numbers, Dates, and Times......Page 89 Working with Arrays......Page 102 Understanding Array Formulas......Page 104 Using Array Constants......Page 106 Using Iteration and Circular References......Page 108 Consolidating Multisheet Data......Page 110 Applying Data-Validation Rules to Cells......Page 115 Using Dialog Box Controls on a Worksheet......Page 118 5 Troubleshooting Formulas......Page 126 Understanding Excel’s Error Values......Page 127 Fixing Other Formula Errors......Page 131 Handling Formula Errors with IFERROR( )......Page 134 Using the Formula Error Checker......Page 135 Auditing a Worksheet......Page 139 6 Understanding Functions......Page 144 The Structure of a Function......Page 145 Typing a Function into a Formula......Page 147 Using the Insert Function Feature......Page 148 Loading the Analysis ToolPak......Page 151 Working with Characters and Codes......Page 154 Converting Text......Page 159 Formatting Text......Page 160 Removing Unwanted Characters from a String......Page 163 Extracting a Substring......Page 166 Searching for Substrings......Page 168 Case Study: Generating Account Numbers......Page 169 Substituting One Substring for Another......Page 172 Case Study: Generating Account Numbers, Part 2......Page 174 Adding Intelligence with Logical Functions......Page 176 Case Study: Building an Accounts Receivable Aging Worksheet......Page 190 Getting Data with Information Functions......Page 193 9 Working with Lookup Functions......Page 202 Understanding Lookup Tables......Page 203 The CHOOSE( ) Function......Page 204 Looking Up Values in Tables......Page 207 How Excel Deals with Dates and Times......Page 218 Using Excel’s Date Functions......Page 221 Using Excel’s Time Functions......Page 237 Case Study: Building an Employee Time Sheer......Page 241 11 Working with Math Functions......Page 246 Understanding Excel’s Rounding Functions......Page 249 Summing Values......Page 255 MOD( ) Function......Page 257 Generating Random Numbers......Page 261 Understanding Descriptive Statistics......Page 266 Counting Items with the COUNT( ) Function......Page 269 Calculating Averages......Page 270 Calculating Extreme Values......Page 273 Calculating Measures of Variation......Page 275 Working with Frequency Distributions......Page 278 Using the Analysis ToolPak Statistical Tools......Page 284 13 Analyzing Data with Tables......Page 300 Converting a Range to a Table......Page 302 Basic Table Operations......Page 303 Sorting a Table......Page 304 Filtering Table Data......Page 309 Referencing Tables in Formulas......Page 318 Excel’s Table Functions......Page 322 Case Study: Applying Statistical Table Functions to a Defects Database......Page 330 What Are PivotTables?......Page 332 Building PivotTables......Page 335 Working with PivotTable Subtotals......Page 340 Changing the Data Field Summary Calculation......Page 342 Creating Custom PivotTable Calculations......Page 349 Case Study: Budgeting with Calculated Items......Page 354 Using PivotTable Results in a Worksheet Formula......Page 356 Using What-If Analysis......Page 358 Working with Goal Seek......Page 364 Working with Scenarios......Page 371 Setting Up and Performing a Find......Page 380 Using Simple Regression on Linear Data......Page 381 Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model......Page 394 Using Simple Regression on Nonlinear Data......Page 401 Using Multiple Regression Analysis......Page 413 Some Background on Solver......Page 418 Using Solver......Page 420 Adding Constraints......Page 423 Setting Other Solver Options......Page 425 Making Sense of Solver’s Messages......Page 430 Case Study: Solving the Transportation Problem......Page 432 Displaying Solver’s Reports......Page 434 Understanding the Time Value of Money......Page 438 Calculating the Loan Payment......Page 439 Building a Loan Amortization Schedule......Page 445 Calculating the Term of the Loan......Page 448 Calculating the Interest Rate Required for a Loan......Page 450 Calculating How Much You Can Borrow......Page 451 Case Study: Working with Mortgages......Page 452 Working with Interest Rates......Page 456 Calculating the Future Value......Page 459 Working Toward an Investment Goal......Page 461 Case Study: Building an Investment Schedule......Page 466 20 Building Discount Formulas......Page 470 Calculating the Present Value......Page 471 Discounting Cash Flows......Page 475 Calculating the Payback Period......Page 481 Case Study: Publishing a Book......Page 486 A......Page 492 C......Page 493 D......Page 494 F......Page 496 G......Page 498 L......Page 499 M......Page 500 P......Page 501 Q-R......Page 502 S......Page 503 T......Page 505 W......Page 506 X-Y-Z......Page 507

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.

MASTER CORE EXCEL 2010 TOOLS FOR BUILDING POWERFUL, RELIABLE SPREADSHEETS! Excel expert Paul McFedries shows how to use Excel 2010s 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 improvementsfrom Sparklines to the brand-new version of Solver. Along the way, youll 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! Youll 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 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 Dramatically increase your productivitysaving 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 ((http://www.wordspy.com) www.wordspy.com ), a website that tracks new words and phrases as they enter the English language. Provides instructions on the formula-building techniques of Microsoft Excel, covering such topics as ranges, text functions, logical and information functions, list data, regression methods, and working with bonds. About MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excel skills, and presents focused tasks and examples for performing them rapidly and effectively. --Book Jacket