Excel Manual

(1)

(2)

A Division of Microsoft Corporation One Microsoft Way

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. 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.

Decision making--Computer

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 [email protected].

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

(3)

1

2

3

4

5

6

7

8

9

10

Whether you work for a Fortune 500 corporation, a small company, a govern ment 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 prob ably 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 Com pany; 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 suc cessful 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 “real-world” feel. Many of the

(8)

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 sin gle concept. You should be able to master the content of most chap ters 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 math ematics of waiting in line. You will also learn about some new devel opments 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.

(9)

Introduction ix

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. Basi cally, 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 mathe matical 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 for mula 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 refer ence 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.

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.

(10)

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 Busi

ness 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 down-load 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.

Support Information

Every effort has been made to ensure the accuracy of this book and the con-tents of the companion CD. To provide feedback on the book’s content or the companion CD, you can send e-mail to [email protected], or write to us at the following address:

Microsoft Excel Data Analysis and Business Modeling Editor

Microsoft Press/Microsoft Learning One Microsoft Way

(11)

Introduction xi

Microsoft Press provides corrections for books through the World Wide Web at http://www.microsoft.com/mspress/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/mspress/support

/search.asp. For support information regarding Microsoft Excel, you can connect

to Microsoft Technical Support on the Web at http://support.microsoft.com/.

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 par ticular 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 model ing 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 unfail ingly corrected my grammatical shortcomings and did not hesitate to make nec essary 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!

(12)

(13)

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?

■ 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 repre€ sent) 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.

(14)

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.

(15)

Chapter 1 Range Names 3

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.

Figure 1-2 By naming the cells that contain state sales with state abbre viations, 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 drop-down arrow in the Name box if you don’t believe that these range names have been created.

(16)

Creating Range Names by Using the Name Define Command

If you choose the Insert, Name Define command, the dialog box shown in Fig€ ure 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 Work-book 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.

(17)

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 Ala€ bama, 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 Histor€ icalInvest.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.)

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.

(18)

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 enter€ ing the 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 for€ mula =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 up-to-date monthly sales average. I caution you, however, that if you enter the for€ mula =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.

How Do I Delete a Range Name?

To delete a range name, simply choose Insert, Name Define to bring up the Define Name dialog box. (See Figure 1-5.) Select the range name you want to delete, and then click Delete. Unfortunately, Excel lets you delete only one range name at a time.

How Do I Change a Range Name?

To change a range name, select Insert, Name Define to bring up the Define Name dialog box. (See Figure 1-5.) Select the range whose name you want to

(19)

change, and then enter a new name or the cell range to which the range name refers. You can change the range by typing in the new range or by selecting the new range in the worksheet. Click OK, and the range is changed.

How Do I Name a Constant?

In many spreadsheets a value occurs so often that you might want to name it to use it as a constant. For example, suppose sales during the current year are $10 million and sales are growing 10 percent a year. (See the file Growth.xls.) We can name the annual growth rate Growth by choosing Insert, Name Define, filling in the Names In Workbook box and the Refers To area as shown in Figure 1-8, and then clicking OK.

Figure 1-8 You can name a value that occurs frequently in a workbook and use it as a constant.

Now everywhere in your spreadsheet, Growth will be interpreted as 1.1. If you haven’t already, open the file Growth.xls. Copying from F11 to G11:H11 the formula E11*Growth generates the sales for years 1–6 that grow at 10 percent a year, as you can see in Figure 1-9.

(20)

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?

Often you perform the same type of operation over and over. Wouldn’t it be nice if you could name the formula that performs the operation? Then, when-ever you needed to use the formula, you could call up the formula from the Paste Name dialog box. Here’s an example.

Suppose that we repeatedly average 12 cells below the current cell to compute average monthly sales for several products during a calendar year. (See the file Sales.xls and Figure 1-10.) We would like to start 2 rows below the current cell and average 12 cells to get an average of monthly sales. To name this operation, we begin by going to any cell. I used cell B2 in this example. Choose Insert, Name Define to bring up the Define Name dialog box. Select the name

annualaverage, and then look at the Refers To area, as indicated in Figure 1-11.

Figure 1-10 Data for naming a formula to average 12 months of sales.

(21)

You enter the formula that performs the operation given the current cell location. Because we are in cell B2, we want to average cells B4:B15, so we enter the formula =AVERAGE(B4:B15). You must type in the formula you want rather than enter the formula by pointing. Now, if we enter the name

annualaverage in any cell, the formula will drop down 2 cells and average the

next 12 cells below the current cell. To illustrate how this named formula works, I’ve entered the formula =annual average in cells C2:E2. You now obtain the annual average of sales for products 2–4. Note that if you are in cell E2 and you select Insert, Name Define, the Refers To area indicates that the range annualaverage refers to the formula =Average(E4:E15), which is the correct formula!

