Formulas and Functions with Microsoft Office Excel 2007

“If you’ve never quite grasped formulas and functions, Paul McFedries will radically expand your understanding and use of Excel. And if you’re already an expert and you’re moving up to Excel 2007, this book will quickly show you features you’ve only dreamed of until now...” —Thomas `Duffbert’ Duff, Duffbert’s Random Musings, http://www.twduff.com   Develop your Microsoft Excel expertise instantly with proven techniques Master Excel Ranges Create Powerful Arrays Troubleshoot Formula Problems Validate Worksheet Data Perform What-If Analysis Model Your Business Track Trends and Make Forecasts Analyze Data Find Optimal Solutions Build Dynamic Loan Schedules  Most Microsoft® Excel users learn only a small percentage of the program’s features. They know they could get more out of Excel if they could just get a leg up on building formulas and using functions. Unfortunately, this side of Excel appears complex and intimidating to the uninitiated—shrouded in the mysteries of mathematics, finance, and impenetrable spreadsheet jargon. Sound familiar? If you’re a businessperson who needs to use Excel as an everyday part of your job, then you’ve come to the right book.  Formulas and Functions with Microsoft® Office Excel 2007 demystifies worksheet formulas and presents the most useful Excel functions in an accessible,  jargon-free way.  This book not only takes you through Excel’s intermediate and advanced formula-building features, it also tells you why these features are useful to you and shows you how to use them in everyday situations. Throughout the book you’ll find no-nonsense, step-by-step tutorials and lots of practical examples aimed directly at business users.        •    Focuses like a laser on the four technologies that you must master to get the most out of Excel: ranges, formulas, functions, and data analysis tools.      •    Shuns spreadsheet theory in favor of practical know-how that you can put to use right away.      •    Provides numerous real-world examples and techniques to help you learn and understand the importance of each section.   Introduction 1    Getting the Most Out of Ranges 2    Using Range Names 3    Building Basic Formulas 4    Creating Advanced Formulas 5    Troubleshooting Formulas II    Harnessing the Power of Functions    6    Understanding Functions 7    Working with Text Functions 8    Working with Logical and Information Functions 9    Working with Lookup Functions 10  Working with Date and Time Functions 11  Working with Math Functions 12  Working with Statistical Functions III    Building Business Models    13  Analyzing Data with Tables 14  Analyzing Data with PivotTables 15  Using Excel’s Business-Modeling Tools 16  Using Regression to Track Trends and Make Forecasts 17  Solving Complex Problems with Solver IV    Building Financial Formulas    18   Building Loan Formulas 19   Building Investment Formulas 20   Building Discount Formulas Paul McFedries is well-known as a teacher of Windows and Office, particularly Excel, and is the president of Logophilia Limited, a technical writing company. Paul has been working with spreadsheets for more than 20 years and has been developing Excel solutions since the late 1980s. Now primarily a writer, Paul has written more than 50 books that have sold more than three million copies worldwide. These books include Microsoft Office Access 2007 Forms, Reports, and Queries; Tricks of the Microsoft Office 2007 Gurus (all from Que); and Microsoft Windows Vista Unleashed (Sams).   Category  Office Productivity Suite Covers    Microsoft Office Excel 2007 User Level         Intermediate - Advanced  

Table of Contents:
Introduction     What’s in the Book     This Book’s Special Features     I    Mastering Excel Ranges and Formulas 1    Getting the Most Out of Ranges     Advanced Range-Selection Techniques         Mouse Range-Selection Tricks         Keyboard Range-Selection Tricks         Working with 3D Ranges         Selecting a Range Using Go To         Using the Go To Special Dialog Box     Data Entry in a Range     Filling a Range     Using the Fill Handle         Using AutoFill to Create Text and Numeric Series         Creating a Custom AutoFill List         Filling a Range     Creating a Series     Advanced Range Copying         Copying Selected Cell Attributes         Combining the Source and Destination Arithmetically         Transposing Rows and Columns     Clearing a Range     Applying Conditional Formatting to a Range         Creating Highlight Cells Rules         Creating Top/Bottom Rules         Adding Data Bars         Adding Color Scales         Adding Icon Sets         From Here 2    Using Range Names     Defining a Range Name         Working with the Name Box         Using the New Name Dialog Box         Changing the Scope to Define Sheet-Level Names         Using Worksheet Text to Define Names         Naming Constants     Working with Range Names         Referring to a Range Name         Working with Name AutoComplete         Navigating Using Range Names         Pasting a List of Range Names in a Worksheet         Displaying the Name Manager         Filtering Names         Editing a Range Name’s Coordinates         Adjusting Range Name Coordinates Automatically         Changing a Range Name         Deleting a Range Name         Using Names with the Intersection Operator         From Here 3    Building Basic Formulas     Understanding Formula Basics         Formula Limits in Excel 2007         Entering and Editing Formulas         Using Arithmetic Formulas         Using Comparison Formulas         Using Text Formulas         Using Reference Formulas     Understanding Operator Precedence         The Order of Precedence         Controlling the Order of Precedence     Controlling Worksheet Calculation     Copying and Moving Formulas         Understanding Relative Reference Format         Understanding Absolute Reference Format         Copying a Formula Without Adjusting Relative References     Displaying Worksheet Formulas     Converting a Formula to a Value     Working with Range Names in Formulas         Pasting a Name into a Formula         Applying Names to Formulas         Naming Formulas     Working with Links in Formulas         Understanding External References         Updating Links         Changing the Link Source     Formatting Numbers, Dates, and Times         Numeric Display Formats         Date and Time Display Formats         Deleting Custom Formats         From Here 4    Creating Advanced Formulas     Working with Arrays         Using Array Formulas         Using Array Constants         Functions That Use or Return Arrays     Using Iteration and Circular References     Consolidating Multisheet Data         Consolidating by Position         Consolidating by Category     Applying Data-Validation Rules to Cells     Using Dialog Box Controls on a Worksheet         Using the Form Controls         Adding a Control to a Worksheet         Linking a Control to a Cell Value         Understanding the Worksheet Controls         From Here 5    Troubleshooting Formulas     Understanding Excel’s Error Values         #DIV/0!         #N/A         #NAME?         Avoiding #NAME? Errors When Deleting Range Names         #NULL!         #NUM!         #REF!         #VALUE!     Fixing Other Formula Errors         Missing or Mismatched Parentheses         Erroneous Formula Results         Fixing Circular References     Handling Formula Errors with IFERROR()     Using the Formula Error Checker         Choosing an Error Action         Setting Error Checker Options     Auditing a Worksheet         Understanding Auditing         Tracing Cell Precedents         Tracing Cell Dependents         Tracing Cell Errors         Removing Tracer Arrows         Evaluating Formulas         Watching Cell Values         From Here     II    Harnessing the Power of Functions    6    Understanding Functions     About Excel’s Functions     The Structure of a Function     Typing a Function into a Formula     Using the Insert Function Feature     Loading the Analysis ToolPak         From Here 7    Working with Text Functions     Excel’s Text Functions     Working with Characters and Codes         The CHAR() Function         The CODE() Function     Converting Text         The LOWER() Function         The UPPER() Function         The PROPER() Function     Formatting Text         The DOLLAR() Function         The FIXED() Function         The TEXT() Function         Displaying When a Workbook Was Last Updated     Manipulating Text     Removing Unwanted Characters from a String         The TRIM() Function         The CLEAN() Function         The REPT() Function: Repeating a Character         Padding a Cell         Building Text Charts     Extracting a Substring         The LEFT() Function         The RIGHT() Function         The MID() Function         Converting Text to Sentence Case         A Date-Conversion Formula     Generating Account Numbers     Searching for Substrings         The FIND() and SEARCH() Functions         Extracting a First Name or Last Name         Extracting First Name, Last Name, and Middle Initial         Determining the Column Letter     Substituting One Substring for Another         The REPLACE() Function         The SUBSTITUTE() Function         Removing a Character from a String         Removing Two Different Characters from a String         Removing Line Feeds     Generating Account Numbers, Part 2         From Here 8    Working with Logical and Information Functions     Adding Intelligence with Logical Functions         Using the IF() Function         Performing Multiple Logical Tests         Combining Logical Functions with Arrays     Building an Accounts Receivable Aging Worksheet         Calculating a Smarter Due Date         Aging Overdue Invoices     Getting Data with Information Functions         The CELL() Function         The ERROR.TYPE() Function         The INFO() Function         The IS Functions         From Here 9    Working with Lookup Functions     Understanding Lookup Tables     The CHOOSE() Function         Determining the Name of the Day of the Week         Determining the Month of the Fiscal Year         Calculating Weighted Questionnaire Results         Integrating CHOOSE() and Worksheet Option Buttons     Looking Up Values in Tables         The VLOOKUP() Function         The HLOOKUP() Function         Returning a Customer Discount Rate with a Range Lookup         Returning a Tax Rate with a Range Lookup         Finding Exact Matches         Advanced Lookup Operations         From Here 10    Working with Date and Time Functions     How Excel Deals with Dates and Times         Entering Dates and Times         Excel and Two-Digit Years     Using Excel’s Date Functions         Returning a Date         Returning Parts of a Date         Calculating the Difference Between Two Dates     Using Excel’s Time Functions         Returning a Time         Returning Parts of a Time         Calculating the Difference Between Two Times     Building an Employee Time Sheet         From Here 11    Working with Math Functions     Understanding Excel’s Rounding Functions         The ROUND() Function         The MROUND() Function         The ROUNDDOWN() and ROUNDUP() Functions         The CEILING() and FLOOR() Functions         Determining the Fiscal Quarter in Which a Date Falls         Calculating Easter Dates         The EVEN() and ODD() Functions         The INT() and TRUNC() Functions         Using Rounding to Prevent Calculation Errors         Setting Price Points     Rounding Billable Time     Summing Values         The SUM() Function         Calculating Cumulative Totals         Summing Only the Positive or Negative Values in a Range     The MOD() Function         A Better Formula for Time Differences         Summing Every nth Row         Determining Whether a Year Is a Leap Year         Creating Ledger Shading     Generating Random Numbers         The RAND() Function         The RANDBETWEEN() Function         From Here 12    Working with Statistical Functions     Understanding Descriptive Statistics     Counting Items with the COUNT() Function     Calculating Averages         The AVERAGE() Function         The MEDIAN() Function         The MODE() Function         Calculating the Weighted Mean     Calculating Extreme Values         The MAX() and MIN() Functions         The LARGE() and SMALL() Functions         Performing Calculations on the Top k Values         Performing Calculations on the Bottom k Values     Calculating Measures of Variation         Calculating the Range         Calculating the Variance with the VAR() Function         Calculating the Standard Deviation with the STDEVP() and STDEV() Functions     Working with Frequency Distributions         The FREQUENCY() Function         Understanding the Normal Distribution and the NORMDIST() Function         The Shape of the Curve I: The SKEW() Function         The Shape of the Curve II: The KURT() Function     Using the Analysis ToolPak Statistical Tools         Using the Descriptive Statistics Tool         Determining the Correlation Between Data         Working with Histograms         Using the Random Number Generation Tool         Working with Rank and Percentile         From Here     III    Building Business Models    13    Analyzing Data with Tables     Converting a Range to a Table     Basic Table Operations     Sorting a Table         Sorting a Table in Natural Order         Sorting on Part of a Field         Sorting Without Articles     Filtering Table Data         Using Filter Lists to Filter a Table         Using Complex Criteria to Filter a Table         Entering Computed Criteria         Copying Filtered Data to a Different Range     Referencing Tables in Formulas         Using Table Specifiers         Entering Table Formulas     Excel’s Table Functions         About Table Functions         Table Functions That Don’t Require a Criteria Range         Table Functions That Accept Multiple Criteria         Table Functions That Require a Criteria Range         Applying Statistical Table Functions to a Defects Database         From Here 14    Analyzing Data with PivotTables     What Are PivotTables?         How PivotTables Work         Some PivotTable Terms     Building PivotTables         Building a PivotTable from a Table or Range         Building a PivotTable from an External Database         Working with and Customizing a PivotTable     Working with PivotTable Subtotals         Hiding PivotTable Grand Totals         Hiding PivotTable Subtotals         Customizing the Subtotal Calculation     Changing the Data Field Summary Calculation         Using a Difference Summary Calculation         Using a Percentage Summary Calculation         Using a Running Total Summary Calculation         Using an Index Summary Calculation     Creating Custom PivotTable Calculations         Creating a Calculated Field         Creating a Calculated Item     Budgeting with Calculated Items     Using PivotTable Results in a Worksheet Formula         From Here 15    Using Excel’s Business-Modeling Tools     Using What-If Analysis         Setting Up a One-Input Data Table         Adding More Formulas to the Input Table         Setting Up a Two-Input Table         Editing a Data Table     Working with Goal Seek         How Does Goal Seek Work?         Running Goal Seek         Optimizing Product Margin         A Note About Goal Seek’s Approximations         Performing a Break-Even Analysis         Solving Algebraic Equations     Working with Scenarios         Understanding Scenarios         Setting Up Your Worksheet for Scenarios         Adding a Scenario         Displaying a Scenario         Editing a Scenario         Merging Scenarios         Generating a Summary Report         Deleting a Scenario         From Here 16    Using Regression to Track Trends and Make Forecasts     Choosing a Regression Method     Using Simple Regression on Linear Data         Analyzing Trends Using Best-Fit Lines         Making Forecasts     Trend Analysis and Forecasting for a Seasonal Sales Model     Using Simple Regression on Nonlinear Data         Working with an Exponential Trend         Working with a Logarithmic Trend             Working with a Power Trend         Using Polynomial Regression Analysis     Using Multiple Regression Analysis         From Here 17    Solving Complex Problems with Solver     Some Background on Solver         The Advantages of Solver         When Do You Use Solver?     Loading Solver     Using Solver     Adding Constraints     Saving a Solution as a Scenario     Setting Other Solver Options         Controlling Solver         Selecting the Method Solver Uses         Working with Solver Models     Making Sense of Solver’s Messages     Solving the Transportation Problem     Displaying Solver’s Reports         The Answer Report         The Sensitivity Report         The Limits Report         From Here     IV    Building Financial Formulas    18    Building Loan Formulas     Understanding the Time Value of Money     Calculating the Loan Payment         Loan Payment Analysis         Working with a Balloon Loan         Calculating Interest Costs, Part I         Calculating the Principal and Interest         Calculating Interest Costs, Part 2         Calculating Cumulative Principal and Interest     Building a Loan Amortization Schedule         Building a Fixed-Rate Amortization Schedule         Building a Dynamic Amortization Schedule     Calculating the Term of the Loan     Calculating the Interest Rate Required for a Loan     Calculating How Much You Can Borrow     Working with Mortgages         Building a Variable-Rate Mortgage Amortization Schedule         Allowing for Mortgage Principal Paydowns         From Here 19    Building Investment Formulas     Working with Interest Rates         Understanding Compound Interest         Nominal Versus Effective Interest         Converting Between the Nominal Rate and the Effective Rate     Calculating the Future Value         The Future Value of a Lump Sum         The Future Value of a Series of Deposits         The Future Value of a Lump Sum Plus Deposits     Working Toward an Investment Goal         Calculating the Required Interest Rate         Calculating the Required Number of Periods         Calculating the Required Regular Deposit         Calculating the Required Initial Deposit         Calculating the Future Value with Varying Interest Rates     Building an Investment Schedule         From Here 20    Building Discount Formulas     Calculating the Present Value         Taking Inflation into Account         Calculating Present Value Using PV()         Income Investing Versus Purchasing a Rental Property         Buying Versus Leasing     Discounting Cash Flows         Calculating the Net Present Value         Calculating Net Present Value Using NPV()         Net Present Value with Varying Cash Flows         Net Present Value with Nonperiodic Cash Flows     Calculating the Payback Period         Simple Undiscounted Payback Period         Exact Undiscounted Payback Point         Discounted Payback Period     Calculating the Internal Rate of Return         Using the IRR() Function         Calculating the Internal Rate of Return for Nonperiodic Cash Flows         Calculating Multiple Internal Rates of Return     Publishing a Book         From Here 0789736683, TOC, 2/19/2007