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.
Search JabSto ::

Custom Search