How can I easily select a cell range?

If you have selected a cell within a cell range, press Ctrl+Shift+* to select the entire range.

How can I paste a list of all range names (and the cells they represent) into

my spreadsheet?

Press F3 to display the Paste Name box, and then click the Paste List button. A list of range names and the cells each corresponds to will be pasted into your spreadsheet, beginning at the current cell location.

Remarks

■ Excel does not allow you to use the letters r and c as range names. Instead it will create a range name r_ or c_.

■ If you use Insert, Name Create to create a range name and your name contains spaces, Excel inserts an underscore (_) to fill in the spaces. For example, the name Product 1 is created as Product_1.

■ Range names cannot begin with numbers or look like a cell refer€ ence. For example, 3Q or A4 are not allowed as range names.

■ The only symbols allowed in range names are periods (.) and under-scores (_).

(22)

Problems

1. The file Stock.xls contains monthly stock returns for General Motors and Microsoft. Name the ranges containing the monthly returns for each stock and compute the average monthly return on each stock. 2. Create a formula to add up the six numbers below a given cell. 3. Suppose you want to name the range containing the cells A1:B3 and

A6:B8. You can select multiple ranges in Excel by holding down the Ctrl key between the selection of different ranges. Use this technique to give the cell range consisting of A1:B3 and A6:B8 the name Red.

(23)

Natural Language Range

Names

■ How do I create a name for a range of cells based on a spreadsheet label and use that name in formulas?

■ How do I use natural language range names to incorporate a rela- tionship such as Month(t) Ending Inventory = Month(t) Beginning

Inventory + Month(t) Production –Month(t) Demand in a spread-

sheet formula?

Microsoft Excel 2002 and Microsoft Office Excel 2003 give you the ability to create “natural language” range names based on labels already present in your spreadsheet. I’ll illustrate the use of natural language range names with two examples.

Note

This feature is available only in Excel 2002 and Excel 2003.

How do I create a name for a range of cells based on a spreadsheet label

and use that name in formulas?

In the file NaturalRange.xls, shipments from three plants to four cities are listed. We want to find the total amount shipped from each plant and the total shipped into each city. Figure 2-1 shows the information in NaturalRange.xls.

(24)

Figure 2-1 Shipments from plants to cities.

To use the natural language range feature, choose Tools, Options. On the Calculation tab, select the Accept Labels In Formulas option. Clearing this option makes the natural language range names invalid.

Next we enter in cell F5 the formula SUM(Plant 1). Note that the total shipments from Plant 1 (573 units) have been tabulated. Excel sensed from the Plant 1 label in row 5 that you wanted to name the range B5:E5 Plant 1. This is amazing, but there’s more! Copy the formula in F5 to the range F6:F7. In F6 you will see the formula SUM(Plant 2), and in F7 you will see SUM(Plant 3). You can see that Excel is smart enough to determine the “natural” way to copy the formula in F5, and it adjusted the range names used in F6 and F7 accordingly. In a similar fashion, copying the formula SUM(City 1) from B8 to C8:E8 com- putes the total shipments into each of the four cities. This use of natural range names makes the logic of the spreadsheet much easier to understand.

Finally, suppose we want to enter the amount shipped from Plant 1 to City 1 into a cell. We could just use the formula =B5. Alternatively, we could use the formula =Plant 1 City 1. Excel now looks for the cell at the intersection of the natural language names defined by Plant 1 and City 1. Of course, this is the spreadsheet entry in cell B5.

How do I use natural language range names to incorporate a relationship

such as Month(t) Ending Inventory = Month(t) Beginning Inventory +

Month(t) Production –Month(t) Demand in a spreadsheet formula?

Often, spreadsheets contain modeling relationships that relate quantities at different points in time. For example, if we are trying to update our ending inventory each month, an appropriate formula is:

Month(t)Ending Inventory = Month(t)Beginning Inventory + Month(t)Production -Month(t)Demand

If we are updating a toy store’s cash on hand at the end of every month, an appropriate formula is:

Month(t)Ending Cash = Month(t)Beginning Cash + Month(t)Cash Inflow -Month(t)Cash Outflow

(25)

Chapter 2 Natural Language Range Names 13

Natural language range names make it easy for a person viewing a spreadsheet to see how this logic was included in the spreadsheet. The file NatRangeII.xls (see Figure 2-2) shows how to model the computation of monthly inventory levels with natural language range names. We begin month 1 with 100 units in inventory and produce 150 units each month. Monthly demands are given in column D of the spreadsheet.

Figure 2-2 Computing inventory with natural language range names.

In cell B6 we set month 2 beginning inventory equal to month 1 ending inventory by entering the formula =E5. Copying this formula to the range B7:B10 computes beginning inventory for months 3–6. We use natural language range names to compute ending inventory by copying from E5 to E6:E10 the following formula:

