Microsoft Excel 2010:Data Analysis and Business Modeling

PUBLISHED BY

M crosoft Press

A D v s on of M crosoft Corporat on

One M crosoft Way

Redmond, Wash ngton 98052-6399

Copyr ght © 2011 by Wayne L W nston

A r ghts reserved No part of the contents of th s book may be reproduced or transm tted n any form or by any

means w thout the wr tten perm ss on of the pub sher

L brary of Congress Contro Number 2010934987

M crosoft and the trademarks sted at http //www m crosoft com/about/ ega /en/us/Inte ectua Property/

Trademarks/EN-US aspx are trademarks of the M crosoft group of compan es A other marks are property of

the r respect ve owners

The examp e compan es, organ zat ons, products, doma n names, e-ma addresses, ogos, peop e, p aces, and

events dep cted here n are ct t ous No assoc at on w th any rea company, organ zat on, product, doma n name,

e-ma address, ogo, person, p ace, or event s ntended or shou d be nferred

Th s book expresses the author’s v ews and op n ons The nformat on conta ned n th s book s prov ded w thout

any express, statutory, or mp ed warrant es Ne ther the authors, M crosoft Corporat on, nor ts rese ers, or

d str butors w be he d ab e for any damages caused or a eged to be caused e ther d rect y or nd rect y by

th s book

Cover: Tw st

Body Part No X17-37446

iv Table of Contents

15 Three-Dimensional Formulas                             117

16 The Auditing Tool                                       121

17 Sensitivity Analysis with Data Tables                       127

18 The Goal Seek Command                                137

19 Using the Scenario Manager for Sensitivity Analysis         143

20 The COUNTIF, COUNTIFS, COUNT, COUNTA, and

COUNTBLANK Functions                                 149

25 Sorting in Excel                                         209

26 Tables                                                 217

27 Spin Buttons, Scroll Bars, Option Buttons, Check Boxes,

Combo Boxes, and Group List Boxes                       229

28 An Introduction to Optimization with Excel Solver          241

29 Using Solver to Determine the Optimal Product Mix         245

30 Using Solver to Schedule Your Workforce                  255

31 Using Solver to Solve Transportation or Distribution

Problems

                                             261