Quick Course® in Microsoft® Excel 2000

Author Online Press, Inc. Pages 176 Disk N/A Level Beg/Int Published 02/16/2000 ISBN 9780735610811 Price $9.99 To see this book's discounted price, select a reseller below.	More Information About the Book Table of Contents Sample Chapter Related Series Related Books About the Author Support: Book & CD Rate this book

Chapter 3: Performing Calculations

• Simple Calculations
  • Doing Arithmetic
  • Totaling Columns of Values
  • Referencing Formula Cells in Other Formulas
  • Naming Cells and Ranges
  • Creating a Calculation Area
• More Calculations
  • Averaging Values
  • Identifying Highest and Lowest Values
  • Calculating with Names
• Formulas That Make Decisions
• Copying Formulas
• Linking Worksheets with Formulas
• Printing and Publishing Worksheets
  • Previewing Worksheets
  • Setting Up the Pages
  • Printing Worksheets
  • Publishing on an Intranet or the World Wide Web

In this chapter, you create a calculation area and explore some of Excel's functions, including the decision-making IF function. You use formulas to link worksheets, and finally, you print your worksheet and publish it as a Web page.

If you don't work in sales, you can adapt the techniques covered in this chapter to analyze other sources of income, such as service fees, subscriptions, or donations.

Chapters 1 and 2 covered some Excel basics, and you now know enough to create simple tables. But you are missing the essential piece of information that turns a table into a worksheet: how to enter formulas. The whole purpose of building worksheets is to have Excel perform calculations for you. In this chapter, we show you how to enter formulas in the 1999 Sales workbook so that you can analyze sales. Along the way, we cover some powerful techniques for manipulating data and a few principles of worksheet design. So fire up Excel, and then we'll get started.

Simple Calculations

Excel has many powerful functions that are a sort of shorthand for the various formulas used in mathematical, logical, statistical, financial, trigonometric, logarithmic, and other types of calculations. However, the majority of worksheets created with Excel involve simple arithmetic. In this section, we show you how to use four arithmetic operators (+, -, *, and /) to add, subtract, multiply, and divide, and then we introduce two Excel features with which you can quickly add sets of numeric values.

Doing Arithmetic

In Excel, you begin a formula with an equal sign (=). In the simplest formulas, the equal sign is followed by a set of values separated by +, -, *, or /, such as

=5+3+2

If you enter this formula in any blank cell in a worksheet, Excel displays the result 10.

Let's experiment with formulas in the 1999 Sales workbook. You want most of the calculations and formatting you do in this chapter to take effect in all the quarter worksheets, so you'll start by grouping them and inserting a couple of blank rows to give you space to work:

1. In the 1999 Sales workbook, hold down the Shift key and click the tab for each quarter's worksheet in turn to group the four worksheets.

2. Drag through the headers for rows 4 and 5 of the 1st Quarter worksheet to select the two rows. Then right-click anywhere in the selected rows and choose Insert from the shortcut menu. Because you selected two rows, Excel inserts two blank rows on each of the four grouped worksheets, moving the tables down so that they begin in row 6.

Now you're ready to construct a formula in cell B5, using some of the values in the Amount of Sale column. You tell Excel to use a value simply by clicking the cell that contains it. Follow these steps:

1. Click cell B5 and type an equal sign followed by an opening parenthesis.

2. Click cell E7. Excel inserts the cell reference E7 in the cell and the formula bar.

3. Type a plus sign and click cell E8. Excel adds the cell reference E8 to the formula.

4. Continue to build the formula by typing plus signs and clicking cells E9, E10, and E11.

5. Type a closing parenthesis followed by a / (the division operator), and then type 5. The formula now looks like this:

   
   Click to view graphic

   This formula tells Excel to first add the amounts in cells E7, E8, E9, E10, and E11 and then divide the result by 5 to obtain the average of the five amounts.