=Beg Inv + Prod - Demand

To see what this formula does, focus on any of rows 5–10. For example, to evaluate the formula in cell E5, Excel finds the number in row 5 from the column headed Beg Inv, adds this number to the number in row 5 from the Prod column, and then subtracts the number in row 5 from the Demand column. Copying this formula down to rows 6 through 10 now computes the correct ending inventory level for each month. Note that if we had copied =B5+C5-D5 down to E6:E10, few people would have understood the logic of our computa- tions. The formula =Beg Inv+ Prod - Demand makes our logic more apparent.

Problems

1. A small candy store has $6,000 in cash on hand on January 1. Cash inflows and outflows for the months January–June are shown in the following table. Use natural language range names to compute each month’s ending cash position.

(26)

May June

Month Inflow Outflow

January February March April $5,000 $4,500 $6,000 $5,000 $5,200 $6,500 $7,200 $7,000 $8,000 $8,000 $9,000 $7,500

2. The following table gives the dollar sales of three products in four regions of the country: East, Midwest, South, and North. Use natural language range names to compute total sales of each product and the total dollar volume of sales in each region.

East Midwest South North

Product 1 $500 $600 $700 $650

Product 2 $600 $536 $665 $888

(27)

Lookup Functions

■ How do I write a formula to compute tax rates based on income?

■ Given a product ID, how can I look up the product’s price?

■ Suppose that a product’s price changes over time. I know the date the product was sold. How can I write a formula to compute the product’s price?

Syntax of the LOOKUP Functions

Lookup functions enable you to “look up” values from worksheet ranges. Excel allows you to perform both vertical lookups (using VLOOKUP functions) and horizontal lookups (using HLOOKUP functions). In a vertical lookup, the lookup operation starts in the first column of a worksheet range. In a horizontal lookup, the operation starts in the first row of a worksheet range. Because the great majority of formulas using lookup functions involve vertical lookups, we’ll concentrate on VLOOKUP functions.

VLOOKUP Syntax

The syntax of the VLOOKUP function is as follows. The brackets ([ ]) indicate optional arguments.

VLOOKUP(lookup value, table range, column index,[range lookup])

■ Lookup value is the value that we want to look up in the first column

of the table range.

(28)

■ Table range is the range that contains the entire lookup table. The

table range includes the first column, in which we try and match the lookup value, and any other columns in which we will look up for mula results.

■ Column index is the column number in the table range from which

the value of the lookup function is obtained.

■ Range lookup is an optional argument. If range lookup is True or is

omitted, the first column of the table range must be in ascending numerical order. If range lookup is True or omitted and an exact match to the lookup value is found in the first column of the table range, Excel keys the lookup off the row of the table in which the exact match is found. If range lookup is True or omitted and an exact match does not exist, Excel keys the lookup off the largest value in the first column that is less than the lookup value. If range lookup is False and an exact match to the lookup value is found in the first col umn of the table range, Excel keys the lookup off the row of the table in which the exact match is found. If no exact match is obtained, Excel returns an #N/A response (Not Available).

HLOOKUP Syntax

For an HLOOKUP function, simply change “column” to “row.” Thus, in an HLOOKUP function, Excel tries to locate the lookup value in the first row (not the first column) of the table range.

Let’s explore some interesting examples of lookup functions.

How do I write a formula to compute tax rates based on income?

The following example shows how a VLOOKUP function works when the first column of the table range consists of numbers in ascending order. Suppose that the tax rate depends on income as follows:

Income Level Tax Rate

$0-$9,999 15 percent

$10,000-$29,999 30 percent

$30,000-$99,999 34 percent

$100,000 and over 40 percent

To see how to write a formula that computes the tax rate for any income level, open the file Lookup.xls, shown in Figure 3-1.

(29)

Chapter 3 Lookup Functions 17

Figure 3-1 Using a lookup function to compute a tax rate. The numbers in the first column of the table range are sorted in ascending order.

I began by entering the relevant information (tax rates and break points) in cell range D6:E9. I named the table range D6:E9 lookup. I recommend that you always name the cells you’re using as the table range. Then you need not remem ber the exact location of the table range, and when you copy any formula involving a lookup function, the lookup range will always be correct. To illustrate how the lookup function works, I entered some incomes in the range D13:D17. By copying from E13:E17 the formula VLOOKUP(D13,Lookup,2,True), we compute the tax rate for the income levels listed in D13:D17. Let’s examine how the lookup func tion worked in cells E13:E17. Note that because the column index in the formula is 2, the answer always comes from the second column of the table range.

■ In D13, the income of -$1,000 yields #N/A because -$1,000 is less than the lowest income level in the first column of the table range.

