101 Ready-to-Use Excel Formulas

101 Ready-to-Use Excel Formulas

by Dick Kusleika, Michael Alexander
Released August 2014
Publisher(s): Wiley
ISBN: 9781118902684

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

Mr. Spreadsheet has done it again with 101 easy-to-apply Excel formulas

101 Ready-to-Use Excel Formulas is filled with the most commonly-used, real-world Excel formulas that can be repurposed and put into action, saving you time and increasing your productivity. Each segment of this book outlines a common business or analysis problem that needs to be solved and provides the actual Excel formulas to solve the problem—along with detailed explanation of how the formulas work.

Written in a user-friendly style that relies on a tips and tricks approach, the book details how to perform everyday Excel tasks with confidence. 101 Ready-to-Use Excel Formulas is sure to become your well-thumbed reference to solve your workplace problems. The recipes in the book are structured to first present the problem, then provide the formula solution, and finally show how it works so that it can be customized to fit your needs. The companion website to the book allows readers to easily test the formulas and provides visual confirmation of the concepts presented.

  • Teaches you how to implement the required Excel formula

  • Explains and details how the formulas work

  • Lets you reuse or customize the given formula to address your particular needs

  • Helps you make the formulas a regular part of your new, more efficient workflow

    • Specific real-world scenarios are used to demonstrate how to most effectively apply Excel and its powerful formulas to complete tasks faster and with greater accuracy than ever before. Now you can save time, automate, and be more efficient and productive with 101 Ready-to-Use Excel Formulas.

    Table of contents

      1. Introduction
        1. What You Need to Know
        2. What You Need to Have
        3. How This Book Is Organized
          1. Chapter 1: Introducing Excel Formulas
          2. Chapter 2: Common Mathematical Operations
          3. Chapter 3: Manipulating Text with Formulas
          4. Chapter 4: Working with Dates and Times
          5. Chapter 5: Performing Conditional Analysis
          6. Chapter 6: Using Lookup Formulas
          7. Chapter 7: Common Business and Financial Formulas
          8. Chapter 8: Common Statistical Analysis
          9. Chapter 9: Using Formulas with Conditional Formatting
        4. Conventions in This Book
          1. What the icons mean
        5. About the Sample Files
      2. Chapter 1: Introducing Excel Formulas
        1. Creating and Editing Excel Formulas
          1. Methods for entering formulas
          2. Editing a formula
        2. Using Formula Operators
          1. Understanding the order of operator precedence
          2. Using nested parentheses
        3. Relative versus Absolute Cell References
        4. Using External Cell References
        5. Formula Calculation Modes
        6. Leveraging Excel Functions
          1. Why to use Excel functions
          2. Understanding function arguments
          3. Getting Help from the Insert Function Wizard
        7. Understanding Formula Errors
        8. Using Named Ranges in Formulas
          1. Creating a named range
          2. Working with the Name Box
      3. Chapter 2: Common Mathematical Operations
        1. Formula 1: Calculating Percent of Goal
          1. How it works
          2. Alternative: Using a common goal
        2. Formula 2: Calculating Percent Variance
          1. How it works
          2. Alternative: Simplified percent variance calculation
        3. Formula 3: Calculating Percent Variance with Negative Values
          1. How it works
        4. Formula 4: Calculating a Percent Distribution
          1. How it works
          2. Alternative: Percent distribution without a dedicated Total cell
        5. Formula 5: Calculating a Running Total
          1. How it works
        6. Formula 6: Applying a Percent Increase or Decrease to Values
          1. How it works
        7. Formula 7: Dealing with Divide-by-Zero Errors
          1. How it works
        8. Formula 8: Basic Rounding of Numbers
          1. How it works
        9. Formula 9: Rounding to the Nearest Penny
          1. How it works
        10. Formula 10: Rounding to Significant Digits
          1. How it works
        11. Formula 11: Counting Values in a Range
          1. How it works
        12. Formula 12: Creating a Conversion Table
          1. How it works
      4. Chapter 3: Manipulating Text with Formulas
        1. Formula 13: Joining Text Strings
          1. How it works
        2. Formula 14: Setting Text to Sentence Case
          1. How it works
        3. Formula 15: Removing Spaces from a Text String
          1. How it works
        4. Formula 16: Extract Parts of a Text String
          1. How it works
        5. Formula 17: Finding a Particular Character in a Text String
          1. How it works
          2. Alternative: Finding the second instance of a character
        6. Formula 18: Substituting Text Strings
          1. How it works
        7. Formula 19: Counting Specific Characters in a Cell
          1. How it works
        8. Formula 20: Adding a Line Break within a Formula
          1. How it works
        9. Formula 21: Cleaning Strange Characters from Text Fields
          1. How it works
        10. Formula 22: Padding Numbers with Zeros
          1. How it works
        11. Formula 23: Formatting the Numbers in a Text String
          1. How it works
          2. Alternative: Using the DOLLAR function
      5. Chapter 4: Working with Dates and Times
        1. Formula 24: Getting the Current Date and Time
          1. How it works
        2. Formula 25: Calculating Age
          1. How it works
        3. Formula 26: Calculating the Number of Days between Two Dates
          1. How it works
        4. Formula 27: Calculating the Number of Workdays between Two Dates
          1. How it works
          2. Alternative: Using NETWORKDAYS.INTL
        5. Formula 28: Generate a List of Business Days Excluding Holidays
          1. How it works
        6. Formula 29: Extracting Parts of a Date
          1. How it works
        7. Formula 30: Calculating the Number of Years and Months between Dates
          1. How it works
        8. Formula 31: Converting Dates to Julian Date Formats
          1. How it works
        9. Formula 32: Calculating the Percent of Year Completed and Remaining
          1. How it works
        10. Formula 33: Returning the Last Date of a Given Month
          1. How it works
          2. Alternative: Using the EOMONTH function
        11. Formula 34: Calculating the Calendar Quarter for a Date
          1. How it works
        12. Formula 35: Calculating the Fiscal Quarter for a Date
          1. How it works
        13. Formula 36: Returning a Fiscal Month from a Date
          1. How it works
        14. Formula 37: Calculate the Date of the Nth Weekday of the Month
          1. How it works
        15. Formula 38: Calculate the Date of the Last Weekday of the Month
          1. How it works
        16. Formula 39: Extracting Parts of a Time
          1. How it works
        17. Formula 40: Calculating Elapsed Time
          1. How it works
        18. Formula 41: Rounding Time Values
          1. How it works
        19. Formula 42: Converting Decimal Hours, Minutes, or Seconds to a Time
          1. How it works
        20. Formula 43: Adding Hours, Minutes, or Seconds to a Time
          1. How it works
      6. Chapter 5: Performing Conditional Analysis
        1. Formula 44: Check to See Whether a Simple Condition Is Met
          1. How it works
        2. Formula 45: Checking for Multiple Conditions
          1. How it works
          2. Alternative 1: Looking up values
        3. Formula 46: Check Whether Condition1 AND Condition2 Are Met
          1. How it works
          2. Alternative 1: Referring to logical conditions in cells
        4. Formula 47: Check Whether Condition1 OR Condition2 Is Met
          1. How it works
        5. Formula 48: Sum All Values That Meet a Certain Condition
          1. How it works
          2. Alternative 1: Summing greater than zero
        6. Formula 49: Sum All Values That Meet Two or More Conditions
          1. How it works
          2. Alternative: SUMPRODUCT
        7. Formula 50: Sum Values That Fall between a Given Date Range
          1. How it works
          2. Alternative 1: SUMIFS
          3. Alternative 2: SUMPRODUCT
        8. Formula 51: Get a Count of Values That Meet a Certain Condition
          1. How it works
          2. Alternative: SUMPRODUCT
        9. Formula 52: Get a Count of Values That Meet Two or More Conditions
          1. How it works
          2. Alternative: SUMPRODUCT
        10. Formula 53: Get the Average of All Numbers That Meet a Certain Condition
          1. How it works
          2. Alternative
        11. Formula 54: Get the Average of All Numbers That Meet Two or More Conditions
          1. How it works
          2. Alternative
      7. Chapter 6: Using Lookup Formulas
        1. Formula 55: Looking Up an Exact Value Based on a Left Lookup Column
          1. How it works
        2. Formula 56: Looking Up an Exact Value Based on Any Lookup Column
          1. How it works
          2. Alternative: The LOOKUP function
        3. Formula 57: Looking Up Values Horizontally
          1. How it works
          2. Alternative
        4. Formula 58: Hiding Errors Returned by Lookup Functions
          1. How it works
          2. Alternative: The ISNA Function
        5. Formula 59: Finding the Closest Match from a List of Banded Values
          1. How it works
          2. Alternative: INDEX and MATCH
        6. Formula 60: Looking Up Values from Multiple Tables
          1. How it works
        7. Formula 61: Looking Up a Value Based on a Two-Way Matrix
          1. How it works
          2. Alternative: Using default values for MATCH
        8. Formula 62: Finding a Value Based on Multiple Criteria
          1. How it works
          2. Alternative: Returning text with SUMPRODUCT
        9. Formula 63: Finding the Last Value in a Column
          1. How it works
          2. Alternative: Finding the last number using LOOKUP
        10. Formula 64: Look Up the Nth Instance of a Criterion
          1. How it works
        11. Formula 65: Performing a Case-Sensitive Lookup
          1. How it works
        12. Formula 66: Letting the User Select How to Aggregate Data
          1. How it works
          2. Alternative
      8. Chapter 7: Common Business and Financial Formulas
        1. Formula 67: Calculating Gross Profit Margin and Gross Profit Margin Percent
          1. How it works
          2. Alternative: Calculating Markup
        2. Formula 68: Calculating EBIT and EBITDA
          1. How it works
        3. Formula 69: Calculating Cost of Goods Sold
          1. How it works
        4. Formula 70: Calculating Return on Assets
          1. How it works
          2. Alternative: Calculating return on equity
        5. Formula 71: Calculating Break Even
          1. How it works
        6. Formula 72: Calculating Customer Churn
          1. How it works
          2. Alternative: Annual churn rate
        7. Formula 73: Calculating Average Customer Lifetime Value
          1. How it works
        8. Formula 74: Calculating Employee Turnover
          1. How it works
        9. Formula 75: Converting Interest Rates
          1. How it works
          2. Alternative: Computing effective rate with FV
        10. Formula 76: Creating a Loan Payment Calculator
          1. How it works
          2. Alternative: Creating an amortization schedule
        11. Formula 77: Creating a Variable-Rate Mortgage Amortization Schedule
          1. How it works
          2. Alternative: Using dates instead of payment numbers
        12. Formula 78: Calculating Depreciation
          1. How it works
          2. Alternative: Accelerated depreciation
        13. Formula 79: Calculating Present Value
          1. How it works
          2. Alternative: Calculating the present value of future payments
        14. Formula 80: Calculating Net Present Value
          1. How it works
          2. Alternative: Positive and negative cash flows
        15. Formula 81: Calculating an Internal Rate of Return
          1. How it works
          2. Alternative: Nonperiodic future cash flows
      9. Chapter 8: Common Statistical Analyses
        1. Formula 82: Calculating a Weighted Average
          1. How it works
          2. Alternative
        2. Formula 83: Smoothing Data with Moving Averages
          1. How it works
        3. Formula 84: Applying Exponential Smoothing to Volatile Data
          1. How it works
        4. Formula 85: Getting the Largest or Smallest Value
          1. How it Works
        5. Formula 86: Getting the Nth Largest or Smallest Value
          1. How it works
          2. Alternative
        6. Formula 87: Calculating Mean, Median, and Mode
          1. How it works
          2. Alternative
        7. Formula 88: Bucketing Data into Percentiles
          1. How it works
          2. Alternative
        8. Formula 89: Identifying Statistical Outliers with an Interquartile Range
          1. How it works
        9. Formula 90: Creating a Frequency Distribution
          1. How it works
          2. Alternative
        10. Formula 91: De-Seasonalize your Data before Forecasting
          1. How it works
        11. Formula 92: Create a Trendline Forecast
          1. How it works
      10. Chapter 9: Using Formulas with Conditional Formatting
        1. Formula 93: Highlight Cells That Meet Certain Criteria
          1. How it works
        2. Formula 94: Highlight Cells Based on the Value of Another Cell
          1. How it works
        3. Formula 95: Highlight Values That Exist in List1 but not List2
          1. How it works
        4. Formula 96: Highlight Values That Exist in List1 and List2
          1. How it works
        5. Formula 97: Highlight Weekend Dates
          1. How it works
        6. Formula 98: Highlight Days between Two Dates
          1. How it works
        7. Formula 99: Highlight Dates Based on Due Date
          1. How it works
        8. Formula 100: Highlight Data Based on Percentile Rank
          1. How it works
        9. Formula 101: Highlight Statistical Outliers
          1. How it works
      11. About the Authors

    Product information

    • Title: 101 Ready-to-Use Excel Formulas
    • Author(s): Dick Kusleika, Michael Alexander
    • Release date: August 2014
    • Publisher(s): Wiley
    • ISBN: 9781118902684