Objective 1. Use SUM, AVERAGE, MIN, and MAX Functions

1.

Start Excel and Close the Getting Started task pane. From the File menu, display the Save As dialog box, click the Save in arrow, and then navigate to the location where you are storing your projects. Click the Create New Folder button and name the folder Excel Chapter 3 and then press to make your new folder the active folder. In the File name box, type 3A_Geography_Lecture_Firstname_Lastname and then click Save or press .

2.

In cell A1, type Average Rainfall Amounts in Buffalo and Seattle and in cell A2, type Measured in Inches of Rainfall per Month

3.

In cell A4 type Month In cell A5, type Jan and drag the fill handle down through cell A16 to have AutoFill complete the months of the year. Then, in the range B4:C16, enter the remaining column titles and data as shown below. Compare your screen with Figure 3.2.

Figure 3.2.

(This item is displayed on page 789 in the print version)

4.

In cell A17, type Sum and in cell A18, type Average Click cell B17, click AutoSum , and then compare your screen with Figure 3.3.

Figure 3.3.

The SUM function that you have used is a predefined formula that adds all the numbers in a selected range of cells. Recall that because the SUM function is so frequently used, it has a button on the Standard toolbar.

When you click the button, the SUM function is placed in the cell and the range of cells that Excel proposes to sum displays in parentheses. The values in parentheses are the argumentsthe values that an Excel function uses to perform calculations or operations.

5.

Press to display the result of the formula's calculation37.6in the cell.

6.

Click cell B18, and then on the Formula Bar, click the Insert Function button . Alternatively, from the Insert menu, click Function.

The = sign displays in the cell and the Insert Function dialog box displays.

7.

Click the Or select a category arrow, and then click Statistical. Under Select a function, click AVERAGE. Compare your screen with Figure 3.4.

Figure 3.4.

8.

In the displayed dialog box, click OK to display the Function Arguments dialog box.

The AVERAGE function is a formula that adds a group of values and then divides the result by the number of values in the group.

In the cell, the Formula Bar, and the dialog box, a range of cells to average is proposed. Excel first proposes the adjacent values above. Because you want to average the values in the range B5:B16and not include the column totalyou will need to edit the proposed range.

9.

In the Function Arguments dialog box, click in the Number1 box to display the insertion point, and then edit as necessary to display B5:B16 Alternatively, click the Number1 Collapse Dialog Box button, select the range B5:B16, and then click the Expand Dialog Box button. Compare your screen with Figure 3.5.

Figure 3.5.

10.

In the Function Arguments dialog box, click OK.

You can see that in Buffalo, the total rainfall for the year is 37.6 inches and the average rainfall per month is 3.133333 inches.

11.

Select the range B17:B18, and then drag the fill handle in the lower right corner of the selected range to the right to fill C17:C18. Notice that the sum of rainfall and the average rainfall per month for both cities is almost the same.

Because the same functionsprebuilt formulasare needed to sum and average the rainfall for Seattle, you can copy these formulas with their relative cell references. You can see that based on these two statistics, both the total rainfall and the average rainfall for the two cities are similar. You will see when you chart the data, however, that the pattern of rainfall in the two cities is quite different.

12.

Save your workbook.

1.

In cell A19, type Minimum and in cell A20, type Maximum

2.

Click cell B19. On the Formula Bar, click the Insert Function button . In the Insert Function dialog box, click the Or select a category arrow, and then click Statistical. Under Select a function, type m to display the Statistical functions that begin with the letter M, and then scroll down and click MIN.

3.

In the displayed dialog box, click OK to display the Function Arguments dialog box; notice the proposed range indicated in the Number1 box.

4.

If necessary, move the Function Arguments dialog box so that you can view column Bpoint to the dialog box's blue title bar and drag it to the right of the screen. Then, select the range B5:B16. Alternatively, type the range in the Number1 box or click the Collapse Dialog Box button, select the range, and then click the Expand Dialog Box button. Compare your screen with Figure 3.6.

Figure 3.6.

5.

In the displayed dialog box, click OK.

The smallest value in the range2.4displays in the cell.

6.

With cell B19 selected, drag the fill handle to the right to cell C19.

The minimum rainfall for Buffalo2.4is much higher than the minimum for Seattle0.7.

7.

Use the skills you just practiced to place the MAX function in cell B20 to find the maximum value in the range B5:B16 and then fill it into cell C20. Compare your screen with Figure 3.7.

Figure 3.7.

The maximum rainfall for Buffalo4.2is clearly lower than the maximum rainfall for Seattle6.3. The minimum and maximum monthly rainfalls for Buffalo are not very different from the average for Buffalo, but the minimum and maximum vary much more from the average for Seattle. Professor Danielson will use this example to show the importance of using more statistical functions than just SUM and AVERAGE.

More Knowledge: The MEDIAN Statistical Function

The MEDIAN statistical function is commonly used to describe a group of datayou have likely seen it used to describe the price of houses in a particular geographical area. The MEDIAN function finds the middle value that has as many values above it in the group as are below it. It differs from AVERAGE in that the result is not affected as much by a single value that is greatly different from the others.

8.

Save the changes you have made to your workbook.

1.

Select the range A4:C20. Point to the right edge of the selected range to display the pointer, and then compare your screen with Figure 3.8.

Figure 3.8.

2.

Drag the selected range to the right until the pointer is in column E and then release the mouse button. Click cell D17, and then look at the Formula Bar; notice that the arguments of the SUM function now refer to column D, as shown in Figure 3.9.

Figure 3.9.

3.

Select the range C17:E20, and then drag the range down one row to place the Maximum row on row 21.

The four rows of statistics and their titles are separated from the rows of data by an empty row.

4.

Click cell D18. On the Formula Bar, notice that the arguments of the SUM function still refers to the range D5:D16 even though the formula has been moved down one row.

5.

Select the range A1:G1, and then click the Merge and Center button . Change the font to Arial Black, change the font size to 12, and then change the Fill Color to Light Yellow. Select the range A2:G2, Merge and Center the text, change the font to Arial Black, be sure the font size is 10, and then change the Fill Color to Light Yellow.

6.

Select the range D5:E16, hold down , and then select the range D18:E21. Alternatively, in the Name Box, type d5:e16,d18:e21 and then press to select the nonadjacent ranges.

7.

From the Format menu, display the Format Cells dialog box, click the Number tab, and then under Category, click Number. Click the Decimal places spin box down arrow to change the number of decimal places to 1, and then click OK.

8.

Select the nonadjacent ranges C4:E4 and C5:C16, apply Bold emphasis and Center the values in their cells.

9.

Select the range C18:E21. On the Formatting toolbar, click the Borders button arrow , and then from the displayed palette, in the third row click the fourth borderThick Box Border. With the range still selected, change the Fill Color to Gray 25%. Click outside the selected area to deselect, and then compare your screen with Figure 3.10.

Figure 3.10.

10.

Rename Sheet1 as Rainfall Select Sheet2 and Sheet3 and delete them. Save your workbook.

[Page 796 (continued)]

Objective 2 Use a Chart to Make Comparisons