■ In D14, the income of $30,000 exactly matches a value in the first col umn of the table range, so the function returns a tax rate of 34 percent.

■ In D15, the income level of $29,000 does not exactly match a value in the first column of the table range, which means the lookup func tion stops at the largest number less than $29,000 in the first column of the range; $10,000 in this case. This function returns the tax rate in column 2 of the table range opposite $10,000, or 30 percent.

■ In D16, the income level of $98,000 does not yield an exact match in the first column of the table range. The lookup function stops at the largest number less than $98,000 in the first column of the table range. This returns the tax rate in column 2 of the table range oppo site $30,000, 34 percent.

(30)

■ In D17, the income level of $104,000 does not yield an exact match in the first column of the table range. The lookup function stops at the largest number less than $104,000 in the first column of the table range, which returns the tax rate in column 2 of the table range opposite $100,000, 40 percent.

■ In F13:F17, we changed the value of the range lookup argument from True to False and copied from F13 to F14:F17 the formula

VLOOKUP(D13,Lookup,2,False). Cell F14 still yields a 34 percent tax

rate because the first column of the table range contains an exact match to $30,000. The other entries in F13:F17 all display #N/A because none of the other incomes in D13:D17 have an exact match in the first column of the table range.

Given a product ID, how can I look up the product’s price?

Often the first column of a table range does not consist of numbers in ascend ing order. For example, the first column of the table range might list product ID codes or employee names. In my experience teaching thousands of financial analysts, I’ve found that many people don’t know how to deal with lookup functions when the first column of the table range does not consist of numbers in ascending order. In these situations, you need to remember only one simple rule: use False as the value of the range lookup argument.

Here’s an example. In the file Lookup.xls (see Figure 3-2), you can see the prices for 5 products, listed by their ID code. How do you write a formula that will take a product ID code and return the product price?

Figure 3-2 Looking up prices from product ID codes. In cases like these, when the table range isn’t sorted in ascending order, enter False as the last argument in the lookup function formula.

(31)

M a n y p e o p l e w o u l d e n t e r t h e f o r m u l a a s I h a v e i n c e l l I 1 8 ,

VLOOKUP(H18,Lookup2,2). However, note that when you omit the fourth argu

ment (the range lookup argument), the value is assumed to be True. Because the product IDs in the table range Lookup2 (H11:I15) are not listed in alphabet ical order, an incorrect price ($3.50) is returned. If we enter in cell I18 the for mula VLOOKUP(H18,Lookup2,2, False), the correct price ($5.20) is returned.

You would also use False in a formula designed to look up an employee’s salary using the employee’s last name or ID number.

Suppose that a product’s price changes over time. I know the date the

product was sold. How can I write a formula to compute the product’s

price?

Suppose the price of a product depends on the date the product was sold. How can you use a lookup function in a formula that will pick up the correct product price? More specifically, suppose the price of a product is as follows.

Date Sold Price

January-April 2005 $98

May-August 2005 $105

September-December 2005 $112

We’ll write a formula that will determine the correct product price for any date on which the product is sold in the year 2005. For variety, we’ll use an

HLOOKUP function. I’ve placed the dates when the price changes in the first

row of the table range. See the file DateLookup.xls, shown in Figure 3-3.

Figure 3-3 Using an HLOOKUP function to determine a price that changes depending on the date it’s sold.

I copied from C8 to C9:C11 the formula HLOOKUP(B8,lookup,2,TRUE). This formula tries to match the dates in column B with the first row of the range

(32)

B2:D3. At any date between 1/1/05 and 4/30/05, the lookup function will stop at 1/1/05 and return the price in B3; for any date between 5/01/05 and 7/31/05, the lookup stops at 5/1/05 and returns the price in C3; and for any date later than 8/01/05, the lookup stops at 8/01/05 and returns the price in D3.

Problems

1. The file Hr.xls gives employee ID codes, salaries, and years of expe rience. Write a formula that takes a given ID code and yields the employee’s salary. Write another formula that takes a given ID code and yields the employee’s years of experience.

2. The file Assign.xls gives the assignment of workers to four groups. The suitability of each worker for each group (on a 0-10 scale) is also given. Write a formula that gives the suitability of each worker for the group to which the worker is assigned.

3. You are thinking of advertising Microsoft products on a sports tele cast. As you buy more ads, the price of each ad drops as described in the following table.

Number of Ads Price Per Ad

1-5 $12,000

6-10 $11,000

11-20 $10,000

More than 20 $9,000

For example, if you buy 8 ads, you pay $11,000 per ad, but if you buy 14 ads, you pay $10,000 per ad. Write a formula that yields the total cost of purchasing any number of ads.

4. You are thinking of advertising Microsoft products on a popular TV music program. You pay one price for the first group of ads, but as you buy more ads, the price per ad drops as described in the follow ing table:

