Transcription
Microsoft Excel DataAnalysis and BusinessModeling (Office 2021and Microsoft 365)7th EditionWayne WinstonA01 Winston FM pi-xxx.indd 127/10/21 2:20 PM
MICROSOFT EXCEL DATA ANALYSIS AND BUSINESS MODELINGEDITOR-IN-CHIEF(OFFICE 2021 AND MICROSOFT 365), 7TH EDITIONBrett BartowPublished with the authorization of Microsoft Corporation by:EXECUTIVE EDITORPearson Education, Inc.Loretta YatesCopyright 2022 by Pearson Education, Inc.All rights reserved. This publication is protected by copyright, and permission must beobtained from the publisher prior to any prohibited reproduction, storage in a retrievalsystem, 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 & PermissionsDepartment, please visit www.pearson.com/permissions.No patent liability is assumed with respect to the use of the information containedherein. Although every precaution has been taken in the preparation of this book, thepublisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.ISBN-13: 978-0-13-761366-3ISBN-10: 0-13-761366-0SPONSORING EDITORCharvi AroraDEVELOPMENT EDITORKate ShoupMANAGING EDITORSandra SchroederSENIOR PROJECT EDITORTracey CroomCOPY EDITORScout FestaINDEXERLibrary of Congress Control Number: 2021948290Erika MillenScoutAutomatedPrintCodePROOFREADERAbigail ManheimTrademarksMicrosoft and the trademarks listed at http://www.microsoft.com on the “Trademarks”webpage are trademarks of the Microsoft group of companies. All other marks areproperty of their respective owners.TECHNICAL EDITORDavid FransonEDITORIAL ASSISTANTCindy TeetersWarning and DisclaimerCOVER DESIGNEREvery 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. Theauthor, the publisher, and Microsoft Corporation shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from theinformation contained in this book or from the use of the programs accompanying it.Twist Creative, SeattleCOMPOSITORcodeMantraSpecial SalesFor information about buying this title in bulk quantities, or for special sales opportunities(which may include electronic versions; custom cover designs; and content particular toyour business, training goals, marketing focus, or branding interests), please contact ourcorporate 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.A01 Winston FM pi-xxx.indd 228/10/21 6:35 PM
Pearson’s Commitment to Diversity, Equity, andInclusionPearson is dedicated to creating bias-free content that reflects the diversityof all learners. We embrace the many dimensions of diversity, including butnot limited to race, ethnicity, gender, socioeconomic status, ability, age,sexual orientation, and religious or political beliefs.Education is a powerful force for equity and change in our world. It has thepotential to deliver opportunities that improve lives and enable economicmobility. As we work with authors to create content for every product andservice, we acknowledge our responsibility to demonstrate inclusivity andincorporate diverse scholarship so that everyone can achieve their potentialthrough learning. As the world’s leading learning company, we have a dutyto help drive change and live up to our purpose to help more people createa better life for themselves and to create a better world.Our ambition is to purposefully contribute to a world where: Everyone has an equitable and lifelong opportunity to succeed throughlearning.Our educational products and services are inclusive and represent therich diversity of learners.Our educational content accurately reflects the histories and experiences of thelearners we serve.Our educational content prompts deeper discussions with learners andmotivates them to expand their own learning (and worldview).While we work hard to present unbiased content, we want to hear fromyou about any concerns or needs with this Pearson product so that we caninvestigate and address them. Please contact us with concerns about any potential bias athttps://www.pearson.com/report-bias.html.iiiA01 Winston FM pi-xxx.indd 328/10/21 5:02 PM
DedicationTo Vivian, Jen, and Greg. You are all so great, and I love all of youso much!ivA01 Winston FM pi-xxx.indd 428/10/21 5:02 PM
Contents at a GlanceAbout the AuthorxxiiiIntroductionxxvCHAPTER 1Basic worksheet modelingCHAPTER 2Range namesCHAPTER 3Lookup functions25CHAPTER 4The INDEX function39CHAPTER 5The MATCH function43CHAPTER 6Text functions and Flash Fill51CHAPTER 7Dates and date functions73CHAPTER 8The net present value functions: NPV and XNPV85CHAPTER 9The internal rate of return: IRR, XIRR, and MIRR functions93CHAPTER 10More Excel financial functions101CHAPTER 11Circular references117CHAPTER 12IF, IFERROR, IFS, CHOOSE, SWITCH, and the IS functions123CHAPTER 13Time and time functions149CHAPTER 14The Paste Special command157CHAPTER 15Three-dimensional formulas and hyperlinks165CHAPTER 16The auditing tool and the Inquire add-in171CHAPTER 17Sensitivity analysis with data tables183CHAPTER 18The Goal Seek command197CHAPTER 19Using the Scenario Manager for sensitivity analysis203CHAPTER 20 he COUNTIF, COUNTIFS, COUNT, COUNTA,Tand COUNTBLANK functions209 he SUMIF, AVERAGEIF, SUMIFS, AVERAGEIFS,TMAXIFS, and MINIFS functions217CHAPTER 22The OFFSET function225CHAPTER 23The INDIRECT function239CHAPTER 24Conditional formatting251CHAPTER 25Excel tables and table slicers283CHAPTER 21111vA01 Winston FM pi-xxx.indd 527/10/21 2:20 PM
pin buttons, scrollbars, option buttons,Scheck boxes, combo boxes, and group list boxes301CHAPTER 27Importing data from a text file or document315CHAPTER 28The Power Query Editor321CHAPTER 29Excel’s new data types343CHAPTER 30Summarizing data with histograms and Pareto charts363CHAPTER 31Summarizing data with descriptive statistics377CHAPTER 32Sorting in Excel397CHAPTER 33Filtering data and removing duplicates411CHAPTER 34 ummarizing data with databaseSstatistical functions429CHAPTER 35Array formulas and functions441CHAPTER 36Excel’s new dynamic array functions461CHAPTER 37Validating data473CHAPTER 38Using PivotTables and slicers to describe data483CHAPTER 39The Data Model541CHAPTER 40Power Pivot551CHAPTER 41Consolidating data571CHAPTER 42Creating subtotals577CHAPTER 43Basic charting585CHAPTER 44Advanced charting617CHAPTER 45Filled and 3D Maps643CHAPTER 46Sparklines659CHAPTER 47Estimating straight-line relationships665CHAPTER 48Modeling exponential growth675CHAPTER 49The power curve681CHAPTER 50Using correlations to summarize relationships689CHAPTER 51Introduction to multiple regression697CHAPTER 52Incorporating qualitative factors into multiple regression705CHAPTER 53Modeling nonlinearities and interactions719CHAPTER 54Analysis of variance: One-way ANOVA727CHAPTER 55Randomized blocks and two-way ANOVA733CHAPTER 56Using moving averages to understand time series745CHAPTER 26viContents at a GlanceA01 Winston FM pi-xxx.indd 627/10/21 2:20 PM
CHAPTER 57Ratio-to-moving-average forecast method749CHAPTER 58An introduction to probability753CHAPTER 59An introduction to random variables763CHAPTER 60 he binomial, hypergeometric, and negativeTbinomial random variables769CHAPTER 61The Poisson and exponential random variable777CHAPTER 62The normal random variable and Z-scores781CHAPTER 63Making probability statements from forecasts791CHAPTER 64 sing the lognormal random variable to modelUstock prices795I mporting past stock prices, exchange rates,and cryptocurrency prices with theSTOCKHISTORY function799CHAPTER 66An introduction to optimization with Excel Solver807CHAPTER 67Using Solver to determine the optimal product mix813CHAPTER 68Using Solver to schedule your workforce825CHAPTER 69 sing Solver to solve transportation orUdistribution problems831CHAPTER 70Using Solver for capital budgeting837CHAPTER 71Using Solver for financial planning845CHAPTER 72Using Solver to rate sports teams853CHAPTER 73The Winters method and the Forecast Sheet tool859CHAPTER 74Forecasting in the presence of special events869CHAPTER 75 arehouse location and the GRG Multistart andWEvolutionary Solver engines877CHAPTER 76Penalties and the Evolutionary Solver889CHAPTER 77The traveling salesperson problem895CHAPTER 78 eibull and beta distributions: Modeling machineWlife and duration of a project901CHAPTER 79Introduction to Monte Carlo simulation907CHAPTER 80Calculating an optimal bid919CHAPTER 81Simulating stock prices and asset-allocation modeling925CHAPTER 82 un and games: Simulating gambling andFsporting-event probabilities937CHAPTER 65Contents at a GlanceA01 Winston FM pi-xxx.indd 7vii27/10/21 2:20 PM
CHAPTER 83Using resampling to analyze data947CHAPTER 84Pricing stock options953CHAPTER 85Determining customer value967CHAPTER 86The economic order quantity inventory model973CHAPTER 87Inventory modeling with uncertain demand979CHAPTER 88Queuing theory: The mathematics of waiting in line987CHAPTER 89Estimating a demand curve993CHAPTER 90Pricing products by using tie-ins999CHAPTER 91 ricing products by using subjectivelyPdetermined demandCHAPTER 92Nonlinear pricing1011CHAPTER 93Use Analyze Data to find patterns in your data1019CHAPTER 94Recording macros1031CHAPTER 95 he LET and LAMBDA functions and theTLAMBDA helper functions1049Advanced sensitivity analysis1063Index1067CHAPTER 96viii1005Contents at a GlanceA01 Winston FM pi-xxx.indd 827/10/21 2:20 PM
ContentsAbout the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiiiIntroduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvChapter 1Chapter 2Basic worksheet modeling1Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9Range names11How can I create named ranges? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11Using the Name box to create a range name . . . . . . . . . . . . . . . . . . . . . 12Creating named ranges using the Create from Selection option . 13Creating range names using the Define Name option . . . . . . . . . . . . 15The Name Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17Remarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23Chapter 3Lookup functions25Syntax of the lookup functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25VLOOKUP syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25HLOOKUP syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26XLOOKUP syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35Chapter 4The INDEX function39Syntax of the INDEX function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41ixA01 Winston FM pi-xxx.indd 927/10/21 2:20 PM
Chapter 5The MATCH function43Syntax of the MATCH function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49Chapter 6Text functions and Flash Fill51Text function syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69Chapter 7Dates and date functions73Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82Chapter 8The net present value functions: NPV and XNPV85Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90Chapter 9The internal rate of return: IRR, XIRR,and MIRR functions93Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98Chapter 10 More Excel financial functions101Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112Chapter 11 Circular references117Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120xContentsA01 Winston FM pi-xxx.indd 1027/10/21 2:20 PM
Chapter 12 IF, IFERROR, IFS, CHOOSE, SWITCH,and the IS functions123Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143Chapter 13 Time and time functions149Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155Chapter 14 The Paste Special command157Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163Chapter 15 Three-dimensional formulas and hyperlinks165Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169Chapter 16 The auditing tool and the Inquire add-in171Excel auditing options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172Show Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172Error Checking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173Watch Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174Trace Precedents, Trace Dependents, and Remove Arrows . . . . . . . 174Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182Chapter 17 Sensitivity analysis with data tables183Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192Chapter 18 The Goal Seek command197Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201ContentsA01 Winston FM pi-xxx.indd 11xi27/10/21 2:20 PM
Chapter 19 Using the Scenario Manager for sensitivity analysis203Answer to this chapter’s question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203Remarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207Chapter 20 The COUNTIF, COUNTIFS, COUNT, COUNTA,and COUNTBLANK functions209Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211Remarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214Chapter 21 The SUMIF, AVERAGEIF, SUMIFS, AVERAGEIFS,MAXIFS, and MINIFS functions217Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222Chapter 22 The OFFSET function225Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226Remarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236Chapter 23 The INDIRECT function239Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248Chapter 24 Conditional formatting251Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278Chapter 25 Excel tables and table slicers283Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298xiiContentsA01 Winston FM pi-xxx.indd 1227/10/21 2:20 PM
Chapter 26 Spin buttons, scrollbars, option buttons,check boxes, combo boxes, and group list boxes301Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313Chapter 27 Importing data from a text file or document315Answers to this chapter’s question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320Chapter 28 The Power Query Editor321Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340Chapter 29 Excel’s new data types343Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362Chapter 30 Summarizing data with histograms andPareto charts363Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376Chapter 31 Summarizing data with descriptive statistics377Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393Chapter 32 Sorting in Excel397Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409Chapter 33 Filtering data and removing duplicates411Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428ContentsA01 Winston FM pi-xxx.indd 13xiii27/10/21 2:20 PM
Chapter 34 Summarizing data with databasestatistical functions429Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438Chapter 35 Array formulas and functions441Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 442Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458Chapter 36 Excel’s new dynamic array functions461Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471Chapter 37 Validating data473Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473Remarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481Chapter 38 Using PivotTables and slicers to describe data483Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 536Chapter 39 The Data Model541Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 542Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550Chapter 40 Power Pivot551Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 570Chapter 41 Consolidating data571Answer to this chapter’s question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 575xivContentsA01 Winston FM pi-xxx.indd 1427/10/21 2:20 PM
Chapter 42 Creating subtotals577Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 583Chapter 43 Basic charting585Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 586Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614Chapter 44 Advanced charting617Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 618Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641Chapter 45 Filled and 3D Maps643Questions answered in this chapter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 657Chapter 46 Sparklines659Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 659Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 664Chapter 47 Estimating straight-line relationships665Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 667Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 672Chapter 48 Modeling exponential growth675Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 676Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 680Chapter 49 The power curve681Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 684Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 687ContentsA01 Winston FM pi-xxx.indd 15xv27/10/21 2:20 PM
Chapter 50 Using correlations to summarize relationships689Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 691Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 695Chapter 51 Introduction to multiple regression697Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 697Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 703Chapter 52 Incorporating qualitative factorsinto multiple regression705Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 705Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 718Chapter 53 Modeling nonlinearities and interactions719Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 719Problems for Chapters 51–53 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 723Chapter 54 Analysis of variance: One-way ANOVA727Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 728Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 731Chapter 55 Randomized blocks and two-way ANOVA733Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 734Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 742Chapter 56 Using moving averages to understand time series745Answer to this chapter’s question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 745Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 747Chapter 57 Ratio-to-moving-average forecast method749Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 749Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 752xviContentsA01 Winston FM pi-xxx.indd 1627/10/21 2:20 PM
Chapter 58 An introduction to probability753Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 753Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 759Chapter 59 An introduction to random variables763Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 763Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 767Chapter 60 The binomial, hypergeometric,and negative binomial random variables769Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 770Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 775Chapter 61 The Poisson an
vi Contents at a Glance CHAPTER 26 Spin buttons, scrollbars, option buttons, check boxes, combo boxes, and group list boxes 301 CHAPTER 27 Importing data from a text file or document 315 CHAPTER 28 The Power Query Editor 321 CHAPTER 29 Excel's new data types 343 CHAPTER 30 Summarizing data with histograms and Pareto charts 363 CHAPTER 31 Summarizing data with descriptive statistics 377
