Grand Average in Pivot Table?

[Guest]

Hello,
I'm pretty much a Pivot Table idiot, so I might be overlooking something
horribly obvious, but I'm trying to have a "Grand Average" column and row in
a Pivot Table instead of a "Grand Total." I see the Grand Total checkboxes
in the Options dialog box, but I've yet to stumble upon a way to specify a
function other than Sum.

Is this possible? If so, can someone let me know how?

Excel2000.

If I didn't explain that clearly enough, let me know and I can give an
example. Thanks for the help.

Mike

 

[Peo Sjoblom]

When you drag the number header into the data field it defaults to sum,
double click it and select average instead or if you already have sum in
your pivot, right click anywhere in the number column (Total column) and
select field settings, there you can also change to average

 

[Guest]

Peo,
That will change the function used to calculate the data within the table.
I want that to be a sum, but I want the "Grand Total" column to be an
average.
Right now, I have Month as the row field, Year as the column field, and Sum
of Milage as the Data Field. So it looks sort of like this:

2003 2004 2005 Grand Total
Jan 500 400 500 1400
Feb 400 300 500 1200
Grand Total 900 700 1000 2600

I want to continue to use the Sum function for calculating the data in the
table, but instead of summing rows and columns, I want to average the rows
and columns, so it would look like this:

2003 2004 2005 Grand Avg
Jan 500 400 500 466.67
Feb 400 300 500 400
Grand Avg 450 350 500 433.33

That way, the actual rows and columns don't change, but the summary values
change.

I hope that makes more sense.

Thanks again.

Mike

 

[Roger Govier]

Hi Mike

I'm assuming that you have more than one entry for each month in your
source table.
If you don't but have just the one value for each month, then selecting
Average in place of Sum as Peo suggests will work, because one entry of
500 is the same as 500/1.

If you have a series of mileages for each month, which are being summed
by the PT to give the total for each month, I think the only way you
will get what you want is to drag mileage to the data area a second
time, and make the second occurrence Average.
On the PT, drag the Data button tot the Total column and you will see
the values side by side, with totals and averages both appearing in the
Grand Totals.
If you want the months total and the average for the month to be shown
in the column next to it, drag the Year button to the left of the Data
button.

 

[mikelee101]

Roger,
Sorry for the late reply, the microsoft groups have been showing
"Temporarily Unavailable" since my last post. I finally realized I
could get to it through Google.

And your first solution was what I finally did. I do have multiple
entries per month, but what I ended up doing was building a second
table that gives me a total (i.e. one mileage entry per month), then I
based the pivot table off of that table and used Average of Mileage
instead of Sum of Mileage as the data operation.

Thanks very much for the help.

Mike

 

[Roger Govier]

Hi Mike
Thanks for taking the time to provide the feedback. Glad you got it
sorted out.