Number of Ads Price Per Ad

1-5 $12,000

6-10 $11,000

11-20 $10,000

(33)

For example, if you buy 8 ads, you pay $12,000 per ad for the first 5 ads and $11,000 for each of the next 3 ads. If you buy 14 ads, you pay $12,000 for each of the first 5 ads, $11,000 for each of the next 5 ads, and $10,000 for each of the last 4 ads. Write a formula that yields the total cost of purchasing any number of ads. Hint: You will probably need at least three columns in your table range and your formula might involve two lookup functions.

5. The annual rate your bank charges you to borrow money for 1, 5, 10, or 30 years is given in the following table.

Duration of Loan (Years) Annual Loan Rate (Percent)

1 6 5 7

10 9

30 10

If you borrow money from the bank for any duration between 1 and 30 years that’s not listed in the table, your rate is found by interpolating between the rates given in the table. For example, let’s say you borrow money for 15 years. Because 15 years is one quarter of the way between 10 years and 30 years, the annual loan rate would be calculated as follows:

Write a formula that will return the annual interest rate on a loan for any period between 1 and 30 years.

6. The distance between any two U.S. cities (excluding cities in Alaska and Hawaii) can be approximated by the formula

69*

The file CityData.xls contains the latitude and longitude of selected U.S. cities. Create a table that gives the distance between any two of the listed cities.

(34)

(35)

The INDEX Function

■ I have a list of distances between U.S. cities. How do I write a func tion that returns the distance between, say, Seattle and Miami?

■ Is there any way I can write a formula that references the entire column containing the distances of each city to Seattle?

Syntax of the INDEX Function

The INDEX function allows you to return the entry in any row and column within a rectangular array of numbers. The most commonly used syntax for the INDEX function is:

INDEX(Array, Row Number, Column Number)

To illustrate, the formula INDEX(A1:D12,2,3) would return the entry in the second row and third column of the array A1:D12. This entry is the one in cell C2.

I have a list of distances between U.S. cities. How do I write a function

that returns the distance between, say, Seattle and Miami?

The file Index.xls (see Figure 4-1) contains the distances between eight U.S. cities. The range C10:J17, which contains the distances, is named Distances.

(36)

Figure 4-1 You can use the INDEX function to calculate the distance between U.S. cities.

Suppose that you want to enter the distance between Boston and Denver in a cell. Because distances from Boston are listed in the first row of the array named Distances, and distances to Denver are listed in the fourth column of the array, the appropriate formula is INDEX(distances,1,4). We find that Boston and Denver are 1991 miles apart. Similarly, to find the (huge) distance between Seattle and Miami, you would use the formula INDEX(distances,6,8). Seattle and Miami are 3389 miles apart.

Imagine that the Seattle Sonics basketball team is embarking on a road trip in which they play games in Phoenix, Los Angeles, Denver, Dallas, and Chicago. At the conclusion of the road trip, the Sonics return to Seattle. Can we easily compute how many miles they travel on the trip? As you can see in Figure 4-2, we simply list in order the cities the Sonics visit (8-7-5-4-3-2-8), starting and ending in Seattle, and copy from D21 to D26 the formula INDEX(distances,C21,C22). The formula in D21 computes the distance between Seattle and Phoenix (city number 7), the formula in D22 computes the distance between Phoenix and Los Angeles, and so on. The Sonics will travel a total of 7112 miles on their road trip. By the way, I used the INDEX function to show that the Miami Heat log more miles during the NBA season than any other team.

(37)

Chapter 4 The INDEX Function 25

Is there any way I can write a formula that references the entire column

containing the distances of each city to Seattle?

The INDEX function makes it easy to reference an entire row or column of an array. If we set the row number to 0, the INDEX function references the listed column. If we set the column number to 0, the INDEX function references the listed row. To illustrate, suppose we want to total the distances from each listed city to Seattle. We could enter either of the following formulas:

SUM(INDEX(distances,8,0)) SUM(INDEX(distances, 0,8))

The first formula adds up the numbers in the eighth row (row 17) of the Distances array; the second formula adds up the numbers in the eighth column (Column J) of the Distances array. In either case we find the total distance from Seattle to the other cities is 15,221 miles, as you can see in Figure 4-3.

Figure 4-3 Total distances of cities to Seattle.

Problems

1. Use the INDEX function to compute the distance between LA and Phoenix and between Denver and Miami.

2. Use the INDEX function to compute the total distance from Dallas to all cities.

3. Mark Cuban and the Dallas Mavericks are embarking on a road trip that takes them to Chicago, Denver, Los Angeles, Phoenix, and Seattle. How many miles will they travel on this road trip?

