101 Ready-To-Use Excel Formulas.

Intro -- Contents at a Glance -- Table of Contents -- Introduction -- Chapter 1: Introducing Excel Formulas -- Creating and Editing Excel Formulas -- Using Formula Operators -- Relative versus Absolute Cell References -- Using External Cell References -- Formula Calculation Modes -- Leveraging Excel Functions -- Understanding Formula Errors -- Using Named Ranges in Formulas -- Chapter 2: Common Mathematical Operations -- Formula 1: Calculating Percent of Goal -- Formula 2: Calculating Percent Variance -- Formula 3: Calculating Percent Variance with Negative Values -- Formula 4: Calculating a Percent Distribution -- Formula 5: Calculating a Running Total -- Formula 6: Applying a Percent Increase or Decrease to Values -- Formula 7: Dealing with Divide-by-Zero Errors -- Formula 8: Basic Rounding of Numbers -- Formula 9: Rounding to the Nearest Penny -- Formula 10: Rounding to Significant Digits -- Formula 11: Counting Values in a Range -- Formula 12: Creating a Conversion Table -- Chapter 3: Manipulating Text with Formulas -- Formula 13: Joining Text Strings -- Formula 14: Setting Text to Sentence Case -- Formula 15: Removing Spaces from a Text String -- Formula 16: Extract Parts of a Text String -- Formula 17: Finding a Particular Character in a Text String -- Formula 18: Substituting Text Strings -- Formula 19: Counting Specific Characters in a Cell -- Formula 20: Adding a Line Break within a Formula -- Formula 21: Cleaning Strange Characters from Text Fields -- Formula 22: Padding Numbers with Zeros -- Formula 23: Formatting the Numbers in a Text String -- Chapter 4: Working with Dates and Times -- Formula 24: Getting the Current Date and Time -- Formula 25: Calculating Age -- Formula 26: Calculating the Number of Days between Two Dates -- Formula 27: Calculating the Number of Workdays between Two Dates.

Formula 28: Generate a List of Business Days Excluding Holidays -- Formula 29: Extracting Parts of a Date -- Formula 30: Calculating the Number of Years and Months between Dates -- Formula 31: Converting Dates to Julian Date Formats -- Formula 32: Calculating the Percent of Year Completed and Remaining -- Formula 33: Returning the Last Date of a Given Month -- Formula 34: Calculating the Calendar Quarter for a Date -- Formula 35: Calculating the Fiscal Quarter for a Date -- Formula 36: Returning a Fiscal Month from a Date -- Formula 37: Calculate the Date of the Nth Weekday of the Month -- Formula 38: Calculate the Date of the Last Weekday of the Month -- Formula 39: Extracting Parts of a Time -- Formula 40: Calculating Elapsed Time -- Formula 41: Rounding Time Values -- Formula 42: Converting Decimal Hours, Minutes, or Seconds to a Time -- Formula 43: Adding Hours, Minutes, or Seconds to a Time -- Chapter 5: Performing Conditional Analysis -- Formula 44: Check to See Whether a Simple Condition Is Met -- Formula 45: Checking for Multiple Conditions -- Formula 46: Check Whether Condition1 AND Condition2 Are Met -- Formula 47: Check Whether Condition1 OR Condition2 Is Met -- Formula 48: Sum All Values That Meet a Certain Condition -- Formula 49: Sum All Values That Meet Two or More Conditions -- Formula 50: Sum Values That Fall between a Given Date Range -- Formula 51: Get a Count of Values That Meet a Certain Condition -- Formula 52: Get a Count of Values That Meet Two or More Conditions -- Formula 53: Get the Average of All Numbers That Meet a Certain Condition -- Formula 54: Get the Average of All Numbers That Meet Two or More Conditions -- Chapter 6: Using Lookup Formulas -- Formula 55: Looking Up an Exact Value Based on a Left Lookup Column -- Formula 56: Looking Up an Exact Value Based on Any Lookup Column.

Formula 57: Looking Up Values Horizontally -- Formula 58: Hiding Errors Returned by Lookup Functions -- Formula 59: Finding the Closest Match from a List of Banded Values -- Formula 60: Looking Up Values from Multiple Tables -- Formula 61: Looking Up a Value Based on a Two-Way Matrix -- Formula 62: Finding a Value Based on Multiple Criteria -- Formula 63: Finding the Last Value in a Column -- Formula 64: Look Up the Nth Instance of a Criterion -- Formula 65: Performing a Case-Sensitive Lookup -- Formula 66: Letting the User Select How to Aggregate Data -- Chapter 7: Common Business and Financial Formulas -- Formula 67: Calculating Gross Profit Margin and Gross Profit Margin Percent -- Formula 68: Calculating EBIT and EBITDA -- Formula 69: Calculating Cost of Goods Sold -- Formula 70: Calculating Return on Assets -- Formula 71: Calculating Break Even -- Formula 72: Calculating Customer Churn -- Formula 73: Calculating Average Customer Lifetime Value -- Formula 74: Calculating Employee Turnover -- Formula 75: Converting Interest Rates -- Formula 76: Creating a Loan Payment Calculator -- Formula 77: Creating a Variable-Rate Mortgage Amortization Schedule -- Formula 78: Calculating Depreciation -- Formula 79: Calculating Present Value -- Formula 80: Calculating Net Present Value -- Formula 81: Calculating an Internal Rate of Return -- Chapter 8: Common Statistical Analyses -- Formula 82: Calculating a Weighted Average -- Formula 83: Smoothing Data with Moving Averages -- Formula 84: Applying Exponential Smoothing to Volatile Data -- Formula 85: Getting the Largest or Smallest Value -- Formula 86: Getting the Nth Largest or Smallest Value -- Formula 87: Calculating Mean, Median, and Mode -- Formula 88: Bucketing Data into Percentiles -- Formula 89: Identifying Statistical Outliers with an Interquartile Range.

Formula 90: Creating a Frequency Distribution -- Formula 91: De-Seasonalize your Data before Forecasting -- Formula 92: Create a Trendline Forecast -- Chapter 9: Using Formulas with Conditional Formatting -- Formula 93: Highlight Cells That Meet Certain Criteria -- Formula 94: Highlight Cells Based on the Value of Another Cell -- Formula 95: Highlight Values That Exist in List1 but not List2 -- Formula 96: Highlight Values That Exist in List1 and List2 -- Formula 97: Highlight Weekend Dates -- Formula 98: Highlight Days between Two Dates -- Formula 99: Highlight Dates Based on Due Date -- Formula 100: Highlight Data Based on Percentile Rank -- Formula 101: Highlight Statistical Outliers -- Index -- About the Authors -- End User License Agreement.

Description based on publisher supplied metadata and other sources.

Electronic reproduction. Ann Arbor, Michigan : ProQuest Ebook Central, 2024. Available via World Wide Web. Access may be limited to ProQuest Ebook Central affiliated libraries.