Microsoft Excel 2013: Data Analysis and Business Modeling Wayne L. Winston Published with the authorization of Microsoft Corporation by: O’Reilly Media, Inc. 1005 Gravenstein Highway North Sebastopol, California 95472 Copyright © 2014 by Wayne L .Winston All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher. ISBN: 978-0-7356-6913-0 2 3 4 5 6 7 8 9 10 LSI 9 8 7 6 5 4 Printed and bound in the United States of America. Microsoft Press books are available through booksellers and distributors worldwide. If you need support related to this book, email Microsoft Press Book Support at [email protected]. Please tell us what you think of this book at http://www.microsoft.com/learning/booksurvey. Microsoft and the trademarks listed at http://www.microsoft.com/about/legal/en/us/IntellectualProperty/ Trademarks/EN-US.aspx are trademarks of the Microsoft group of companies. All other marks are property of their respective owners. The example companies, organizations, products, domain names, email addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, email address, logo, person, place, or event is intended or should be inferred. This book expresses the author’s views and opinions. The information contained in this book is provided without any express, statutory, or implied warranties. Neither the author, O’Reilly Media, Inc., Microsoft Corporation, nor its resellers, or distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by this book. Acquisitions and Developmental Editor: Kenyon Brown Production Editor: Kara Ebrahim Editorial Production: nSight, Inc. Technical Reviewer: Peter Myers Copyeditor: nSight, Inc. Indexer: nSight, Inc. Cover Design: Twist Creative • Seattle Cover Composition: Ellie Volckhausen Illustrator: Rebecca Demarest [2014-01-31] Contents at a glance Introduction xxi ChAptEr 1 range names 1 ChAptEr 2 Lookup functions 15 ChAptEr 3 INDEX function 23 ChAptEr 4 MAtCh function 27 ChAptEr 5 text functions 35 ChAptEr 6 Dates and date functions 51 ChAptEr 7 Evaluating investments by using net present value criteria 59 ChAptEr 8 Internal rate of return 67 ChAptEr 9 More Excel financial functions 75 ChAptEr 10 Circular references 87 ChAptEr 11 IF statements 93 ChAptEr 12 time and time functions 111 ChAptEr 13 the paste Special command 117 ChAptEr 14 three-dimensional formulas 123 ChAptEr 15 the Auditing tool and Inquire add-in 127 ChAptEr 16 Sensitivity analysis with data tables 139 ChAptEr 17 the Goal Seek command 149 ChAptEr 18 Using the Scenario Manager for sensitivity analysis 155 ChAptEr 19 the COUNtIF, COUNtIFS, COUNt, COUNtA, and COUNtBLANK functions 161 ChAptEr 20 the SUMIF, AVErAGEIF, SUMIFS, and AVErAGEIFS functions 169 ChAptEr 21 the OFFSEt function 175 ChAptEr 22 the INDIrECt function 187 ChAptEr 23 Conditional formatting 195 ChAptEr 24 Sorting in Excel 223 ChAptEr 25 tables 231 ChAptEr 26 Spinner buttons, scroll bars, option buttons, check boxes, combo boxes, and group list boxes 245 ChAptEr 27 the analytics revolution 261 ChAptEr 28 Introducing optimization with Excel Solver 267 ChAptEr 29 Using Solver to determine the optimal product mix 273 ChAptEr 30 Using Solver to schedule your workforce 285 ChAptEr 31 Using Solver to solve transportation or distribution problems 291 ChAptEr 32 Using Solver for capital budgeting 297 ChAptEr 33 Using Solver for financial planning 305 ChAptEr 34 Using Solver to rate sports teams 313 ChAptEr 35 Warehouse location and the GrG Multistart and Evolutionary Solver engines 319 ChAptEr 36 penalties and the Evolutionary Solver 329 ChAptEr 37 the traveling salesperson problem 335 ChAptEr 38 Importing data from a text file or document 339 ChAptEr 39 Importing data from the Internet 345 ChAptEr 40 Validating data 349 ChAptEr 41 Summarizing data by using histograms 359 ChAptEr 42 Summarizing data by using descriptive statistics 369 ChAptEr 43 Using pivottables and slicers to describe data 385 ChAptEr 44 the Data Model 441 ChAptEr 45 powerpivot 455 ChAptEr 46 power View 469 ChAptEr 47 Sparklines 485 ChAptEr 48 Summarizing data with database statistical functions 491 ChAptEr 49 Filtering data and removing duplicates 501 ChAptEr 50 Consolidating data 521 ChAptEr 51 Creating subtotals 527 ChAptEr 52 Charting tricks 533 ChAptEr 53 Estimating straight-line relationships 569 ChAptEr 54 Modeling exponential growth 577 ChAptEr 55 the power curve 581 ChAptEr 56 Using correlations to summarize relationships 589 ChAptEr 57 Introduction to multiple regression 597 ChAptEr 58 Incorporating qualitative factors into multiple regression 605 ChAptEr 59 Modeling nonlinearities and interactions 615 ChAptEr 60 Analysis of variance: one-way ANOVA 623 ChAptEr 61 randomized blocks and two-way ANOVA 629 ChAptEr 62 Using moving averages to understand time series 641 ChAptEr 63 Winters’s method 645 ChAptEr 64 ratio-to-moving-average forecast method 651 ChAptEr 65 Forecasting in the presence of special events 655 ChAptEr 66 An introduction to random variables 663 iv Contents at a glance ChAptEr 67 the binomial, hypergeometric, and negative binomial random variables 669 ChAptEr 68 the poisson and exponential random variable 679 ChAptEr 69 the normal random variable 683 ChAptEr 70 Weibull and beta distributions: modeling machine life and duration of a project 691 ChAptEr 71 Making probability statements from forecasts 697 ChAptEr 72 Using the lognormal random variable to model stock prices 701 ChAptEr 73 Introduction to Monte Carlo simulation 705 ChAptEr 74 Calculating an optimal bid 715 ChAptEr 75 Simulating stock prices and asset allocation modeling 721 ChAptEr 76 Fun and games: simulating gambling and sporting event probabilities 731 ChAptEr 77 Using resampling to analyze data 739 ChAptEr 78 pricing stock options 743 ChAptEr 79 Determining customer value 757 ChAptEr 80 the economic order quantity inventory model 763 ChAptEr 81 Inventory modeling with uncertain demand 769 ChAptEr 82 Queuing theory: the mathematics of waiting in line 777 ChAptEr 83 Estimating a demand curve 785 ChAptEr 84 pricing products by using tie-ins 791 ChAptEr 85 pricing products by using subjectively determined demand 797 ChAptEr 86 Nonlinear pricing 803 ChAptEr 87 Array formulas and functions 813 Index 831 Contents at a glance v Contents Introduction ....................................................xxi Errata ................................................... xxvi We want to hear from you ................................. xxvi Stay in touch ............................................. xxvi Chapter 1 Range names 1 How can I create named ranges? ....................................1 Using the Name box to create a range name ....................2 Creating named ranges by using Create From Selection . . . . . . . . . .4 Creating range names by using Define Name ...................5 Name Manager ..............................................6 Answers to this chapter’s questions ..................................7 Remarks ........................................................13 Problems ........................................................13 Chapter 2 Lookup functions 15 Syntax of the lookup functions .....................................15 VLOOKUP syntax ...........................................15 HLOOKUP syntax ...........................................16 Answers to this chapter’s questions .................................16 Problems ........................................................20 Chapter 3 INDEX function 23 Syntax of the INDEX function ......................................23 Answers to this chapter’s questions .................................23 Problems ........................................................25 What do you think of this book? We want to hear from you! Microsoft is interested in hearing your feedback so we can continually improve our books and learning resources for you. to participate in a brief online survey, please visit: microsoft.com/learning/booksurvey vii Chapter 4 MATCH function 27 Answers to this chapter’s questions .................................29 Problems ........................................................32 Chapter 5 Text functions 35 Text function syntax ..............................................36 The LEFT function ...........................................37 The RIGHT function .........................................37 The MID function ...........................................37 The TRIM function ..........................................37 The LEN function ...........................................37 The FIND and SEARCH functions .............................37 The REPT function ..........................................37 The CONCATENATE and & functions ..........................38 The REPLACE function .......................................38 The VALUE function ........................................38 The UPPER, LOWER, and PROPER functions ....................38 The CHAR function .........................................38 The CLEAN Function ........................................39 The SUBSTITUTE FUNCTION .................................39 Answers to this chapter’s questions .................................40 Extracting data by using the Convert Text To Columns Wizard ...43 Problems ........................................................47 Chapter 6 Dates and date functions 51 Answers to this chapter’s questions .................................52 Problems ........................................................57 Chapter 7 Evaluating investments by using net present value criteria 59 Answers to this chapter’s questions .................................60 Problems ........................................................64 viii Contents