4. The file Product.xls contains monthly sales for six products. Use the INDEX function to compute the sales of product 2 in March. Use the INDEX function in a formula that computes total sales during April.

(38)

(39)

The MATCH Function

■ Given monthly sales for several products, how do I write a formula that will calculate the sales of a product during a given month? For example, how much of product 2 did I sell during June?

■ Given a list of baseball player salaries, how do I write a formula that yields the player with the highest salary? How about the player with the fifth highest salary?

■ Given the annual cash flows from an investment project, how do I write a formula that returns the number of years required to pay back the project’s initial investment cost?

Suppose you have a spreadsheet with 5000 rows containing 5000 names. You need to find the name John Doe, which you know appears somewhere (and only once) in the list. Wouldn’t you like to know a formula that would return the row number at which John Doe is located? The Excel MATCH func tion enables you to find within a given array the first occurrence of a “match” to a given text string or number. You should use the MATCH function instead of a LOOKUP function in situations in which you want the position of a number in a range rather than the value in a particular cell. The syntax of the match function is:

Match(lookup value, lookup range, match type)

In the explanation that follows, we’ll assume that all cells in the lookup range are located in the same column. In this syntax,

■ Lookup value is the value you’re trying to match in the lookup range.

■ Lookup range is the range you’re examining for a “match” to the

lookup value.

(40)

■ Match type =1 requires your lookup range to consist of numbers

listed in ascending order. The MATCH function then returns the row location in the lookup range (relative to the top of the lookup range) that contains the largest value in the range that is less than or equal to the lookup value. Match type = -1 requires the lookup range to consist of numbers listed in descending order. The MATCH function returns the row location in the lookup range (relative to the top of the lookup range) that contains the last value in the range that is greater than or equal to the lookup value. No matter in what order the values or text in the lookup range are listed (ascending, descend ing, or no order), match type = 0 returns the row location in the lookup range that contains the first exact match to the lookup value. When no exact match exists and match type = 0, Excel returns #N/A. The great majority of MATCH function applications use match type =

0, but if match type is not included, match type = 1 is assumed.

The file MatchEx.xls, shown in Figure 5-1, contains three examples of the MATCH function’s syntax.

Figure 5-1 Examples of using the MATCH function to locate the position of a value in a range.

In cell B13, the formula MATCH("Boston",B4:B11,0) returns 1 because the first row in the range B4:B11 contains the value Boston. Notice that text values must be enclosed in quotation marks (“”). In cell B14, the formula

MATCH("Phoenix",B4:B11,0) returns 7 because cell B10 (the seventh cell in

B4:B11) is the first cell in the range that matches “Phoenix”. In cell E12, the for mula MATCH(0,E4:E11,1) returns 4 because the last number that is less than or equal to 0 in the range E4:E11 is in cell E7 (the fourth cell in the lookup range). In cell G12, the formula MATCH(-4,G4:G11,-1) returns 7 because the last num ber that is greater than or equal to -4 in the range G4:G11 is contained in cell G10 (the seventh cell in the lookup range).

(41)

Chapter 5 The MATCH Function 29

Unlike LOOKUP functions, the MATCH function can work with an inexact match. For example, the formula MATCH("Pho*",B4:B11,0) returns 7. The aster isk is treated as a wildcard, which means that Excel searches for the first text string in the range B4:B11 that begins with Pho.

If the lookup range is contained in a single row, Excel returns the relative position of the first match in the lookup range, moving left to right. As shown in the following examples, the MATCH function is often very useful when it is combined with other Excel functions, such as VLOOKUP, INDEX, or MAX.

Given monthly sales for several products, how do I write a formula that

will calculate the sales of a product during a given month? For example,

how much of product 2 did I sell during June?

The file ProductLookup.xls (shown in Figure 5-2) lists sales of four NBA bob ble-head dolls for January through June. How can we write a formula that com putes the sales of a given product during a given month? The trick is to use one MATCH function to find the row in which the given product is located and another MATCH function to find the column in which the given month is located. We can then use the INDEX function to return the product sales.

Figure 5-2 The MATCH function can be used in combination with func tions such as INDEX and VLOOKUP.

We have named the range B4:G7, which contains sales data for the dolls, as Sales. We enter the product we want to know about in cell A10 and the m o n t h w e ’ r e e x a m i n i n g i n c e l l B 1 0 . I n C 1 0 , w e u s e t h e f o r m u l a

MATCH(A10,A4:A7,0) to determine which row number in the range Sales con

tains sales figures for the Kobe doll. Then, in cell D10, we use the formula

MATCH(B10,B3:G3,0) to determine which column number in the range Sales

contains June sales. Now that we have the row and column numbers that con tain the sales figures we want, we can use the formula (INDEX(Sales,C10,D10)) in cell E10 to yield the piece of sales data we want. For more information on the INDEX function, see Chapter 4.

