Average in Pivot Tables

[enders]

Hi,

I have created a table with the following data
student id, exam name, score.

I want to create the following pivot table

| Exam name 1 | Exam name 2
---------------------------------------
student id1 | Score | Score
student id2 | Score | Score
student id3 | Score | Score
---------------------------------------
Average Average

Excel will only let me give the Grand total.
Is it possible to get the average.
The sum of the score is meaningless.

With regards

Constantijn Enders

 

[Roger Govier]

If you go to Field settings for your data fields and change Sum to Average
you will get the overall average at the foot of your table.
Clearly with a sample of 1, for any individual student the Sum and Average
will be the same for each exam result, but the Grand Total will be average
rather than a Total

 

[Debra Dalgleish]

You could move Exam Name to the first field in the row area.
Then, double-click on its field button, and choose Average for the
Subtotals.

 

[Guest]

Hi,

I've been having the same problem, but using the Pivot table view of a form
in Access. I was wondering whose answer you used and whether it solved your
problem. I can't seem to find a place under the field properties of the grand
total to change Sum to Average.

Thanks

 

[Jessica C]

What about if the source is an OLAP cube? We are having the same issue...

 

[Guest]

Access pivot tables are nifty, but Microsoft didn't make it easy to get
started with them.

First, make sure you've clicked on the "Show details" icon. (If "Hide
details" is clicked, the Sigma icon and the Pivot tables/Autocalc option will
be greyed out. Frustrated the hell out of me until I discovered this.)

Then click the column you want to average. Click the Sigma icon, or choose
Pivot table/Autocalc, and then choose Average. In Show details view, this
will add a new "Average of {fieldname}" ROW. Click on "Hide details", and
Access will convert it to a column.

Then right click on the column, choose Properties, and you can change the
column heading and the format. You can then drag the Sum column off the
screen if it's meaningless.

I've got an article scheduled for the September issue of Access Advisor
which shows how to use Access pivot tables to provide flexible usere-defined
reporting. They're incredibly useful once you get your head wrapped around
how they work. (And I'm not selling anything here, guys!)

Kevin