Microsoft Excel 2019: Formulas And Functions
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
Handling formula errors with IFERROR() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57Using the formula error checker. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58Choosing an error action. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58Setting error checker options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59Auditing a worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62Understanding auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62Tracing cell precedents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63Tracing cell dependents. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63Tracing cell errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64Removing tracer arrows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64Evaluating formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64Watching cell values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65Part IIHarnessing the power of functions67Chapter 4Understanding functions69About Excel’s functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70The structure of a function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70Typing a function into a formula. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73Using the Insert Function feature. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74Loading the Analysis ToolPak. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76Chapter 5Working with text functions79Excel’s text functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79Working with characters and codes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80The CHAR() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81The CODE() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83Converting text. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .84The LOWER() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84The UPPER() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84The PROPER() function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84The NUMBERVALUE() function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85Formatting text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85The DOLLAR() function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86The FIXED() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87Contentsix
The TEXT() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87Displaying when a workbook was last updated. . . . . . . . . . . . . . . . . . . 89Manipulating text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89Removing unwanted characters from a string. . . . . . . . . . . . . . . . . . . . 89The TEXTJOIN() function: Concatenating text with a delimiter. . . . . 91The REPT() function: Repeating a character or string. . . . . . . . . . . . . 92Extracting a substring. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94Converting text to sentence case. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95A date-conversion formula. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95Searching for substrings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97The FIND() and SEARCH() functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97Extracting a first name or last name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97Extracting first name, last name, and middle initial . . . . . . . . . . . . . . . 99Determining the column letter. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99Substituting one substring for another. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100The REPLACE() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100The SUBSTITUTE() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101Removing a character from a string. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101Removing two different characters from a string. . . . . . . . . . . . . . . . 102Removing line feeds. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103Chapter 6Working with logical and information functions105Adding intelligence with logical functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105Using the IF() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106Performing multiple logical tests. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109Combining logical functions with arrays . . . . . . . . . . . . . . . . . . . . . . . . . 116Getting data with information functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124The CELL() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125The ERROR.TYPE() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128The INFO() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129The SHEET() and SHEETS() functions . . . . . . . . . . . . . . . . . . . . . . . . . . 130The IS functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130Chapter 7Working with lookup functions135Taking a look at Excel’s lookup functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135Understanding lookup tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136xContents
The CHOOSE() function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137Determining the name of the day of the week. . . . . . . . . . . . . . . . . . . 137Determining the month of the fiscal year . . . . . . . . . . . . . . . . . . . . . . . 138Calculating weighted questionnaire results . . . . . . . . . . . . . . . . . . . . . 139Integrating CHOOSE() and worksheet option buttons. . . . . . . . . . . . 139Looking up values in tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140The VLOOKUP() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140The HLOOKUP() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141Returning a customer discount rate with a range lookup. . . . . . . . . 142Returning a tax rate with a range lookup. . . . . . . . . . . . . . . . . . . . . . . . 143Finding exact matches. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144Advanced lookup operations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146The MATCH() and INDEX() functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . 146Chapter 8Working with date and time functions153How Excel deals with dates and times. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153Entering dates and times. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154Excel and two-digit years . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155Using Excel’s date functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156Returning a date. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157Returning parts of a date. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159Calculating the difference between two dates. . . . . . . . . . . . . . . . . . . 169Using Excel’s time functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172Returning a time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173Returning parts of a time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174Calculating the difference between two times. . . . . . . . . . . . . . . . . . . 176Chapter 9Working with math functions183Excel’s math and trig functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183Understanding Excel’s rounding functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . 186The ROUND() function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186The MROUND() function . . . . . . . . . . . . . . . . . . . .
Microsoft Excel 2019 Formulas and Functions. Published with the authorization of Microsoft Corporation by: Pearson Education, Inc. . Formula limits in Excel 2019 . 4. Entering and editing formulas . 4. Using arithmetic formulas .
Excel 5.0 Excel 5.0 1993 Excel 5.0 1993 Excel 7.0 Excel 95 1995 Excel 8.0 Excel 97 1997 Excel 98 1998 Excel 9.0 Excel 2000 1999 Excel 2001 2000 Excel 10.0 Excel XP 2001 Excel v.X 2001 Excel 11.0 Excel 2003 2003 Excel 2004 2004 2.1.2 Worksheet Document Definition: Worksheet Document A worksheet document consists of a single sheet only.
A Note about Array formulas (not for Excel 365 / Excel 2021) Sometimes, you will need to enter a formula as array formula. In Excel 365/Excel 2021, all formulas are treated as Array formula, hence you need not enter any formula as Array formula. Only for older versions of Excel, you might need to enter a formula as Array formula.
Microsoft Excel can also be used to balance a checkbook, create an expense report, build formulas, and edit them. CREATING A NEW DOCUMENT OPENING MICROSOFT EXCEL ON A PC To begin Microsoft Excel, Go to Start All Programs Applications Microsoft Office Microsoft Excel
Power Map Power Map provides a new perspective for your data by plotting geocoded data onto a three-dimensional view of the earth and optionally showing changes to that data over time. To use Power Map, you import raw data into a Microsoft Excel 2013 workbook, add the data to an Excel data model, and enhance the data in the data model if necessary.File Size: 1MBPage Count: 17Explore furtherGetting an excel list of all Azure Virtual machinesdbaharrison.blogspot.comDownload Azure Devops Board To Excelwww.how-use-excel.comGetting an excel list of all Azure Virtual machines .www.firstcloud.ioGetting an excel list of all Azure Virtual machines .laptrinhx.comRunning Excel On Azurewww.how-use-excel.comRecommended to you based on what's popular Feedback
Why should you learn Excel Formulas? Thanks for downloading this book. Excel is part of the Microsoft Office suite and the world’s most widely used spreadsheet application used by Millions. Compared to other Spreadsheet programs Excel is very feature rich and popular and incorporated with
Excel in Corporate World with Microsoft Excel Presented by: Anil Dhawan Overview: Getting Started with Excel - 02 hoursOverview: Getting Started with Excel - 02 hours Formatting Essentials - 02 hours Functions & Formulas - 05 hours Data Analysis Tools - 01 hour Excel Charts - 0.5 hour Pivot Tabl
1. Memulai Excel 2003 Untuk membuka aplikasi Excel 2003 kita dapat mengklik icon Excel yang ada pada layer desktop atau menggunakan menu Start All Program Microsoft Office Microsoft Office Excel 2003 hingga muncul tampilan lembar kerja Excel. Menu Microsoft Excel
IGCSE – Accounting 0452 9 reputation of the firm which equal the difference between the net assets and selling price of the firm. 16. Direct expense of manufacturing There are any expenses which a manufacturer can directly link with the product begin manufactured 17. Appropriation account That account which shows how the profit for the year has been used 18. Collection period for trade .