(42)

Given a list of baseball player salaries, how do I write a formula that

yields the player with the highest salary? How about the player with the

fifth highest salary?

The file Baseball.xls (see Figure 5-3) lists the salaries paid to 401 major league baseball players during the 2001 season. The data is not sorted in order of sal ary, and we want to write a formula that returns the name of the player with the highest salary as well as the name of the player with the fifth highest salary. To find the name of the player with the highest salary we proceed as follows:

■ Use the MAX function to determine the value of the highest salary.

■ Use the MATCH function to determine the row that contains the player with the highest salary.

■ Use a VLOOKUP function (keying off the data row containing the player’s salary) to look up the player’s name.

We have named the range C12:C412, which includes the players’ salaries, as Salaries. We’ve named the range used in our LOOKUP function (range A12:C412) as Lookup.

Figure 5-3 This example uses the MAX, MATCH, and VLOOKUP func tions to find and display the highest value in a list.

In cell C9, we begin by finding the highest player salary ($22 million) with t h e f o r mu l a M A X ( S a l a r i e s ) . N e x t , i n c e l l C 8 , w e u s e t h e f o r m u l a

MATCH(C9,Salaries,0) to determine the “player number” of the player with the

highest salary. We use match type = 0 because the salaries are not listed in ascending or descending order. We find that player number 345 on the list has t h e h i g h e s t s a l a r y . F i n a l l y , i n c e l l C 6 , w e u s e t h e f u n c t i o n

(43)

Chapter 5 The MATCH Function 31

the lookup range. Not surprisingly, we find that Alex Rodriguez was the highest paid player in 2001.

To find the name of the fifth highest player, we need a function that yields the fifth largest number in an array. The LARGE function does that job. The syn tax of the large function is LARGE(cell range, k). When the LARGE function is entered in this fashion, it returns the kth largest number in a cell range. Thus, the formula LARGE(salaries,5) in cell D9 yields the fifth largest salary ($12.6 million). Proceeding as before, we find that Derek Jeter is the player with the fifth highest salary. (The dl indicates that at the beginning of the season, Jeter was on the disabled list.)

Given the annual cash flows from an investment project, how do I write a

formula that returns the number of years required to pay back the

project’s initial investment cost?

The file Payback.xls, shown in Figure 5-4, shows the cash flows for an invest ment project over the next 15 years. We assume that at time 0, the project required a cash outflow of $100 million. During year 1, the project generated a cash inflow of $14 million. We expect cash flows to grow at 10 percent per year. How many years will pass before the project pays back its investment?

The number of years required for a project to payback an investment is called the payback period. In high-tech industries, payback period is often used to rank investments. (You’ll learn in Chapter 8, however, that payback is flawed as a measure of investment quality because payback ignores the value of money over time.) For now, let’s just concentrate on how to determine the pay-back period for our simple investment model.

Figure 5-4 In this example, we’ll use the MATCH function to calculate an investment’s payback period.

(44)

To determine the payback period for the project, we proceed as follows:

■ In column B we compute the cash flows for each year.

■ In column C we compute the cumulative cash flows (total to date) for each year.

■ We use the MATCH function (with match type =1) to determine the row number of the first year in which cumulative cash flow is posi tive. This calculation gives us the payback period.

We gave the cells in B1:B3 the range names listed in A1:A3. Our year 0 cash flow (-Initial_investment) is entered in cell B5. Our year 1 cash flow (Year_1_cf) is entered in cell B6. Copying from B7 to B8:B20 the formula

B6*(1+Growth) computes the cash flow for years 2-15.

To compute the year 0 cumulative cash flow, we enter the formula B5 in cell C5. For later years we calculate cumulative cash flow by using a formula such as Year t cumulative cash flow = Year t – 1 cumulative cash flow + Year t

cash flow. To implement this relationship, simply copy from C6 to C7:C20 the

formula =C5+B6.

To compute the payback period, we use the MATCH function (with match

type = 1) to compute the last row of the range C5:C20 containing a value less

than 0. This calculation will always give us the payback period. For example, if the last row in C5:C20 that contains a value less than 0 is the sixth row in the range, that means the seventh value marks the cumulative cash flow for the first year the project is paid back. Because our first year is year 0, the payback occurs during year 6. Therefore, the formula in cell E2, MATCH(0,C5:C20,1) yields the payback period (6 years). If any cash flows after year 0 were nega tive, this method would fail because our range of cumulative cash flows would not be listed in ascending order.

Problems

1. Using the distances between U.S. cities given in Chapter 4, write a formula with the MATCH function to determine (based on the names of the cities) the distance between any two of the given cities. 2. The file MatchType1.xls lists in chronological order the dollar

amount of 30 transactions. Write a formula that yields the first trans-action for which total volume to date exceeds $10,000.

