Thư viện tri thức trực tuyến
Kho tài liệu với 50,000+ tài liệu học thuật
© 2023 Siêu thị PDF - Kho tài liệu học thuật hàng đầu Việt Nam
Microsoft Excel data analysis and business modeling
Nội dung xem thử
Mô tả chi tiết
< Day Day Up >
Microsoft Excel Data Analysis and Business Modeling
by Wayne L. Winston ISBN:0735619018
Microsoft Press © 2004 (616 pages)
Use this resource to apply the techniques that business
analysts at leading companies use to analyze and transform
data into bottom line results, and use Microsoft Excel for data
analysis, modeling, and decision making.
Table of Contents
Microsoft Excel Data Analysis and Business Modeling
Introduction
Chapter 1 - Range Names
Chapter 2 - Natural Language Range Names
Chapter 3 - Lookup Functions
Chapter 4 - The INDEX Function
Chapter 5 - The MATCH Function
Chapter 6 - Text Functions
Chapter 7 - Dates and Date Functions
Chapter 8 - Evaluating Investments with Net Present Value Criteria
Chapter 9 - Internal Rate of Return
Chapter 10 -
Functions for Personal Financial Decisions: The PV, FV, PMT, PPMT,
and IPMT Functions
Chapter 11 - Circular References
Chapter 12 - IF Statements
Chapter 13 - The Paste Special Command
Chapter 14 - The Auditing Tool
Chapter 15 - Sensitivity Analysis with Data Tables
Chapter 16 - The Goal Seek Command
Chapter 17 - Using the Scenario Manager for Sensitivity Analysis
Chapter 18 - Creating and Using Spinners for Sensitivity Analysis
Chapter 19 - The COUNTIF, COUNT, COUNTA, and COUNTBLANK Functions
Chapter 20 - The SUMIF Function
Chapter 21 - The OFFSET Function
Chapter 22 - The INDIRECT Function
Chapter 23 - Conditional Formatting
Chapter 24 - An Introduction to Optimization with the Excel Solver
Chapter 25 - Using Solver to Determine the Optimal Product Mix
Chapter 26 - Using Solver to Solve Transportation or Distribution Problems
Chapter 27 - Using Solver to Schedule Your Workforce
Chapter 28 - Using Solver for Capital Budgeting
Chapter 29 - Using Solver for Financial Planning
Chapter 30 - Using Solver to Rate Sports Teams
Chapter 31 - Importing Text or Microsoft Word Data into Excel
Chapter 32 - Importing Data from the Web into Excel
Chapter 33 - Validating Data
< Day Day Up >
Microsoft Excel Data Analysis and Business Modeling
by Wayne L. Winston ISBN:0735619018
Microsoft Press © 2004 (616 pages)
Use this resource to apply the techniques that business
analysts at leading companies use to analyze and transform
data into bottom line results, and use Microsoft Excel for data
analysis, modeling, and decision making.
Table of Contents
Microsoft Excel Data Analysis and Business Modeling
Introduction
Chapter 1 - Range Names
Chapter 2 - Natural Language Range Names
Chapter 3 - Lookup Functions
Chapter 4 - The INDEX Function
Chapter 5 - The MATCH Function
Chapter 6 - Text Functions
Chapter 7 - Dates and Date Functions
Chapter 8 - Evaluating Investments with Net Present Value Criteria
Chapter 9 - Internal Rate of Return
Chapter 10 -
Functions for Personal Financial Decisions: The PV, FV, PMT, PPMT,
and IPMT Functions
Chapter 11 - Circular References
Chapter 12 - IF Statements
Chapter 13 - The Paste Special Command
Chapter 14 - The Auditing Tool
Chapter 15 - Sensitivity Analysis with Data Tables
Chapter 16 - The Goal Seek Command
Chapter 17 - Using the Scenario Manager for Sensitivity Analysis
Chapter 18 - Creating and Using Spinners for Sensitivity Analysis
Chapter 19 - The COUNTIF, COUNT, COUNTA, and COUNTBLANK Functions
Chapter 20 - The SUMIF Function
Chapter 21 - The OFFSET Function
Chapter 22 - The INDIRECT Function
Chapter 23 - Conditional Formatting
Chapter 24 - An Introduction to Optimization with the Excel Solver
Chapter 25 - Using Solver to Determine the Optimal Product Mix
Chapter 26 - Using Solver to Solve Transportation or Distribution Problems
Chapter 27 - Using Solver to Schedule Your Workforce
Chapter 28 - Using Solver for Capital Budgeting
Chapter 29 - Using Solver for Financial Planning
Chapter 30 - Using Solver to Rate Sports Teams
Chapter 31 - Importing Text or Microsoft Word Data into Excel
Chapter 32 - Importing Data from the Web into Excel
Chapter 33 - Validating Data
Chapter 34 - Summarizing Data with Histograms
Chapter 35 - Summarizing Data with Descriptive Statistics
Chapter 36 - Using PivotTables to Describe Data
Chapter 37 - Summarizing Data with Database Statistical Functions
Chapter 38 - Filtering Data
Chapter 39 - Consolidating Data
Chapter 40 - Creating Subtotals
Chapter 41 - Estimating Straight Line Relationships
Chapter 42 - Modeling Exponential Growth
Chapter 43 - The Power Curve
Chapter 44 - Using Correlations to Summarize Relationships
Chapter 45 - Introduction to Multiple Regression
Chapter 46 - Incorporating Qualitative Factors into Multiple Regression
Chapter 47 - Modeling Nonlinearities and Interactions
Chapter 48 - Analysis of Variance: One-Way ANOVA
Chapter 49 - Randomized Blocks and Two-Way ANOVA
Chapter 50 - Using Moving Averages to Understand Time Series
Chapter 51 - Forecasting with Moving Averages
Chapter 52 - Forecasting in the Presence of Special Events
Chapter 53 - An Introduction to Random Variables
Chapter 54 - The Binomial and Hypergeometric Random Variables
Chapter 55 - The Poisson and Exponential Random Variable
Chapter 56 - The Normal Random Variable
Chapter 57 - Weibull and Beta Distributions: Modeling Machine Life and Duration
of a Project
Chapter 58 - Introduction to Monte Carlo Simulation
Chapter 59 - Calculating an Optimal Bid
Chapter 60 - Simulating Stock Prices and Asset Allocation Modeling
Chapter 61 -
Fun and Games: Simulating Gambling and Sporting Event
Probabilities
Chapter 62 - Using Resampling to Analyze Data
Chapter 63 - Pricing Stock Options
Chapter 64 - Determining Customer Value
Chapter 65 - The Economic Order Quantity Inventory Model
Chapter 66 -
Determining the Reorder Point: How Low Should I Let My Inventory
Level Go Before I Reorder?
Chapter 67 - Queuing Theory: The Mathematics of Waiting in Line
Chapter 68 - Estimating a Demand Curve
Chapter 69 - Pricing Products with Tie-Ins
Chapter 70 - Pricing Products Using Subjectively Determined Demand
Chapter 71 - Nonlinear Pricing
Chapter 72 - Array Formulas and Functions
Chapter 73 - Picking Your Fantasy Football Team
Index
List of Figures
List of Tables
List of Sidebars
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
< Day Day Up >
< Day Day Up >
Back Cover
Now you can apply the techniques that business analysts at leading companies use to analyze and transform data into
bottom line results. For more than 10 years, well-known consultant and business professor Wayne Winston has been
teaching corporate clients and MBA candidates the most effective ways to use Microsoft Excel for data analysis,
modeling, and decision-making. This practical, business-focused guide delivers the best of Winston’s classroom
experience to you in 70+ concise chapters, organized by real-world scenarios. Quickly find and apply exactly the
information you need to solve a specific business problem—from asset allocation modeling to estimating exponential
growth, forecasting sales, optimizing portfolios, and other critical functions.
About the Author
Wayne L. Winston teaches decision sciences at Indiana University’s Kelley School of Business. For more than 10 years
he has taught business analysts how to use Excel to make better decisions. His clients include Bristol-Myers Squibb
Company; Cisco Systems, Inc.; Eli Lilly and Company; Ford Motor Company; General Motors Corporation; Intel
Corporation; Microsoft Corporation; NCR Corporation; Owens Corning; Pfizer, Inc.; Proctor & Gamble; the U.S. Army;
and the U.S. Department of Defense. Wayne and business partner Jeff Sagarin developed a player rating system that
the Dallas Mavericks use to identify statistical trends and evaluate players.
< Day Day Up >
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
< Day Day Up >
Microsoft Excel Data Analysis and Business Modeling
Wayne L. Winston
PUBLISHED BY Microsoft Press A Division of Microsoft Corporation One Microsoft Way Redmond,
Washington 98052-6399
Copyright © 2004 by Wayne 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.
Library of Congress Cataloging-in-Publication Data Winston, Wayne L.
Microsoft Excel Data Analysis and Business Modeling/ Wayne Winston. p. cm. Includes index. ISBN 0-7356-
1901-8 1. Industrial management--Statistical methods--Computer programs. 2. Decision making--Computer
programs. 3. Microsoft Excel (Computer file) I. Title.
HD30.215.W56 2004 005.54--dc22 2003064860
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 QWT 8 7 6 5 4 3
Distributed in Canada by H.B. Fenn and Company Ltd.
A CIP catalogue record for this book is available from the British Library.
Microsoft Press books are available through booksellers and distributors worldwide. For further information
about international editions, contact your local Microsoft Corporation office or contact Microsoft Press
International directly at fax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress. Send
comments to mspinput@microsoft.com.
Microsoft, Microsoft Press, MSN, PivotChart, PivotTable, Windows, the Windows logo, and Xbox are either
registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
Other product and company names mentioned herein may be the trademarks of their respective owners.
The example companies, organizations, products, domain names, e-mail addresses, logos, people, places,
and events depicted herein are fictitious. No association with any real company, organization, product,
domain name, e-mail 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 authors, 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 Editor: Alex Blanton
Project Editors: Jean Trenary and Kristine Haugseth
Technical Editor: John Pierce
Indexer: Liz Cunningham
Body Part No. X10-21791
About the Author
Wayne L. Winston is a professor of decision sciences at the Indiana University Kelley School of Business. He
has earned MBA teaching awards for 18 consecutive years and regularly teaches business analysts how to
use Microsoft Excel to make better decisions. Wayne also consults for several Fortune 500 clients, including
Bristol-Myers Squibb Company; Cisco Systems, Inc.; Eli Lilly and Company; Ford Motor Company; General
Motors Corporation; Intel Corporation; Microsoft Corporation; NCR Corporation; Owens Corning; Pfizer, Inc.;
Proctor & Gamble; the U.S. Army; and the U.S. Department of Defense. He and business partner Jeff
Sagarin developed the statistics tracking and rating system used by the Dallas Mavericks professional
basketball team. Wayne is also a two time Jeopardy! Champion.
Acknowledgments
I'm eternally grateful to Jennifer Skoog and Norm Tonina, who had faith in me and first hired me to teach
Excel classes for Microsoft finance. Jennifer in particular was instrumental in helping me design the content
and style of the classes on which this book is based. Keith Lange of Eli Lilly and Company, Pat Keating and
Doug Hoppe of Cisco Systems, Inc., and Dennis Fuller of the U.S. Army also helped me refine my thoughts
on teaching data analysis and modeling with Excel.
I am grateful to my many students at the organizations where I've taught and at the Kelley School of
Business. Many of them have taught me things I did not know about Excel.
Alex Blanton of Microsoft Press championed the project and shared my vision of developing a user-friendly
text designed for use by working business analysts. An author could not ask for a better editor than John
Pierce. He unfailingly corrected my grammatical shortcomings and did not hesitate to make necessary
changes. Microsoft Press did a great job with the production process and made writing a book as painless as
possible.
Finally, my wonderful family put up with my long absences during the year in which the book was written. My
lovely and talented wife, Vivian, is always patient and inspiring. My daughter, Jennifer, and son, Gregory,
inspired many of the book's examples. Even our dog, Honey, helped by barking less when she saw I was
working!
< Day Day Up >
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
< Day Day Up >
Introduction
Whether you work for a Fortune 500 corporation, a small company, a government agency, or a not-for-profit
organization, if you're reading this introduction, the chances are that you use Microsoft Excel in your daily
work. Your job probably involves summarizing, reporting, and analyzing data, and it might also involve
building analytical models to help your employer increase profits, reduce costs, or just manage operations
more efficiently.
Over the past decade, I've taught thousands of analysts at organizations such as Bristol-Myers Squibb
Company; Cisco Systems, Inc.; Eli Lilly and Company; Ford Motor Company; General Motors Corporation;
Intel Corporation; Microsoft Corporation; NCR Corporation; Owens Corning; Pfizer, Inc.; Proctor & Gamble;
the U.S. Army; and the U.S. Department of Defense how to use Excel more efficiently and productively in
their jobs. At each of these organizations, my classes have received uniformly positive evaluations. Students
have often told me that using the tools and methods I teach in my classes has saved them hours of time each
week and has provided them with new and improved approaches for analyzing important business problems.
I've used the techniques described in this book in my own consulting practice to solve many business
problems. We even use Excel to help the Dallas Mavericks basketball team and its owner Mark Cuban
evaluate NBA referees, players, and lineups! I have also taught an Excel business modeling and data
analysis course to MBA students for many years at the Indiana University Kelley School of Business. (As proof
of my teaching excellence, I have received an MBA teaching award for 18 consecutive years.)
The book you have in your hands is an attempt to try and make these successful classes available to
everyone. Here is why I think this book will help you learn how to get more from Excel:
The materials have been tested and used successfully in the classes
I've taught for the U.S. Army and a number of Fortune 500 companies.
I've written the book as though I'm talking to the reader. This approach hopefully transfers the spirit of a
successful classroom environment to the written page.
I teach by example, which makes concepts easier to master. These examples are constructed to have a 'realworld' feel. Many of the examples are based on questions sent to me by former students working at Fortune
500 corporations.
For the most part, I lead you through the approaches I take to set up and answer a wide range of data
analysis and business questions in Excel. You can follow along with my explanations by referring to the
sample worksheets that accompany each example. However, I've also included template files for the
examples presented in this book on the book's companion CD. If you want, these templates enable you
to work directly with Excel and complete each example on your own.
For the most part, the chapters are short and organized around a single concept. You should be able to
master the content of most chapters with at most two hours of study. By looking at the questions that
begin each chapter, you'll gain an idea about the types of problems you'll be able to solve after mastering
a chapter's topics.
You won't just learn about Excel formulas in this book. You will learn some important math in a fairly
painless fashion. For example, you'll learn something about statistics, forecasting, optimization models,
Monte Carlo simulation, inventory modeling, and the mathematics of waiting in line. You will also learn
about some new developments in business thinking (real options, computing customer value,
mathematical pricing models, and so on).
At the end of each chapter, I've provided a group of problems (a total of nearly 400 in all) that you can
work through on your own. These problems will help you determine whether you've mastered the
concepts in each chapter. Answers to all the problems are included on the CD that accompanies this
book.
Most of all, learning should be fun. If you read this book, you will learn how to predict U.S. presidential
elections, how to set football point spreads, how to determine the probability of winning at craps, and how
to determine the probability of each team winning the NBA finals. These examples are interesting and
fun, and they also teach you a lot of useful things about Excel.
You can follow almost all of the examples in this book whether you are working with Microsoft Office Excel
2003 (the newest version of Excel), Microsoft Excel 2002 or Microsoft Excel 2000.
What You Should Know Before Reading This Book
To follow the examples in this book, you don't need to be an expert in Excel. Basically, the two key actions
you should know how to perform are the following:
How to enter a formula You should know that formulas must begin with an equal sign (=). You should
also know the basic mathematical operators. For example, you should know that an asterisk (*) is used
for multiplication and a forward slash (/) is used for division.
How to work with cell references You should know that when you copy a formula that contains a cell
reference such as $A$4 (an absolute cell reference, which is created by the dollar signs), the formula will
still refer to cell $A$4 in the cells you copy it to. When you copy a formula that contains a cell reference
such as $A4 (a mixed cell address), the column (A) will remain fixed, but the row number (4) will change.
When you copy a formula that contains a cell reference such as A$4 (a mixed cell address), the row will
remain fixed, but the column will change. Finally, when you copy a formula that contains a cell reference
such as A4 (a relative cell reference), both the row and the column of the cells referenced in the formula
will change.
Essentially, that's all you need to know about Excel to read and benefit from this book. Starting from this
narrow base of knowledge, I'll show you the amazing things you can do with Excel.
< Day Day Up >
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
< Day Day Up >
How to Use This Book
As you read along with the examples in this book, you can take one of two approaches. You can open the
template that corresponds to the example you are studying and complete each step of the example as you
read the book. You will be surprised how easy this process is and how much you'll learn and retain. You can
also follow my explanations as you look at the final version of each sample file instead of filling in the
template.
Using the Companion CD
The CD-ROM that accompanies this book contains the sample files you use in the book's examples (both the
final Excel workbooks and the template files you can work with on your own). The workbooks and templates
are organized in folders that are named for each chapter. The answers to all chapter-ending problems in the
book are also included on the book's CD. Each answer file is named so that you can identify it easily. For
example, the answer to problem 2 in Chapter 10 is in the file named S10_2.xls.
To use the CD, insert it into your CD-ROM drive. If AutoRun is not enabled on your computer, double-click the
file StartCD.exe in the root folder of the CD. (You'll be presented with a licensing agreement that you need to
accept before you can install the files that come on the CD.) The sample files will be copied to the folder
C:\Microsoft Press\Excel Data Analysis by default.
The CD also contains a version of Microsoft Excel Data Analysis and Business-Modeling in PDF format.
Adobe Reader is required to view the PDF version of the book. The CD includes a link to Adobe's Web site,
where you can download a copy of Adobe Reader if you don't already have a copy installed on your
computer. (You can download Adobe Reader free of charge.)
System Requirements
To work with this book's sample files, your computer must meet the following minimum system requirements:
A minimum of 40 MB of available hard disk space is required to install the sample files.
A copy of Microsoft Excel 2000, Microsoft Excel 2002, or Microsoft
Office Excel 2003 needs to be installed.
< Day Day Up >
< Day Day Up >
Support Information
Every effort has been made to ensure the accuracy of this book and the contents of the companion CD. To
provide feedback on the book's content or the companion CD, you can send e-mail to
mspinput@microsoft.com, or write to us at the following address:
Microsoft Excel Data Analysis and Business Modeling Editor
Microsoft Press/Microsoft Learning
One Microsoft Way
Redmond, WA 98052
Microsoft Press provides corrections for books through the World Wide Web at
http://www.microsoft.com/learning/support/. To connect directly to the Microsoft Press Knowledge Base and
enter a query regarding a question or issue that you might have, go to http://www.microsoft.com/learning/
support/search.asp. For support information regarding Microsoft Excel, you can connect to Microsoft
Technical Support on the Web at http://support.microsoft.com/.
< Day Day Up >
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
< Day Day Up >
Chapter 1: Range Names
Overview
I want to add up sales in Arizona, California, Montana, New York, and New Jersey. Can I use a formula to
compute total sales in a form such as AZ+CA+MT+NY+NJ instead of SUM(A21:A25) and still get the
right answer?
To compute total sales for a year, I often average the 12 monthly sales below the current cell. Can I
name my formula annualaverage so that when I enter annualaverage in a cell the appropriate average is
computed?
How can I easily select a cell range?
How can I paste a list of all range names (and the cells they represent) into my spreadsheet?
You have probably received spreadsheets that use formulas such as SUM(A5000:A5049). Then you have to
struggle to understand what's contained in cells A5000:A5049. If cells A5000:A5049 contain sales in each
U.S. state, wouldn't the spreadsheet be easier to understand if the formula was SUM(USSales)? In this
chapter, I'll teach you how to name individual cells, ranges of cells, constants, and formulas. I'll also show you
how to use range names in formulas.
< Day Day Up >
< Day Day Up >
How Can I Create Range Names?
There are three ways to create range names:
Entering a range name in the Name box
Choosing the Name, Create command from the Insert menu
Choosing the Name, Define command from the Insert menu
Using the Name Box to Create a Range Name
The Name box is located directly above the label for column A, as you can see in Figure 1-1. (To see the
Name box, you need to display the Formula bar.) To create a range name using the Name box, simply select
with the mouse the cell or range of cells that you want to name, click in the Name box, and then type the
range name you want to use. Press Enter, and you've created the range name. Clicking on the drop-down
arrow for the Name box displays the range names defined in the current workbook. You can also display all
the range names in a workbook by pressing the F3 button, which displays the Paste Name dialog box. When
you select a range name from the Name box, Excel selects the cells corresponding to that range name. This
enables you to check that you've chosen the cell or range that you intended to.
Figure 1-1: You can create a range name by selecting the cell range you want to name and then typing
the range name in the Name box.
Creating Range Names by Using the Name Create Command
The spreadsheet States.xls contains sales during March for each of the 50 U.S. states. Figure 1-2 shows a
subset of this data. We would like to name each cell in the range B6:B55 with the correct state abbreviation.
To do this, select the range A6:B55, choose Insert, Name Create, and then choose the Create Names In Left
Column option, as indicated in Figure 1-3.
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
Figure 1-2: By naming the cells that contain state sales with state abbreviations, you can use the
abbreviation when you refer to the cell rather than the cell's column and row number.
Figure 1-3: Use the Name, Create command on the Insert menu to name cell ranges. The Create
Names dialog box provides options for naming cell ranges.
Excel now knows to associate the names in the left column of the selected range with the cells in the second
column of the selected range. Thus B6 is assigned the range name AL, B7 is named AK, and so on. Note
that creating these range names in the Name box would have been incredibly tedious! Click on the dropdown arrow in the Name box if you don't believe that these range names have been created.
Creating Range Names by Using the Name Define Command
If you choose the Insert, Name Define command, the dialog box shown in Figure 1-4 comes up.
Figure 1-4: The Define Name dialog box before creating any range names.
Suppose you want to assign the name range1 (range names are not case sensitive) to the cell range A2:B7.
Simply type range1 in the Names In Workbook box and then go down to the Refers To area and point to the
range or type in =A2:B7. Click Add, and you're done. The Define Name dialog box will now look like Figure 1-
5.
Figure 1-5: Define Name dialog box after creating a range name.
Of course, if you now click in the Name box, the name range1 will appear. Now let's look at some specific
examples of how to use range names.
I want to add up sales in Arizona, California, Montana, New York, and New Jersey. Can I use a formula
to compute total sales in a form such as AZ+ CA+ MT+ NY+ NJ instead of SUM(A21:A25) and still get
the right answer?
Let's return to the file States.xls in which we assigned each state's abbreviation as the range name for the
state's sales. If we want to compute total sales in Alabama, Alaska, Arizona, and Arkansas, we could clearly
use the formula SUM(B6:B9). We could also point to cells B6, B7, B8, and B9, and the formula would be
entered as =AL+AK+AZ+AR. The latter formula is, of course, much easier to understand.
As another illustration of how to use range names, look at the file HistoricalInvest.xls, shown in Figure 1-6,
which contains annual percentage returns on stocks, T-Bills, and bonds. (The rows for years 1935-1996 are
hidden.)
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
UNREGISTERED VERSION OF CHM TO PDF CONVERTER By THETA-SOFTWARE
Figure 1-6: Historical investment data.
After selecting the cell range B7:D81 and choosing Insert, Name Create, we choose to create names in the
top row of the range. The range B8:B81 is named Stocks, the range C8:C81 T.Bills, and the range D8:D81
T.Bonds. Now we no longer need to remember where our data is. For example, in cell B84, after typing
=AVERAGE(, we can press F3 and the Paste Name dialog box appears, as shown in Figure 1-7.
Figure 1-7: You can add a range name to a formula by using the Paste Name dialog box.
Then we can select Stocks in the Paste Name list and click OK. After enteringthe closing parentheses, our
formula, =AVERAGE(Stocks), computes the average return on stocks (12.05 percent). The beauty of this
approach is that even if we don't remember where the data is, we can work with the stock return data
anywhere in the workbook!
If we use a column name (in the form A:A, C:C, and so on) in a formula, Excel treats an entire column as a
range name. For example, entering the formula =AVERAGE(A:A) will average all numbers in column A.
Using a range name for an entire column is very helpful if you frequently enter new data into a column. For
example, if column A contains monthly sales of a product, as new sales data is entered each month, our
formula always computes an uptodate monthly sales average. I caution you, however, that if you enter the
formula =AVERAGE(A:A) in column A, you will get a circular reference message because the value of the
cell containing the average formula depends on the cell containing the average. You will learn how to resolve
circular references later in the book, in Chapter 11.
< Day Day Up >