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 use the AVERAGEIF function and create these

formulas:

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

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

Using Excel’s PivotTable feature

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.

The Create PivotTable dialog box appears.

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

Excel displays the PivotTable Fields task pane.

4.
Drag the Gender item to the Rows section, at the bottom.

5.
Drag the Score item to the Values section.

Excel creates the pivot table but calculates the sum of the scores 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 78-3 shows the pivot table and the PivotTable Fields task pane.