6. Click the Enter button. Excel displays the result of the formula, 1688.612, in cell B5.

   You can use this same technique to create any simple formula. Start by typing an equal sign, then either enter a value or click the cell that contains the value, type the appropriate arithmetic operator, enter the next value, and so on. Unless you tell Excel to do otherwise, the program performs multiplication and division before addition and subtraction. If you need certain parts of the formula to be carried out in a different order, use parentheses as you did in this example to override the default order.

Totaling Columns of Values

Although this method of creating a formula is simple enough, it would become tedious if you had to type and click in order to add a long series of values. Fortunately, Excel automates the addition process with a very useful button: the AutoSum button.

Using the AutoSum Button

The AutoSum button will probably become one of your most often-used Excel buttons. In fact, using this button is so easy that we'll dispense with explanations and simply show you what to do:

1. Select cell E19.

2. Click the AutoSum button on the Standard toolbar. Excel looks first above and then to the left of the active cell for an adjacent range of values to total. Excel assumes that you want to total the values above E19 and enters the SUM function in cell E19 and in the formula bar. Your worksheet looks like the one shown below.

   
   Click to view graphic

3. Click the Enter button to enter the formula in cell E19. Excel displays the result $16,804.16—the sum of the values in E7:E18. (If necessary, widen the cell to see the value.)

   That was easy. The AutoSum button serves you well whenever you want a total to appear at the bottom of a column or to the right of a row of values. But what if you want the total to appear elsewhere on the worksheet? Knowing how to create SUM functions from scratch gives you more flexibility.

Using the SUM Function

Let's go back and dissect the SUM function that Excel inserted in cell E19 when you clicked the AutoSum button so that you can examine the function's components.

With cell E19 selected, you can see the following entry in the formula bar:

=SUM(E7:E18)

Like all formulas, the SUM function begins with an equal sign (=). Next comes the function name in capital letters, followed by a set of parentheses enclosing the reference to the range containing the amounts you want to total. This reference is the SUM function's argument. An argument answers questions such as "What?" or "How?" and gives Excel the additional information it needs to perform the function. In the case of SUM, Excel needs only one piece of information—the references of the cells you want to total. As you'll see later, Excel might need several pieces of information to carry out other functions, and you enter an argument for each piece.

Creating a SUM function from scratch is not particularly difficult. For practice, follow these steps:

1. Select cell B5, and type this:

   =SUM(

   When you begin typing, the cell's old value is overwritten.

2. Select E7:E18 on the worksheet in the usual way. Excel inserts the reference E7:E18 after the opening parenthesis.

3. Type a closing parenthesis and then press Enter. Excel displays in cell B5 the total of the values in the Amount of Sale column—$16,804.16.

Referencing Formula Cells in Other Formulas

After you create a formula in one cell, you can use its result in other formulas simply by referencing its cell. To see how this works, follow these steps:

1. Select cell C5 and type an equal sign.

2. Click cell B5, which contains the SUM function you just entered, type a / (the division operator), and then type 12.

3. Click the Enter button. Excel displays the result—the average of the invoice amounts—in cell C5. (We discuss an easier way to calculate averages on page 68.)

4. Press the Delete key to erase both the experimental formula and its result from cell C5.

Naming Cells and Ranges

Many of the calculations that you might want to perform on this worksheet—for example, calculating each sales amount as a percentage of total sales—will use the total you have calculated in cell B5. You could include a copy of the SUM function now in cell B5 in these other calculations, or you could simply reference cell B5. The latter method seems quick and simple, but what if you subsequently move the formula in B5 to another location? Excel gives you a way to reference this formula no matter where on the worksheet you move it. You can assign cell B5 a name and then use the name in any calculations that involve the total.

Here's how to assign a name to a cell:

1. Select cell B5 and click the name box located to the left of the formula bar.

2. Type Total and press Enter. The name box now contains the cell's name instead of its reference. You can use either designation in formulas.

To see how Excel uses the names you assign, try this:

1. Select cell E19, which currently contains the SUM function you inserted earlier in the chapter.

2. Type =Total and press Enter. The worksheet does not appear to change, but now instead of two SUM functions, the worksheet contains only one. You have told Excel to assign the value of the cell named Total, which contains the SUM function, to cell E19.

You can also assign names to ranges. Let's use a different method to assign the name Amount to the cells containing amounts in column E:

1. Select E7:E18 and choose Name and then Define from the Insert menu to display this dialog box:

   
   Click to view graphic

   The reference '1st Quarter'!$E$7:$E$18 displayed in the Refers To edit box is an absolute reference to the range E7:E18 on the 1st Quarter sheet of the current workbook. (For an explanation of absolute references, see below.)

2. Replace Excel's suggested name with Amount and press Enter.

Now let's replace the range reference in the SUM function in cell B5 with the new range name. (You can't do this while the worksheets are grouped.) Follow these steps:

