101 Ready-to-Use Excel Formulas

285 Pages·2016·5.81 MB·English

by  Alexander Michael

101 Ready-to-Use Excel® Formulas
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.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 form 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 Permissions Department, John Wiley & Sons, Inc., 111
River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at
http://www.wiley.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 [phone]. 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.wiley.com. For more information about Wiley products, visit www.wiley.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 9 8 7 6 5 4 3 2 1
101 Ready-to-Use Excel Formulas

Table of Contents
1.
Introduction
1.
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
Chapter 1: Introducing Excel Formulas
2.
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
3.
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
Chapter 3: Manipulating Text with Formulas
4.
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
Chapter 4: Working with Dates and Times
5.
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
Chapter 5: Performing Conditional Analysis
6.
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
Chapter 6: Using Lookup Formulas
7.
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