[PDF]excel

101 Ready-to-Use

Excel


Formulas



John Walkenbachy Series Editor W 1 LEY




101

Ready-to-Use
Excel® Formulas


by Michael Alexander and Dick Kusleika


Wiley



101 Ready-to-Use Excel® Formulas

Published by: John Wiley & Sons, Inc., 1 1 1 River Street, Hoboken, NJ 07030-5774, www.wilev.com
Copyright © 2014 by John Wiley & Sons, Inc., Hoboken, New Jersey
Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any fomr or by any
means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections
107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests
to the Publisher for permission should be addressed to the Per mis sions Department, John Wiley & Sons, Inc., 1 1 1
River Street, Hoboken, NJ 07030, (201) 748-601 1, fax (201) 748-6008, or online at
http://www.wilev.com/go/permissions .

Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or
its affiliates in the United States and other countries and may not be used without written permission. Microsoft and
Excel are registered trademarks of the Microsoft Corporation. All other trademarks are the property of their
respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR
MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR
COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL
WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A
PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR
PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY
NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE
UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL,
ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS
REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE
SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES
ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO
IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER
INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE
INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS
IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES
LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS
WORK WAS WRITTEN AND WHEN IT IS READ. FULFILLMENT OF EACH COUPON OFFER IS
THE SOLE RESPONSIBILITY OF THE OFFEROR.

For general information on our other products and services, please contact our Customer Care Department within
the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit
www.wiley.com/techsupport .

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with
standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to
media such as a CD or DVD that is not included in the version you purchased, you may download this material at
http://booksupport.wilev.com . For more information about Wiley products, visit www.wilev.com .

Library of Congress Control Number: 2014935514

ISBN 978-1-118-90268-4 (pbk); ISBN 978-1-118-90259-2 (ebk); ISBN 978-1-118-90289-9 (ebk)

Manufactured in the United States of America


10 98765432 1


Ahashare.com

101 Ready-to-Use Excel Formulas


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

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-bv-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


Ahashare.com


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 NETWQRKDAYS.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 EQMQNTH 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

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 Condition 1 AND Condition2

Are Met

1 . How it works

2. Alternative 1: Referring to logical conditions in cells

4. Formula 47: Check Whether Condition 1 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

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 SUMPRQDUCT

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


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 chum 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 FY

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 cashflows

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

io. 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 Listl but not

List2

1 . How it works

4. Formula 96: Highlight Values That Exist in Listl 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


ii. About the Authors


Introduction

Formulas are the true 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.

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 functions 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 usual general overview of Excel formula writing, this
book provides 101 of the most commonly used, real-world Excel formulas.

For each formula covered, we outline a common problem that needs to be solved and
provide the actual Excel formula to solve the problem, along with detailed
explanations of how the formula works. This approach lets you use this book as a
handy reference for finding a formula that solves a common problem

After reading about a given formula, you should be able to


• Immediately implement the needed Excel formula

• Understand how the formula works

• Reuse the formula in other workbooks



What You Need to Know

To get the most out of this book, you need to have established certain skills before
diving in. The ideal candidate for this book has experience working with data in Excel
along with familiarity with the basic concepts of data analysis such as working with
tables, aggregating data, performing calculations, and creating charts.



What You Need to Have

You need the following to be able to download and use the examples highlighted
this book:


• A licensed copy of Excel 2010 or Excel 2013

• An Internet connection in order to download the sample files



Ahashare.com


How This Book Is Organized

We’ve grouped this book into nine chapters that are chock-full of tips, techniques, and
formulas dedicated to a particular topic.

Chapter 1: Introducing Excel Formulas

Chapter 1 serves as an introduction to Excel formulas, giving you an understanding of
how Excel formulas work and some of the ground rules for working with formulas.

Chapter 2: Common Mathematical Operations

In Chapter 2, you gain insight into some of the fundamental mathematical operations
every Excel analyst should know. The formulas found here serve as the foundation for
all kinds of advanced data analysis.

Chapter 3: Manipulating Text with Formulas

Chapter 3 focuses on the transformation and shaping of text strings. In this chapter, you
explore some of the common text transformation exercises an Excel analyst performs,
and in the process, you get a feel for many of the text-based functions Excel has to
offer.

Chapter 4: Working with Dates and Times

Chapter 4 gives you a solid understanding of how Excel handles time-based data.
Through the prism of the most commonly used date formulas, you discover how to
more effectively utilize the dates and times within your data sets.

Chapter 5: Performing Conditional Analysis

In Chapter 5, you take a look at a wide array of conditional analysis formulas that add
flexibility to your analytical processes. With the formulas found here, you’ll be able to
save time, organize your analytical processes, and enhance your data-crunching
>>>