Excel 2016 Formulas and Functions

Excel 2016 Formulas and Functions

by Paul McFedries
Released October 2015
Publisher(s): Que
ISBN: 9780134269078

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Master core Excel 2016 tools for building powerful, reliable spreadsheets with Excel 2016 Formulas and Functions. Excel expert Paul McFedries shows how to use Excel 2016’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 features and improvements ranging from AutoFill to Excel’s newest functions. Along the way, you 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.

  • Quickly create powerful spreadsheets with range names and array 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

  • Validate data, troubleshoot problems, and build more accurate, trustworthy spreadsheets

    •

    Table of contents

    1. About This eBook
    2. Title Page
    3. Copyright Page
    4. Contents at a Glance
    5. Contents
    6. About the Author
    7. Dedication
    8. Acknowledgments
    9. We Want to Hear from You!
    10. Reader Services
    11. Introduction
      1. What’s in the Book
      2. This Book’s Special Features
    12. Part 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
        2. Data Entry in a Range
        3. Filling a Range
          1. Using the Fill Handle
          2. Flash-Filling a Range
        4. Creating a Series
        5. Advanced Range Copying and Pasting
          1. Pasting Selected Cell Attributes
          2. Combining Two Ranges Arithmetically
          3. Transposing Rows and Columns
        6. Clearing a Range
        7. 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
        8. 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 AutoComplete for Range Names
          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
          9. Changing a Range Name
          10. Deleting a Range Name
          11. Using Names with the Intersection Operator
        3. From Here
      3. 3. Building Basic Formulas
        1. Understanding Formula Basics
          1. Formula Limits in Excel 2016
          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
          1. Displaying All Worksheet Formulas
          2. Displaying a Cell’s Formula by Using FORMULATEXT()
        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
          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
          2. Date and Time Display Formats
          3. Deleting Custom Formats
        10. From Here
      4. 4. Creating Advanced Formulas
        1. Working with Arrays
          1. Using Array Formulas
          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. Displaying the Developer Tab
          2. Using the Form Controls
          3. Adding a Control to a Worksheet
          4. Linking a Control to a Cell Value
          5. Understanding the Worksheet Controls
        6. From Here
      5. 5. Troubleshooting Formulas
        1. Understanding Excel’s Error Values
          1. #DIV/0!
          2. #N/A
          3. #NAME?
          4. Case Study: Avoiding #NAME? Errors When Deleting Range Names
          5. #NULL!
          6. #NUM!
          7. #REF!
          8. #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
        6. From Here
    13. Part 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
        6. From Here
      2. 7. Working with Text Functions
        1. Excel’s Text Functions
        2. Working with Characters and Codes
          1. The CHAR() Function
          2. The CODE() Function
        3. Converting Text
          1. The LOWER() Function
          2. The UPPER() Function
          3. The PROPER() Function
          4. The NUMBERVALUE() 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
          1. Removing Unwanted Characters from a String
          2. The REPT() Function: Repeating a Character or String
          3. Extracting a Substring
          4. Converting Text to Sentence Case
          5. A Date-Conversion Formula
          6. Case Study: Generating Account Numbers, Part I
        6. 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
        7. 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. Case Study: Generating Account Numbers, Part II
          6. Removing Line Feeds
        8. From Here
      3. 8. Working with Logical and Information Functions
        1. Adding Intelligence with Logical Functions
          1. Using the IF() Function
          2. Performing Multiple Logical Tests
          3. Combining Logical Functions with Arrays
          4. Case Study: Building an Accounts Receivable Aging Worksheet
        2. Getting Data with Information Functions
          1. The CELL() Function
          2. The ERROR.TYPE() Function
          3. The INFO() Function
          4. The SHEET() and SHEETS() Functions
          5. The IS Functions
        3. From Here
      4. 9. Working with Lookup Functions
        1. Excel’s Lookup Functions
        2. Understanding Lookup Tables
        3. 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
        4. 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
          6. Advanced Lookup Operations
        5. 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
          2. Returning Parts of a Date
          3. Calculating the Difference Between Two Dates
        3. Using Excel’s Time Functions
          1. Returning a Time
          2. Returning Parts of a Time
          3. Calculating the Difference Between Two Times
          4. Case Study: Building an Employee Time Sheet
        4. From Here
      6. 11. Working with Math Functions
        1. Excel’s Math and Trig Functions
        2. Understanding Excel’s Rounding Functions
          1. The ROUND() Function
          2. The MROUND() Function
          3. The ROUNDDOWN() and ROUNDUP() Functions
          4. The CEILING.MATH() and FLOOR.MATH() Functions
          5. The EVEN() and ODD() Functions
          6. The INT() and TRUNC() Functions
          7. Case Study: Rounding Billable Time
        3. Summing Values
          1. The SUM() Function
          2. Calculating Cumulative Totals
          3. Summing Only the Positive or Negative Values in a Range
        4. The MOD() Function
          1. A Better Formula for Time Differences
          2. Summing Every nth Row
          3. Determining Whether a Year Is a Leap Year
          4. Creating Ledger Shading
        5. Generating Random Numbers
          1. The RAND() Function
          2. The RANDBETWEEN() Function
        6. From Here
      7. 12. Working with Statistical Functions
        1. Excel’s Statistical Functions
        2. Understanding Descriptive Statistics
        3. Counting Items with the COUNT() Function
        4. Calculating Averages
          1. The AVERAGE() Function
          2. The MEDIAN() Function
          3. The MODE() Function
          4. Calculating the Weighted Mean
        5. 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
        6. Calculating Measures of Variation
          1. Calculating the Range
          2. Calculating the Variance
          3. Calculating the Standard Deviation
        7. 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
        8. 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
        9. From Here
    14. Part III: Building Business Models
      1. 13. Analyzing Data with Tables
        1. Planning an Excel Table
        2. Converting a Range to a Table
        3. Basic Table Operations
        4. Sorting a Table
          1. Performing a More Complex Sort
          2. Sorting a Table in Natural Order
          3. Sorting on Part of a Field
          4. Sorting Without Articles
        5. Filtering Table Data
          1. Using Filter Lists to Filter a Table
          2. Using Complex Criteria to Filter a Table
          3. Entering Computed Criteria
          4. Copying Filtered Data to a Different Range
        6. Referencing Tables in Formulas
          1. Using Table Specifiers
          2. Entering Table Formulas
        7. Excel’s Table Functions
          1. About Table Functions
          2. Table Functions That Don’t Require a Criteria Range
          3. Table Functions That Accept Multiple Criteria
          4. Table Functions That Require a Criteria Range
          5. Case Study: Applying Statistical Table Functions to a Defects Database
        8. 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
        7. 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 Data 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
        4. 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
          2. Making Forecasts
          3. Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model
        3. Using Simple Regression on Nonlinear Data
          1. Working with an Exponential Trend
          2. Working with a Logarithmic Trend
          3. Working with a Power Trend
          4. Using Polynomial Regression Analysis
        4. Using Multiple Regression Analysis
        5. 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. Selecting the Method Solver Uses
          2. Controlling How Solver Works
          3. Working with Solver Models
        7. Making Sense of Solver’s Messages
          1. Case Study: Solving the Transportation Problem
        8. Displaying Solver’s Reports
          1. The Answer Report
          2. The Sensitivity Report
          3. The Limits Report
        9. From Here
    15. Part IV: Building Financial Formulas
      1. 18. Building Loan Formulas
        1. Understanding the Time Value of Money
        2. Calculating a Loan Payment
          1. Loan Payment Analysis
          2. Working with a Balloon Loan
          3. Calculating Interest Costs, Part 1
          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 a Loan
        5. Calculating the Interest Rate Required for a Loan
        6. Calculating How Much You Can Borrow
          1. Case Study: Working with Mortgages
        7. 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. Case Study: Building an Investment Schedule
        4. 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. Case Study: Publishing a Book
        5. From Here
    16. Index
    17. Code Snippets

    Product information

    • Title: Excel 2016 Formulas and Functions
    • Author(s): Paul McFedries
    • Release date: October 2015
    • Publisher(s): Que
    • ISBN: 9780134269078