1. First hold down the Shift key and click the 1st Quarter tab to ungroup the sheets. Then click B5 to select it and to display its contents in the formula bar.

2. Drag through the E7:E18 reference in the formula bar to highlight it. (While you're dragging, Excel displays the reference in blue and outlines the actual range with a blue border. See the tip on page 73 to find out why.)

3. Choose Name and then Paste from the Insert menu to display this dialog box:

   
   Click to view graphic

4. Select Amount and click OK. Excel replaces the range reference with the name assigned to the range, and the formula bar now reads =SUM(Amount).

5. Click the Enter button. The total in cell B5 remains the same as before, even though you've changed the formula.

6. Click the Save button to save your work.

From now on, we won't give you specific instructions to save your work, but you should get in the habit of saving often, perhaps after working through each example.

Creating a Calculation Area

Before we discuss other calculations you might want to perform with this worksheet, let's look at ways to format your information to make the results of calculations stand out from the data. As your worksheets grow in complexity, you'll find that paying attention to such details will keep you oriented and help others understand your results.

Usually when you create a worksheet, you are interested not so much in the individual pieces of information as in the results of the calculations you perform on the pieces. The current worksheet is not much bigger than one screen, but often worksheets of this type include many screenfuls of information. It's a good idea to design your worksheets so that the important information is easily accessible and in a predictable location. For these reasons, we suggest that you leave room in the top left corner of your worksheets for a calculation area. This habit is useful for the following reasons:

• You don't have to scroll around looking for totals and other results.

• You can print just the first page of a worksheet to get a report of the most pertinent information.

• You can easily jump to the calculation area from anywhere on the worksheet by pressing Ctrl+Home to move to cell A1.

To see firsthand how helpful this can be, create an area at the top of each of the grouped quarter sheets of the 1999 Sales workbook for a set of calculations. Start by freeing up some more space below the worksheet title:

1. Group the four quarter sheets and then select A6:E19.

2. Then use the Cut and Paste buttons to move the selection to A13:E26. (Leave the entry in B5 where it is.)

3. Press Ctrl+Home. Your screen now looks like the one shown below.

   
   Click to view graphic

Now you need to make the calculation area stand out. With Excel, you can get really fancy, using colored fonts and shading to draw attention to calculation results. For now, though, let's place a simple border around the calculation area:

1. Select A4:E11 and choose Cells from the Format menu.

2. When the Format Cells dialog box appears, click the Border tab to display these options:

   
   Click to view graphic

3. In the Style list of the Line section, select the fifth option in the column on the right. In the Presets section, select Outline.

4. Now click the Patterns tab to display these options:

   
   Click to view graphic

5. If you have a color printer, simply pick a light color for the background of the selected cells and then click OK. (We left the background white so that our screen graphics would be legible.)

Now let's add another touch:

1. Select A5:A10.

2. Click the Bold button on the Formatting toolbar.

1. Select cell A5, type Total Sales, and click the Enter button. The new heading is bold because you already applied the Bold style to cell A5.

2. Without moving the selection, choose Column and AutoFit Selection from the Format menu to adjust the width of column A to the longest entry. (From now on, use this technique to adjust columns as necessary to see their contents.)

3. For good measure, select cell B5, click the Format Painter button, and select B6:B10 to copy the currency format to that range of cells.

4. Press Ctrl+Home. The results are shown below.

   
   Click to view graphic

More Calculations

Let's perform some more calculations on the sales data, starting with the average sale.

Averaging Values

To find the average of the invoices in this worksheet, you can build a formula that includes Excel's AVERAGE function. To avoid making errors while typing function names and to make sure you include all the arguments needed for the calculation, you'll want to use the Paste Function button. Follow these steps:

1. Select cell A6, type Average Sale, and click the Enter button. (If the heading wraps to two lines, right-click the row 6 header, choose Format Cells from the shortcut menu, click the Alignment tab, turn off Wrap Text, and click OK.) Then widen column A.

2. Select cell B6 and click the Paste Function button on the Standard toolbar. Excel displays the Paste Function dialog box shown on the facing page, and the Office Assistant may offer assistance.

   
   Click to view graphic

   Below the list boxes, the dialog box displays the syntax of the function selected in the Function Name list. The syntax tells you how the function must be entered after the = sign in the formula bar. You will replace the placeholders between parentheses in the syntax (in this case, number1,number2,...) with the actual values you want Excel to use.

3. If necessary, click No in the Office Assistant's box. Then select AVERAGE in the Function Name list and click OK. Excel displays this formula palette:

   
   Click to view graphic

   The palette displays a definition of the function and its arguments. In the Number1 edit box, you can enter a number, cell reference, name, formula, or another function.

4. Select E14:E25 in the worksheet to add its name, Amount, as the formula's argument. As you begin your selection, the palette collapses, and when you release the mouse button, the palette expands again. Excel displays =AVERAGE(Amount) in the formula bar and the result of the formula at the bottom of the palette, which is shown below.

   
   Click to view graphic

5. Click OK to enter the formula in cell B6. Press Ctrl+Home to see the results, $1,400.35, displayed in cell B6.

Identifying Highest and Lowest Values

Excel provides two functions that instantly identify the highest and lowest values in a group. To understand the benefits of these functions, imagine that the 1st Quarter worksheet contains data from not 12 but 112 customers! Let's start with the highest sale:

1. Select cell A7, type Highest Sale, press the Right Arrow key to confirm the entry and to select cell B7.

2. Click the Edit Formula button (the = at the left end of the formula bar). Excel enters = in the formula bar and replaces the name box to the left of the formula bar with the function name box.

3. Click the arrow to the right of the function name box. Then select MAX (for maximum) from the drop-down list. (If the function you want is already displayed, you can simply click it.) Excel then displays the formula palette shown here:

   
   Click to view graphic

4. Select E14:E25 on the worksheet. The formula bar displays =MAX(Amount), and Excel displays the result of the formula at the bottom of the palette.

5. Click OK to close the formula palette. Excel enters the highest sale amount, $2,643.90, in cell B7.

To determine the lowest sale, you'll use Excel's AutoCalculate feature:

1. Select cell A8, type Lowest Sale, and press Enter.

2. Select E14:E25 and notice that Excel has entered the sum of the values in the range in the AutoCalculate area of the status bar at the bottom of the window.

3. Right-click the AutoCalculate area and select Min from the shortcut menu. Excel displays the result, $345.00, in the AutoCalculate area.

4. Now that you know the result, select cell B8, type 345, and press Enter.

   You can also use AutoCalculate to quickly apply other functions, such as AVERAGE and COUNT, to a selected range.

Calculating with Names

The salespeople at the Cream of the Crop ice cream company all earn commission. As a gross indicator of sales expenses, you can use the Total Sales value in cell B5 to calculate the total sales commission. Here's how:

1. In cell A9, type Commission and press Tab.

2. Type 6% and click the Enter button.

3. With cell B9 still active, choose Name and then Define from the Insert menu. Excel scans the adjacent cells and suggests the name Commission. Click OK.

   Now you'll use the commission percentage in a formula that will calculate the total commission:

4. Select cell A10, type Sales Expense, and press Tab.

5. With cell B10 selected, type the formula =Total*Commission and press Enter. Excel multiplies the value in the cell named Total (B5) by the value in the cell named Commission (B9) and displays the result, $1,008.25, in cell B10.

6. Now select cell B9, type 5%, and press Enter. Instantly, the value in cell B10 changes to reflect the new commission rate, as shown here:

   
   Click to view graphic

   If a hundred calculations throughout the worksheet referenced the cell named Commission, Excel would adjust all their results to reflect this one change. Powerful stuff!

Formulas That Make Decisions

There will be times when you want Excel to carry out one task under certain circumstances and another task if those circumstances don't apply. To give this kind of instruction to Excel, you use the IF function.

displays Yes if either test is true.

In its simplest form, the IF function tests the value of a cell and does one thing if the test is positive (true) and another if the test is negative (false). It requires three arguments: the test, the action to perform if the test is true, and the action to perform if the test is false. You supply the arguments one after the other within the function's parentheses, separating them with commas (no spaces). Try this:

1. Select cell D5, type the following, and then press Enter:

   =IF(B5=0,"TRUE","FALSE")

   Excel checks whether the value in cell B5 is zero (the test). Because it isn't zero, Excel ignores TRUE (the action to perform if the test is true) and in cell D5 displays FALSE (the action to perform if the test is false).

2. Double-click cell D5. (Notice that Excel changes the B5 reference to blue and puts a matching blue border around cell B5—see the tip below for more information.) Drag through =0 to highlight it, type<100000, and press Enter. The entry in cell D5 instantly changes from FALSE to TRUE, because the value in cell B5 is less than one hundred thousand; that is, the test is true.

3. Now select cell D5 and press Delete to clear the cell.

In this example, the test Excel performed was a simple evaluation of the value in a cell. However, you can also build tests that involve other functions. Recall that the last two characters of the customer numbers in column C of the worksheet indicate whether the sale was made to a large chain store or to an individually owned store. Suppose you want to assign each customer number to a Chain or Individual category so that you can compare the sales for the two store types. Follow these steps:

1. Select cell D13, enter the heading Type, and press Enter.

2. In cell D14, type the following and click the Enter button:

   =IF(RIGHT(C14,2)="AA","Chain","Individual")

You have told Excel to look at the two characters at the right end of the value in cell C14 and if they are AA, to enter Chain in cell D14. If they are not AA, Excel is to enter Individual. The result is shown here:

Click to view graphic

Copying Formulas

The IF function you just entered is arduous to type, even for good typists. Fortunately, you don't have to enter it more than once. By using a simple mouse operation called AutoFill, you can copy the formula into the cells below, like this:

1. With D14 selected, position the pointer over the tiny square, called the fill handle, in the bottom right corner of the cell.

2. When the pointer changes to a black cross, hold down the left mouse button and drag down to cell D25. When you release the mouse button, Excel copies the formula from D14 into the highlighted cells. Here are the results:

   
   Click to view graphic

3. Select cell D15 and look at the formula in the formula bar. Excel has changed the original formula

   =IF(RIGHT(C14,2)="AA","Chain","Individual")

   to

   =IF(RIGHT(C15,2)="AA","Chain","Individual")

When you used AutoFill, Excel changed the reference so that it refers to cell C15 as its argument, not C14. Why?

By default, Excel uses relative references in its formulas. Relative references refer to cells by their position in relation to the cell containing the formula. So when you copied the formula in cell D14 to cell D15, Excel changed the reference from C14 to C15—the cell in the same row and one column to the left of the cell containing the formula. If you were to copy the formula in cell D14 to F14, Excel would change the reference from C14 to E14 so that the formula would continue to reference the cell in the same relative position.

When you don't want a reference to be copied as a relative reference, as it was in these examples, you need to use an absolute reference. Absolute references refer to cells by their fixed position in the worksheet. To make a reference absolute, you add dollar signs before its column letter and row number. For example, to change the reference C4:C9 to an absolute reference, you would enter it as $C$4:$C$9. You could then copy a formula that contained this reference anywhere on the worksheet and it would always refer to the range C4:C9.

References can also be partially relative and partially absolute. For example, $C4 has an absolute column reference and a relative row reference, and C$4 has a relative column reference and an absolute row reference.

Linking Worksheets with Formulas

Earlier you transferred the results of a SUM function to a different cell simply by referencing the cell containing the function (see page 62). You can just as easily transfer a function's results from one worksheet to another, thereby linking the two worksheets so that any changes in the source worksheet are immediately reflected in the linked worksheet. In this section, you'll set up the four quarter worksheets first so that they contain different sets of invoice data, and then you'll use the Totals worksheet to build some summary information. Let's get going:

1. Hold down the Shift key, click the 1st Quarter tab to ungroup the quarter worksheets, and then click the 2nd Quarter tab.

2. Select cell A14, type 4/6/99, press Enter, type 4/13/99, and press Enter again.

3. Select A14:A15 and drag the fill handle down to cell A25. Based on the interval between the dates in the two selected cells, Excel calculates what the entries in each cell you select should be, and when you release the mouse button, Excel fills the selected range with a set of dates one week apart.

4. Next select cell E14, increment its entry by $100, and press Enter. Repeat this step for the entries in E15:E25.

5. Repeat steps 2, 3, and 4 for the 3rd Quarter worksheet, using starting dates of 7/5/99 and 7/12/99 and incrementing the sales amounts by $200. Then do the same for the 4th Quarter worksheet with 10/7/99 and 10/14/99 and $300.

results in an error—unless the range name Excel happens to be assigned to a cell or range in the worksheet.

Why haven't the values in the calculation area changed? The formulas in B5:B10 reference the Amount and Total names, which you applied to cells on the 1st Quarter sheet. Follow these steps to quickly create new names:

1. Choose Name and then Define from the Insert menu, select Amount, change the name to Amount 1, and click Add.

2. Change the Names In Workbook entry to Amount 2, change ='1st in the Refers To entry to ='2nd, and click Add.

3. Repeat step 2 to create an Amount 3 name that refers to the 3rd Quarter sheet and an Amount 4 name that refers to the 4th Quarter sheet.

4. Repeat steps 2 and 3 to create Total names for the four sheets.

5. Delete the original Amount and Total names and click OK.

6. Back in the workbook, replace the names in all the formulas on the quarter sheets with the correct names for the active worksheet. (For example, on the 1st Quarter sheet, replace Amount with Amount 1 and Total with Total 1.)

Now that the quarter sheets contain different sets of data, you can turn your attention to the Totals worksheet:

1. Activate the Totals worksheet, press Ctrl+Home, and enter Annual Summary, 1999 in cell A1. Then make this title bold and left-aligned, and change its size to 22.

2. In cell B3, type 1st Quarter, click the Enter button, and drag the fill handle to cell E3. The AutoFill feature increments the number in the selection and enters the headings 2nd Quarter, 3rd Quarter, and 4th Quarter.

3. Enter Total in cell F3, Sales in cell A4, Sales Expense in cell A5, and Gross Profit in cell A7.

4. Make the headings in row 3 and column A bold, and then select columns A through F and change their widths to 15.

5. Select B4:F7 and format the range as currency.

1. Select cell B4, type =, activate the 1st Quarter sheet, click cell B5, and press Tab.

2. Repeat step 1 to link cell C4 on the Totals sheet with cell B5 on the 2nd Quarter sheet, cell D4 on the Totals sheet with cell B5 on the 3rd Quarter sheet, and cell E4 on the Totals sheet with cell B5 on the 4th Quarter sheet.

3. In cell F4 on the Totals sheet, click the AutoSum button, and click the Enter button.

4. Now link the Sales Expense formulas in cell B10 on the four quarter sheets to cells B5:E5 on the Totals sheet, and use the AutoSum button to obtain the total sales expense for the year.

5. Enter the gross profit on the Totals sheet by selecting cell B7, entering =B4-B5, and using AutoFill to copy the formula to C7:F7. Here are the results:

   
   Click to view graphic

Now test the links by following these steps:

1. Suppose the summer months were unusually hot, resulting in record sales for the third quarter. Activate the 3rd Quarter worksheet and increment all the sales amounts by $1,000.

2. Return to the Totals worksheet and compare the new totals with those in the preceding graphic. The linking formulas have done their jobs and faithfully updated the Totals worksheet to reflect the new information in the source worksheets.