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.

Search JabSto ::

Custom Search