WIXLIB

Microsoft Excel 2019Formulas and FunctionsPaul McFedries

Microsoft Excel 2019 Formulas and FunctionsPublished with the authorization of Microsoft Corporation by:Pearson Education, Inc.Copyright 2019 by Pearson Education, Inc.All rights reserved. This publication is protected by copyright, and permission must be obtained from the publisher prior toany prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permissions, request forms, and the appropriate contacts within the Pearson Education Global Rights & Permissions Department, please visit www.pearsoned.com/permissions/.No patent liability is assumed with respect to the use of the information contained herein. Although every precaution hasbeen taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor isany liability assumed for damages resulting from the use of the information contained herein.ISBN-13: 978-1-5093-0619-0ISBN-10: 1-5093-0619-6Library of Congress Control Number: 20199306611 19TrademarksMicrosoft and the trademarks listed at http://www.microsoft.com on the “Trademarks” webpage are trademarks of theMicrosoft group of companies. All other marks are property of their respective owners.Warning and DisclaimerEvery effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied.The information provided is on an “as is” basis. The author, the publisher, and Microsoft Corporation shall have neitherliability nor responsibility to any person or entity with respect to any loss or damages arising from the information containedin this book.Special SalesFor information about buying this title in bulk quantities, or for special sales opportunities (which may include electronicversions; custom cover designs; and content particular to your business, training goals, marketing focus, or brandinginterests), please contact our corporate sales department at corpsales@pearsoned.com or (800) 382-3419.For government sales inquiries, please contact governmentsales@pearsoned.com.For questions about sales outside the U.S., please contact intlcs@pearson.com.Editor-in-Chief: Brett BartowExecutive Editor: Loretta YatesSponsoring Editor: Charvi AroraManaging Editor: Sandra SchroederSenior Project Editor: Tracey CroomProject Editor: Charlotte KughenIndexer: Cheryl LenserProofreader: Gill Editorial ServicesTechnical Editor: Bob UmlasPublishing Coordinator: Cindy TeetersCover Designer: Twist Creative, SeattleCompositor: Bronkella Publishing LLCGraphics: TJ Graham Art

To Karen and Chase

Contents at a GlanceIntroductionxxiiiPart IMastering Excel formulas1CHAPTER 1Building basic formulas3CHAPTER 2Creating advanced formulas25CHAPTER 3Troubleshooting formulas49Part IIHarnessing the power of functions67CHAPTER 4Understanding functions69CHAPTER 5Working with text functionsCHAPTER 6Working with logical and information functions105CHAPTER 7Working with lookup functions135CHAPTER 8Working with date and time functions153CHAPTER 9Working with math functions183Part IIIBuilding business formulas205CHAPTER 10Implementing basic business formulas207CHAPTER 11Building descriptive statistical formulas229CHAPTER 12Building inferential statistical formulas245CHAPTER 13Applying regression to track trends and make forecasts265CHAPTER 14Building loan formulas305CHAPTER 15Working with investment formulas325CHAPTER 16Building discount formulas339Part IVBuilding business models359CHAPTER 17Analyzing data with tables361CHAPTER 18Analyzing data with PivotTables381CHAPTER 19Using Excel’s business modeling tools399CHAPTER 20Solving complex problems with Solver421Index44379v

ContentsIntroduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiiiPart IMastering Excel formulas1Chapter 1Building basic formulas3Understanding formula basics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3Formula limits in Excel 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4Entering and editing formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4Using arithmetic formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5Using comparison formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6Using text formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6Using reference formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6Understanding operator precedence. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7The order of precedence. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7Controlling the order of precedence. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8Controlling worksheet calculation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9Copying and moving formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11Understanding relative reference format. . . . . . . . . . . . . . . . . . . . . . . . . 12Understanding absolute reference format . . . . . . . . . . . . . . . . . . . . . . . . 13Copying a formula without adjusting relative references. . . . . . . . . . 14Displaying worksheet formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15Displaying all worksheet formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15Displaying a cell’s formula by using FORMULATEXT(). . . . . . . . . . . . . . 15Converting a formula to a value. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15Working with range names in formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16Pasting a name into a formula. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16Applying names to formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17Naming formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20Working with links in formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21Understanding external references. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22Updating links. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22Changing the link source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23vii

Chapter 2Creating advanced formulas25Working with arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25Using array formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26Using array constants. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30Functions that use or return arrays. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31Using iteration and circular references. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32Consolidating multisheet data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34Consolidating by position. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35Consolidating by category. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38Applying data-validation rules to cells. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40Using dialog box controls on a worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42Displaying the Developer tab. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42Using the form controls. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43Adding a control to a worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43Linking a control to a cell value. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43Understanding the worksheet controls . . . . . . . . . . . . . . . . . . . . . . . . . . 44Chapter 3Troubleshooting formulas49Understanding Excel’s error values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50#CALC!. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50#DIV/0! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50#FIELD! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51#N/A. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51#NAME?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51Avoiding #NAME? errors when deleting range names. . . . . . . . . . . . . . 52#NULL!. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53#NUM!. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53#REF!. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53#SPILL! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54#UNKNOWN!. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54#VALUE! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54Fixing other formula errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55Missing or mismatched parentheses. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55Erroneous formula results. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56Fixing circular references. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56viiiContents