101 Ready to Use Excel Formulas By John Wiley Free eBook Download

Formulas are the real engines of Excel. Employing various Excel functions, formulas enable Excel analysts to create aggregated reporting, complex calculation engines, clever dashboard models, and much more. Indeed, Excel analysts become more productive as their proficiency with Excel functions and formulas improves.

Download This Book: CLICK HERE

But building proficiency with Excel functions and formulas takes time. Given that Excel contains more than 400 functions, you could spend months, even years, learning which functions are best for certain tasks and which features can be combined with others functions.


Unfortunately, many analysts don’t have the luxury of taking a few weeks’ time-out to learn all they need to know about Excel functions and formulas. The scenarios and issues they face require solutions now. This is where 101 Ready-to-Use Excel Formulas comes in. This book approaches
Excel formulas with the assumption that “learning” comes with accomplishing core tasks. Instead of offering the general overview of Excel formula writing, this book provides 101 of the most commonly used, real-world Excel formulas.

Download This Book: CLICK HERE

Table of Contents

Introduction

• What You Need to Know
• What You Need to Have
• How This Book Is Organized

Chapter 1: Introducing Excel Formulas
Chapter 2: Common Mathematical Operations
Chapter 3: Manipulating Text with Formulas
Chapter 4: Working with Dates and Times
Chapter 5: Performing Conditional Analysis
Chapter 6: Using Lookup Formulas
Chapter 7: Common Business and Financial Formulas
Chapter 8: Common Statistical Analysis
Chapter 9: Using Formulas with Conditional Formatting

Conventions in This Book

• What the icons mean

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

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 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. Method 3: Calculating Percent Variance with Negative Values
• 1. How it works
• 4. Method 4: Calculating a Percent Distribution
• 1. How it works
• 2. Alternative: Percent distribution without a Total dedicated cell
• 5. Method 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

Download This Book: CLICK HERE

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. Option 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 UpValues from Multiple Tables

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

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

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