## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

## 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.

1. Introduction
1. What You Need to Know
2. What You Need to Have
3. How This Book Is Organized
4. Conventions in This Book
2. Chapter 1: Introducing Excel Formulas
1. Creating and Editing Excel Formulas
2. Using Formula Operators
3. Relative versus Absolute Cell References
4. Using External Cell References
5. Formula Calculation Modes
6. Leveraging Excel Functions
7. Understanding Formula Errors
8. Using Named Ranges in Formulas
3. Chapter 2: Common Mathematical Operations
1. Formula 1: Calculating Percent of Goal
2. Formula 2: Calculating Percent Variance
3. Formula 3: Calculating Percent Variance with Negative Values
4. Formula 4: Calculating a Percent Distribution
5. Formula 5: Calculating a Running Total
6. Formula 6: Applying a Percent Increase or Decrease to Values
7. Formula 7: Dealing with Divide-by-Zero Errors
8. Formula 8: Basic Rounding of Numbers
9. Formula 9: Rounding to the Nearest Penny
10. Formula 10: Rounding to Significant Digits
11. Formula 11: Counting Values in a Range
12. Formula 12: Creating a Conversion Table
4. Chapter 3: Manipulating Text with Formulas
1. Formula 13: Joining Text Strings
2. Formula 14: Setting Text to Sentence Case
3. Formula 15: Removing Spaces from a Text String
4. Formula 16: Extract Parts of a Text String
5. Formula 17: Finding a Particular Character in a Text String
6. Formula 18: Substituting Text Strings
7. Formula 19: Counting Specific Characters in a Cell
8. Formula 20: Adding a Line Break within a Formula
9. Formula 21: Cleaning Strange Characters from Text Fields
10. Formula 22: Padding Numbers with Zeros
11. Formula 23: Formatting the Numbers in a Text String
5. Chapter 4: Working with Dates and Times
1. Formula 24: Getting the Current Date and Time
2. Formula 25: Calculating Age
3. Formula 26: Calculating the Number of Days between Two Dates
4. Formula 27: Calculating the Number of Workdays between Two Dates
5. Formula 28: Generate a List of Business Days Excluding Holidays
6. Formula 29: Extracting Parts of a Date
7. Formula 30: Calculating the Number of Years and Months between Dates
8. Formula 31: Converting Dates to Julian Date Formats
9. Formula 32: Calculating the Percent of Year Completed and Remaining
10. Formula 33: Returning the Last Date of a Given Month
11. Formula 34: Calculating the Calendar Quarter for a Date
12. Formula 35: Calculating the Fiscal Quarter for a Date
13. Formula 36: Returning a Fiscal Month from a Date
14. Formula 37: Calculate the Date of the Nth Weekday of the Month
15. Formula 38: Calculate the Date of the Last Weekday of the Month
16. Formula 39: Extracting Parts of a Time
17. Formula 40: Calculating Elapsed Time
18. Formula 41: Rounding Time Values
19. Formula 42: Converting Decimal Hours, Minutes, or Seconds to a Time
20. Formula 43: Adding Hours, Minutes, or Seconds to a Time
6. Chapter 5: Performing Conditional Analysis
1. Formula 44: Check to See Whether a Simple Condition Is Met
2. Formula 45: Checking for Multiple Conditions
3. Formula 46: Check Whether Condition1 AND Condition2 Are Met
4. Formula 47: Check Whether Condition1 OR Condition2 Is Met
5. Formula 48: Sum All Values That Meet a Certain Condition
6. Formula 49: Sum All Values That Meet Two or More Conditions
7. Formula 50: Sum Values That Fall between a Given Date Range
8. Formula 51: Get a Count of Values That Meet a Certain Condition
9. Formula 52: Get a Count of Values That Meet Two or More Conditions
10. Formula 53: Get the Average of All Numbers That Meet a Certain Condition
11. Formula 54: Get the Average of All Numbers That Meet Two or More Conditions
7. Chapter 6: Using Lookup Formulas
1. Formula 55: Looking Up an Exact Value Based on a Left Lookup Column
2. Formula 56: Looking Up an Exact Value Based on Any Lookup Column
3. Formula 57: Looking Up Values Horizontally
4. Formula 58: Hiding Errors Returned by Lookup Functions
5. Formula 59: Finding the Closest Match from a List of Banded Values
6. Formula 60: Looking Up Values from Multiple Tables
7. Formula 61: Looking Up a Value Based on a Two-Way Matrix
8. Formula 62: Finding a Value Based on Multiple Criteria
9. Formula 63: Finding the Last Value in a Column
10. Formula 64: Look Up the Nth Instance of a Criterion
11. Formula 65: Performing a Case-Sensitive Lookup
12. Formula 66: Letting the User Select How to Aggregate Data
8. Chapter 7: Common Business and Financial Formulas
1. Formula 67: Calculating Gross Profit Margin and Gross Profit Margin Percent
2. Formula 68: Calculating EBIT and EBITDA
3. Formula 69: Calculating Cost of Goods Sold
4. Formula 70: Calculating Return on Assets
5. Formula 71: Calculating Break Even
6. Formula 72: Calculating Customer Churn
7. Formula 73: Calculating Average Customer Lifetime Value
8. Formula 74: Calculating Employee Turnover
9. Formula 75: Converting Interest Rates
10. Formula 76: Creating a Loan Payment Calculator
11. Formula 77: Creating a Variable-Rate Mortgage Amortization Schedule
12. Formula 78: Calculating Depreciation
13. Formula 79: Calculating Present Value
14. Formula 80: Calculating Net Present Value
15. Formula 81: Calculating an Internal Rate of Return
9. Chapter 8: Common Statistical Analyses
1. Formula 82: Calculating a Weighted Average
2. Formula 83: Smoothing Data with Moving Averages
3. Formula 84: Applying Exponential Smoothing to Volatile Data
4. Formula 85: Getting the Largest or Smallest Value
5. Formula 86: Getting the Nth Largest or Smallest Value
6. Formula 87: Calculating Mean, Median, and Mode
7. Formula 88: Bucketing Data into Percentiles
8. Formula 89: Identifying Statistical Outliers with an Interquartile Range
9. Formula 90: Creating a Frequency Distribution
10. Formula 91: De-Seasonalize your Data before Forecasting
11. Formula 92: Create a Trendline Forecast
10. Chapter 9: Using Formulas with Conditional Formatting
1. Formula 93: Highlight Cells That Meet Certain Criteria
2. Formula 94: Highlight Cells Based on the Value of Another Cell
3. Formula 95: Highlight Values That Exist in List1 but not List2
4. Formula 96: Highlight Values That Exist in List1 and List2
5. Formula 97: Highlight Weekend Dates
6. Formula 98: Highlight Days between Two Dates
7. Formula 99: Highlight Dates Based on Due Date
8. Formula 100: Highlight Data Based on Percentile Rank
9. Formula 101: Highlight Statistical Outliers