Microsoft Office Tutorials and References

In Depth Information

**Using formulas**

Using formulas

Another method of creating averages is to use formulas. The formula to calculate the average of

all students is simple:

=AVERAGE(B2:B27)

To calculate the average of the genders, you can take advantage of the AVERAGEIF function to

create these formulas:

=AVERAGEIF(C2:C27,”Female”,B2:B27)

=AVERAGEIF(C2:C27,”Male”,B2:B27)

The AVERAGEIF function was introduced in Excel 2007. If you need your workbook to be

compatible with earlier versions, use these formulas:

=SUMIF(C2:C27,”Female”,B2:B27)/COUNTIF(C2:C27,”Female”)

=SUMIF(C2:C27,”Male”,B2:B27)/COUNTIF(C2:C27,”Male”)

Using a pivot table

A third method of averaging the scores is to create a pivot table. Many users avoid creating pivot

tables because they consider this feature too complicated. As you can see, it’s simple to use:

1.
Select any cell in the data range, and choose Insert

Tables

PivotTable, to display the

➜

➜

Create PivotTable dialog box.

2.
In the dialog box, verify that Excel selected the correct data range and specify a cell on

the existing worksheet as the location.

Cell E1 is a good choice.

3.
Click OK, and Excel displays the PivotTable Field List.

4.
In the PivotTable Field List, drag the Gender item to the Row Labels section, at the

bottom.

5.
Drag the Score item to the Values section.

Excel creates the pivot table but displays the SUM function rather than the average.

6.
To change the summary function that’s used, right-click any of the values in the pivot

table and choose Summarize Data By

Average from the shortcut menu.

➜

Figure 103-3 shows the pivot table.