pivot table and grand average

[RichardO]

Hi,

Does anyone know how to calculate the grand average in a pivot table?
Just as a pivot table gives the grand total, can a pivot table als
calculate the grand average?

Thanks.

RichardO

 

[Peo Sjoblom]

Yes, right click in the data field and select field settings, that will give
you options to choose
sum, count, average, max, min etc.. Or when you do the layout double click
the data button

 

[RichardO]

Hi,

I tried that already, but that gives the average of each item in th
data field. I was wondering if excel can calculate a grand averag
just as it calculates a grand total. For example

Sum of items Qtr
1 2 3 4
Total Grand Total
ID 1 30 40 30 20 120
120
2 10 5 10 10 3
35
3 20 40 20 40 120
120

So, from the above, the Grand Average for ID 1 = 120/4 = 30, If
choose Qtrs 2 & 3, the grand average= 70/2 = 35.

Whereas if I use the average function as you suggested, it averages th
ID items and then finds the Grand total. Say there are 6 ID 1 items
then under Qtr 1, the pt would give 30/6=5, that's not what I a
looking for. Is there a way I could put a Grand average column.
Thanks.


Regards,
RichardO

 

[Peo Sjoblom]

The grand total you get with average is the average grand total.
To simplify, if you have numeric values in one column and
let's say you have IDs in another column, you put the numbers in data,
IDs in the row header
However it is hard to tell without knowing your source data..

 

[RichardO]

Thank you for your suggestion. I am repasting the table as it did not
come out well in the last post. Here is how my source data looks
like:


Sum of Price
Qtr
ID 1 2 3 4 Ttl Grand Total
1 30 40 30 20 120 120
2 10 5 10 10 35 35
3 20 40 20 40 120 120

I just need Excel to calculate the Grand Average i.e. Grand Total/# of
Qtrs displayed. Is this possible in Excel at all? As you rightly
mentioned, ID 1 with 6 items will have an average of 30/6=5, whereas I
need the average of all the sum i.e. 120/# of Qtrs, in this case 4 i.e.
120/4=30 for ID1. If Qtr 3 &4 are selected, Grand Average should be:
(30+20)/4 = 12.5.

It seems easy, but I have not been able to find a way of doing it.


Regards,
RichardO.