3. The file MatchTheMax.xls gives the product ID code and unit sales for 265 different products. Use the MATCH function in a formula that yields the product ID of the product with the largest unit sales.

(45)

Text Functions

■ I have a spreadsheet in which each cell contains a product descrip tion, a product ID, and a product price. How can I put all the prod uct descriptions in column A, all the product IDs in column B, and all the prices in column C?

■ Every day I receive data about total U.S. sales, which is computed in a cell as the sum of East, North, and South region sales. How can I extract East, North, and South sales to separate cells?

■ I download quarterly gross national product (GNP) data from the Web. The cell containing first quarter data for 1980 contains the entry 1980.1 5028.8. How can I place the date and GNP value in different cells?

■ In the spreadsheet I use for a mailing list, column A contains peo ple’s names, column B contains their street address, and column C contains their city and zip code. How can I create each person’s full address in column E?

When someone sends you data or you download data from the Web, often the data isn’t formatted the way you want. For example, when downloading sales data, dates and sales amounts might be in the same cell but you need them to be in separate cells. How can you manipulate data so that it has the format you need? The answer is to become good at using Excel’s set of text functions. In this chapter, I’ll show you how to use the following

(46)

Excel text functions to magically manipulate your data until it looks the way you want: ■ LEFT ■ RIGHT ■ MID ■ TRIM ■ LEN ■ FIND ■ SEARCH ■ CONCATENATE ■ REPLACE ■ VALUE

Text Function Syntax

The file Reggie.xls, shown in Figure 6-1, includes examples of text functions. You’ll see how to apply these functions to a specific problem later in the chap ter, but let’s begin by describing what each of the text functions do. Then we’ll combine these functions to perform some fairly complex manipulations of data.

Figure 6-1 Examples of text functions.

The LEFT Function

The function LEFT(text,k) returns the first k characters in a text string. For exam ple, cell C3 contains the formula LEFT(A3,4). Excel returns Regg.

(47)

Chapter 6 Text Functions 35

The RIGHT Function

The function RIGHT(text,k) returns the last k characters in a text string. For example, in cell C4, the formula RIGHT(A3,4) returns ller.

The MID Function

The function MID(text, k, m) begins at character k of a text string and returns the next m characters. For example, the formula MID(A3,2,5) in cell C8 returns characters 2-6 from cell A3, the result being eggie.

The TRIM Function

The function TRIM(text) removes all spaces from a text string except for single spaces between words. For example, in cell C5 the formula TRIM(A3) elimi nates two of the three spaces between Reggie and Miller and yields Reggie

Miller.

The LEN Function

The function LEN(text) returns the number of characters in a text string (spaces are included). For example, in cell C6 the formula LEN(A3) returns 15 because cell A3 contains 15 characters. In cell C7, the formula LEN(C5) returns 13. Because the trimmed result in cell C5 has two spaces removed, cell C5 contains two less characters than the original text in A3.

The FIND and SEARCH Functions

The function FIND(text to find, actual text, k) returns the location at or after character k of the first character of text to find in actual text. FIND is case sensi tive. SEARCH has the same syntax as FIND, but it is not case sensitive. For exam ple, if we enter FIND ("r",A3,1) in cell C10, Excel returns 15, the location of the first lowercase r in the text string Reggie Miller. (The uppercase R is ignored because FIND is case sensitive.) Entering SEARCH("r",A3,1) in cell C11 returns 1 because SEARCH matches r to either a lowercase or an uppercase character. Entering FIND(" ",A3,1) in cell C9 returns 7 because the first space in the string

Excel Manual

1

2

3

4

5

6

7

8

9

10

Table of Contents

Range Names

Natural Language Range Names

Lookup Functions

The INDEX Function

The MATCH Function

Text Functions

Dates and Date Functions

Evaluating Investments with Net Present Value Criteria

Internal Rate of Return

Functions for Personal Financial Decisions:

The PV, FV, PMT, PPMT, and IPMT Functions

11

Circular References

12

IF Statements

13

The Paste Special Command

1

11

15

23

27

33

43

51

59

67

77

83

99

14

The Auditing Tool

107

15

Sensitivity Analysis with Data Tables

115

16

The Goal Seek Command

129

17

Using the Scenario Manager for Sensitivity Analysis

137

18

Creating and Using Spinners for Sensitivity Analysis

143

19

The COUNTIF, COUNT, COUNTA, and COUNTBLANK Functions

151

20

The SUMIF Function

159

21

The OFFSET Function

163

22

The INDIRECT Function

175

23

Conditional Formatting

181

24

An Introduction to Optimization with the Excel Solver

191

25

Using Solver to Determine the Optimal Product Mix

197

26

Using Solver to Solve Transportation or Distribution Problems