You are previewing Business Solutions Formulas and Functions with Microsoft® Office Excel 2007.
O'Reilly logo
Business Solutions Formulas and Functions with Microsoft® Office Excel 2007

Book Description

“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,

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.


    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

    1. Copyright
      1. Dedication
    2. About the Author
    3. Acknowledgments
    4. We Want to Hear from You!
    5. Reader Services
    6. Introduction
      1. What’s in the Book
      2. This Book’s Special Features
    7. I. Mastering Excel Ranges and Formulas
      1. 1. Getting the Most Out of Ranges
        1. Advanced Range-Selection Techniques
          1. Mouse Range-Selection Tricks
          2. Keyboard Range-Selection Tricks
          3. Working with 3D Ranges
          4. Selecting a Range Using Go To
          5. Using the Go To Special Dialog Box
            1. Selecting Cells by Type
            2. Selecting Adjacent Cells
            3. Selecting Cells by Differences
            4. Selecting Cells by Reference
            5. Other Go To Special Options
            6. Shortcut Keys for Selecting via Go To
        2. Data Entry in a Range
        3. Filling a Range
        4. Using the Fill Handle
          1. Using AutoFill to Create Text and Numeric Series
          2. Creating a Custom AutoFill List
          3. Filling a Range
        5. Creating a Series
        6. Advanced Range Copying
          1. Copying Selected Cell Attributes
          2. Combining the Source and Destination Arithmetically
          3. Transposing Rows and Columns
        7. Clearing a Range
        8. Applying Conditional Formatting to a Range
          1. Creating Highlight Cells Rules
          2. Creating Top/Bottom Rules
          3. Adding Data Bars
          4. Adding Color Scales
          5. Adding Icon Sets
          6. From Here
      2. 2. Using Range Names
        1. Defining a Range Name
          1. Working with the Name Box
          2. Using the New Name Dialog Box
          3. Changing the Scope to Define Sheet-Level Names
          4. Using Worksheet Text to Define Names
          5. Naming Constants
        2. Working with Range Names
          1. Referring to a Range Name
          2. Working with Name AutoComplete
          3. Navigating Using Range Names
          4. Pasting a List of Range Names in a Worksheet
          5. Displaying the Name Manager
          6. Filtering Names
          7. Editing a Range Name’s Coordinates
          8. Adjusting Range Name Coordinates Automatically
            1. Solution 1: Include a Blank Cell at the End of the Range
            2. Solution 2: Name the Entire Row or Column
          9. Changing a Range Name
          10. Deleting a Range Name
          11. Using Names with the Intersection Operator
          12. From Here
      3. 3. Building Basic Formulas
        1. Understanding Formula Basics
          1. Formula Limits in Excel 2007
          2. Entering and Editing Formulas
          3. Using Arithmetic Formulas
          4. Using Comparison Formulas
          5. Using Text Formulas
          6. Using Reference Formulas
        2. Understanding Operator Precedence
          1. The Order of Precedence
          2. Controlling the Order of Precedence
        3. Controlling Worksheet Calculation
        4. Copying and Moving Formulas
          1. Understanding Relative Reference Format
          2. Understanding Absolute Reference Format
          3. Copying a Formula Without Adjusting Relative References
        5. Displaying Worksheet Formulas
        6. Converting a Formula to a Value
        7. Working with Range Names in Formulas
          1. Pasting a Name into a Formula
          2. Applying Names to Formulas
            1. Ignoring Relative and Absolute References When Applying Names
            2. Using Row and Column Names When Applying Names
          3. Naming Formulas
        8. Working with Links in Formulas
          1. Understanding External References
          2. Updating Links
          3. Changing the Link Source
        9. Formatting Numbers, Dates, and Times
          1. Numeric Display Formats
            1. Changing Numeric Formats
            2. Customizing Numeric Formats
            3. Hiding Zeros
            4. Using Condition Values
          2. Date and Time Display Formats
            1. Customizing Date and Time Formats
          3. Deleting Custom Formats
          4. From Here
      4. 4. Creating Advanced Formulas
        1. Working with Arrays
          1. Using Array Formulas
            1. Understanding Array Formulas
            2. Array Formulas That Operate on Multiple Ranges
          2. Using Array Constants
          3. Functions That Use or Return Arrays
        2. Using Iteration and Circular References
        3. Consolidating Multisheet Data
          1. Consolidating by Position
          2. Consolidating by Category
        4. Applying Data-Validation Rules to Cells
        5. Using Dialog Box Controls on a Worksheet
          1. Using the Form Controls
          2. Adding a Control to a Worksheet
          3. Linking a Control to a Cell Value
          4. Understanding the Worksheet Controls
            1. Group Boxes
            2. Option Buttons
            3. Check Boxes
            4. List Boxes and Combo Boxes
            5. Scroll Bars and Spin Boxes
          5. From Here
      5. 5. Troubleshooting Formulas
        1. Understanding Excel’s Error Values
          1. #DIV/0!
          2. #N/A
          3. #NAME?
          4. #NULL!
          5. #NUM!
          6. #REF!
          7. #VALUE!
        2. Fixing Other Formula Errors
          1. Missing or Mismatched Parentheses
          2. Erroneous Formula Results
          3. Fixing Circular References
        3. Handling Formula Errors with IFERROR()
        4. Using the Formula Error Checker
          1. Choosing an Error Action
          2. Setting Error Checker Options
        5. Auditing a Worksheet
          1. Understanding Auditing
          2. Tracing Cell Precedents
          3. Tracing Cell Dependents
          4. Tracing Cell Errors
          5. Removing Tracer Arrows
          6. Evaluating Formulas
          7. Watching Cell Values
          8. From Here
    8. II. Harnessing the Power of Functions
      1. 6. Understanding Functions
        1. About Excel’s Functions
        2. The Structure of a Function
        3. Typing a Function into a Formula
        4. Using the Insert Function Feature
        5. Loading the Analysis ToolPak
          1. From Here
      2. 7. Working with Text Functions
        1. Excel’s Text Functions
        2. Working with Characters and Codes
          1. The CHAR() Function
            1. Generating the ANSI Character Set
            2. Generating a Series of Letters
          2. The CODE() Function
            1. Generating a Series of Letters Starting from Any Letter
        3. Converting Text
          1. The LOWER() Function
          2. The UPPER() Function
          3. The PROPER() Function
        4. Formatting Text
          1. The DOLLAR() Function
          2. The FIXED() Function
          3. The TEXT() Function
          4. Displaying When a Workbook Was Last Updated
        5. Manipulating Text
        6. Removing Unwanted Characters from a String
          1. The TRIM() Function
          2. The CLEAN() Function
          3. The REPT() Function: Repeating a Character
          4. Padding a Cell
          5. Building Text Charts
        7. Extracting a Substring
          1. The LEFT() Function
          2. The RIGHT() Function
          3. The MID() Function
          4. Converting Text to Sentence Case
          5. A Date-Conversion Formula
        8. Searching for Substrings
          1. The FIND() and SEARCH() Functions
          2. Extracting a First Name or Last Name
          3. Extracting First Name, Last Name, and Middle Initial
          4. Determining the Column Letter
        9. Substituting One Substring for Another
          1. The REPLACE() Function
          2. The SUBSTITUTE() Function
          3. Removing a Character from a String
          4. Removing Two Different Characters from a String
          5. Removing Line Feeds
          6. From Here
      3. 8. Working with Logical and Information Functions
        1. Adding Intelligence with Logical Functions
          1. Using the IF() Function
            1. IF(): The Simplest Case
            2. Handling a FALSE Result
            3. Avoiding Division by Zero
          2. Performing Multiple Logical Tests
            1. Nesting IF() Functions
            2. Calculating Tiered Bonuses
            3. The AND() Function
            4. Slotting Values into Categories
            5. The OR() Function
            6. Applying Conditional Formatting with Formulas
          3. Combining Logical Functions with Arrays
            1. Applying a Condition Across a Range
            2. Operating Only on Cells That Meet a Condition
            3. Determining Whether a Value Appears in a List
            4. Counting Occurrences in a Range
            5. Determining Where a Value Appears in a List
        2. Getting Data with Information Functions
          1. The CELL() Function
          2. The ERROR.TYPE() Function
          3. The INFO() Function
          4. The IS Functions
            1. Counting the Number of Blanks in a Range
            2. Checking a Range for Non-numeric Values
            3. Counting the Number of Errors in a Range
            4. Ignoring Errors When Working with a Range
          5. From Here
      4. 9. Working with Lookup Functions
        1. Understanding Lookup Tables
        2. The CHOOSE() Function
          1. Determining the Name of the Day of the Week
          2. Determining the Month of the Fiscal Year
          3. Calculating Weighted Questionnaire Results
          4. Integrating CHOOSE() and Worksheet Option Buttons
        3. Looking Up Values in Tables
          1. The VLOOKUP() Function
          2. The HLOOKUP() Function
          3. Returning a Customer Discount Rate with a Range Lookup
          4. Returning a Tax Rate with a Range Lookup
          5. Finding Exact Matches
            1. Looking Up a Customer Account Number
            2. Combining Exact-Match Lookups with In-Cell Drop-Down Lists
          6. Advanced Lookup Operations
            1. The MATCH() and INDEX() Functions
            2. Looking Up a Value Using Worksheet List Boxes
            3. Using Any Column as the Lookup Column
            4. Creating Row-and-Column Lookups
            5. Creating Multiple-Column Lookups
          7. From Here
      5. 10. Working with Date and Time Functions
        1. How Excel Deals with Dates and Times
          1. Entering Dates and Times
          2. Excel and Two-Digit Years
        2. Using Excel’s Date Functions
          1. Returning a Date
            1. TODAY(): Returning the Current Date
            2. DATE(): Returning Any Date
            3. DATEVALUE(): Converting a String to a Date
          2. Returning Parts of a Date
            1. The YEAR() Function
            2. The MONTH() Function
            3. The DAY() Function
            4. The WEEKDAY() Function
            5. The WEEKNUM() Function
            6. Returning a Date X Years, Months, or Days from Now
            7. A Workday Alternative: The WORKDAY() Function
            8. Adding X Months: A Problem
            9. The EDATE() Function
            10. The EOMONTH() Function
            11. Returning the Last Day of Any Month
            12. Determining a Person’s Birthday Given the Birth Date
            13. Returning the Date of the Nth Occurrence of a Weekday in a Month
            14. Calculating Holiday Dates
            15. Calculating the Julian Date
          3. Calculating the Difference Between Two Dates
            1. Calculating a Person’s Age
            2. The DATEDIF() Function
            3. Calculating a Person’s Age, Part 2
            4. NETWORKDAYS(): Calculating the Number of Workdays Between Two Dates
            5. DAYS360(): Calculating Date Differences Using a 360-Day Year
            6. YEARFRAC(): Returning the Fraction of a Year Between Two Dates
        3. Using Excel’s Time Functions
          1. Returning a Time
            1. NOW(): Returning the Current Time
            2. TIME(): Returning Any Time
            3. TIMEVALUE(): Converting a String to a Time
          2. Returning Parts of a Time
            1. The HOUR() Function
            2. The MINUTE() Function
            3. The SECOND() Function
            4. Returning a Time X Hours, Minutes, or Seconds from Now
            5. Summing Time Values
          3. Calculating the Difference Between Two Times
          4. From Here
      6. 11. Working with Math Functions
        1. Understanding Excel’s Rounding Functions
          1. The ROUND() Function
          2. The MROUND() Function
          3. The ROUNDDOWN() and ROUNDUP() Functions
          4. The CEILING() and FLOOR() Functions
          5. Determining the Fiscal Quarter in Which a Date Falls
          6. Calculating Easter Dates
          7. The EVEN() and ODD() Functions
          8. The INT() and TRUNC() Functions
          9. Using Rounding to Prevent Calculation Errors
          10. Setting Price Points
        2. Summing Values
          1. The SUM() Function
          2. Calculating Cumulative Totals
          3. Summing Only the Positive or Negative Values in a Range
        3. The MOD() Function
          1. A Better Formula for Time Differences
          2. Summing Every nth Row
            1. Special Case No. 1: Summing Only Odd Rows
            2. Special Case No. 2: Summing Only Even Rows
          3. Determining Whether a Year Is a Leap Year
          4. Creating Ledger Shading
        4. Generating Random Numbers
          1. The RAND() Function
            1. Generating Random n-Digit Numbers
            2. Generating a Random Letter
            3. Sorting Values Randomly
          2. The RANDBETWEEN() Function
          3. From Here
      7. 12. Working with Statistical Functions
        1. Understanding Descriptive Statistics
        2. Counting Items with the COUNT() Function
        3. Calculating Averages
          1. The AVERAGE() Function
          2. The MEDIAN() Function
          3. The MODE() Function
          4. Calculating the Weighted Mean
        4. Calculating Extreme Values
          1. The MAX() and MIN() Functions
          2. The LARGE() and SMALL() Functions
          3. Performing Calculations on the Top k Values
          4. Performing Calculations on the Bottom k Values
        5. Calculating Measures of Variation
          1. Calculating the Range
          2. Calculating the Variance with the VAR() Function
          3. Calculating the Standard Deviation with the STDEVP() and STDEV() Functions
        6. Working with Frequency Distributions
          1. The FREQUENCY() Function
          2. Understanding the Normal Distribution and the NORMDIST() Function
          3. The Shape of the Curve I: The SKEW() Function
          4. The Shape of the Curve II: The KURT() Function
        7. Using the Analysis ToolPak Statistical Tools
          1. Using the Descriptive Statistics Tool
          2. Determining the Correlation Between Data
          3. Working with Histograms
          4. Using the Random Number Generation Tool
          5. Working with Rank and Percentile
          6. From Here
    9. III. Building Business Models
      1. 13. Analyzing Data with Tables
        1. Converting a Range to a Table
        2. Basic Table Operations
        3. Sorting a Table
          1. Sorting a Table in Natural Order
          2. Sorting on Part of a Field
          3. Sorting Without Articles
        4. Filtering Table Data
          1. Using Filter Lists to Filter a Table
            1. Working with Quick Filters
            2. Showing Filtered Records
          2. Using Complex Criteria to Filter a Table
            1. Setting Up a Criteria Range
            2. Filtering a Table with a Criteria Range
            3. Entering Compound Criteria
          3. Entering Computed Criteria
          4. Copying Filtered Data to a Different Range
        5. Referencing Tables in Formulas
          1. Using Table Specifiers
          2. Entering Table Formulas
        6. Excel’s Table Functions
          1. About Table Functions
          2. Table Functions That Don’t Require a Criteria Range
            1. Using COUNTIF()
            2. Using SUMIF()
            3. Using AVERAGEIF()
          3. Table Functions That Accept Multiple Criteria
            1. Using COUNTIFS()
            2. Using SUMIFS()
            3. Using AVERAGEIFS()
          4. Table Functions That Require a Criteria Range
            1. Using DAVERAGE()
            2. Using DGET()
          5. From Here
      2. 14. Analyzing Data with PivotTables
        1. What Are PivotTables?
          1. How PivotTables Work
          2. Some PivotTable Terms
        2. Building PivotTables
          1. Building a PivotTable from a Table or Range
          2. Building a PivotTable from an External Database
          3. Working with and Customizing a PivotTable
        3. Working with PivotTable Subtotals
          1. Hiding PivotTable Grand Totals
          2. Hiding PivotTable Subtotals
          3. Customizing the Subtotal Calculation
        4. Changing the Data Field Summary Calculation
          1. Using a Difference Summary Calculation
          2. Using a Percentage Summary Calculation
          3. Using a Running Total Summary Calculation
          4. Using an Index Summary Calculation
        5. Creating Custom PivotTable Calculations
          1. Creating a Calculated Field
          2. Creating a Calculated Item
        6. Using PivotTable Results in a Worksheet Formula
          1. From Here
      3. 15. Using Excel’s Business-Modeling Tools
        1. Using What-If Analysis
          1. Setting Up a One-Input Data Table
          2. Adding More Formulas to the Input Table
          3. Setting Up a Two-Input Table
          4. Editing a Data Table
        2. Working with Goal Seek
          1. How Does Goal Seek Work?
          2. Running Goal Seek
          3. Optimizing Product Margin
          4. A Note About Goal Seek’s Approximations
          5. Performing a Break-Even Analysis
          6. Solving Algebraic Equations
        3. Working with Scenarios
          1. Understanding Scenarios
          2. Setting Up Your Worksheet for Scenarios
          3. Adding a Scenario
          4. Displaying a Scenario
          5. Editing a Scenario
          6. Merging Scenarios
          7. Generating a Summary Report
          8. Deleting a Scenario
          9. From Here
      4. 16. Using Regression to Track Trends and Make Forecasts
        1. Choosing a Regression Method
        2. Using Simple Regression on Linear Data
          1. Analyzing Trends Using Best-Fit Lines
            1. Plotting a Best-Fit Trendline
            2. Understanding the Regression Equation
            3. Understanding R2
            4. Calculating Best-Fit Values Using TREND()
            5. Calculating Best-Fit Values Using LINEST()
            6. Analyzing the Sales Versus Advertising Trend
          2. Making Forecasts
            1. Plotting Forecasted Values
            2. Extending a Linear Trend with the Fill Handle
            3. Extending a Linear Trend Using the Series Command
            4. Forecasting with the Regression Equation
            5. Forecasting with TREND()
            6. Forecasting with LINEST()
        3. Using Simple Regression on Nonlinear Data
          1. Working with an Exponential Trend
            1. Plotting an Exponential Trendline
            2. Calculating Exponential Trend and Forecast Values
            3. Exponential Trending and Forecasting Using the GROWTH() Function
          2. Working with a Logarithmic Trend
            1. Plotting a Logarithmic Trendline
            2. Calculating Logarithmic Trend and Forecast Values
          3. Working with a Power Trend
            1. Plotting a Power Trendline
            2. Calculating Power Trend and Forecast Values
          4. Using Polynomial Regression Analysis
            1. Plotting a Polynomial Trendline
            2. Calculating Polynomial Trend and Forecast Values
        4. Using Multiple Regression Analysis
          1. From Here
      5. 17. Solving Complex Problems with Solver
        1. Some Background on Solver
          1. The Advantages of Solver
          2. When Do You Use Solver?
        2. Loading Solver
        3. Using Solver
        4. Adding Constraints
        5. Saving a Solution as a Scenario
        6. Setting Other Solver Options
          1. Controlling Solver
          2. Selecting the Method Solver Uses
          3. Working with Solver Models
        7. Making Sense of Solver’s Messages
        8. Displaying Solver’s Reports
          1. The Answer Report
          2. The Sensitivity Report
          3. The Limits Report
          4. From Here
    10. IV. Building Financial Formulas
      1. 18. Building Loan Formulas
        1. Understanding the Time Value of Money
        2. Calculating the Loan Payment
          1. Loan Payment Analysis
          2. Working with a Balloon Loan
          3. Calculating Interest Costs, Part I
          4. Calculating the Principal and Interest
          5. Calculating Interest Costs, Part 2
          6. Calculating Cumulative Principal and Interest
        3. Building a Loan Amortization Schedule
          1. Building a Fixed-Rate Amortization Schedule
          2. Building a Dynamic Amortization Schedule
        4. Calculating the Term of the Loan
        5. Calculating the Interest Rate Required for a Loan
        6. Calculating How Much You Can Borrow
          1. From Here
      2. 19. Building Investment Formulas
        1. Working with Interest Rates
          1. Understanding Compound Interest
          2. Nominal Versus Effective Interest
          3. Converting Between the Nominal Rate and the Effective Rate
        2. Calculating the Future Value
          1. The Future Value of a Lump Sum
          2. The Future Value of a Series of Deposits
          3. The Future Value of a Lump Sum Plus Deposits
        3. Working Toward an Investment Goal
          1. Calculating the Required Interest Rate
          2. Calculating the Required Number of Periods
          3. Calculating the Required Regular Deposit
          4. Calculating the Required Initial Deposit
          5. Calculating the Future Value with Varying Interest Rates
          6. From Here
      3. 20. Building Discount Formulas
        1. Calculating the Present Value
          1. Taking Inflation into Account
          2. Calculating Present Value Using PV()
          3. Income Investing Versus Purchasing a Rental Property
          4. Buying Versus Leasing
        2. Discounting Cash Flows
          1. Calculating the Net Present Value
          2. Calculating Net Present Value Using NPV()
          3. Net Present Value with Varying Cash Flows
          4. Net Present Value with Nonperiodic Cash Flows
        3. Calculating the Payback Period
          1. Simple Undiscounted Payback Period
          2. Exact Undiscounted Payback Point
          3. Discounted Payback Period
        4. Calculating the Internal Rate of Return
          1. Using the IRR() Function
          2. Calculating the Internal Rate of Return for Nonperiodic Cash Flows
          3. Calculating Multiple Internal Rates of Return
          4. From Here