Grand Average in Pivot Table?

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

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


--

Regards,

Peo Sjoblom


"mikelee101" <mikelee101@discussions.microsoft.com> wrote in message
news:8BB0E89E-9BAC-44B7-9E82-EA1DDFCAFDFB@microsoft.com...
> 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,
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

"Peo Sjoblom" wrote:

> 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
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "mikelee101" <mikelee101@discussions.microsoft.com> wrote in message
> news:8BB0E89E-9BAC-44B7-9E82-EA1DDFCAFDFB@microsoft.com...
> > 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
>
>
>

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.

--
Regards

Roger Govier


"mikelee101" <mikelee101@discussions.microsoft.com> wrote in message
news:FC37F86D-744A-4CAA-9674-47AD23C32EB1@microsoft.com...
> 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
>
> "Peo Sjoblom" wrote:
>
>> 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
>>
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> "mikelee101" <mikelee101@discussions.microsoft.com> wrote in message
>> news:8BB0E89E-9BAC-44B7-9E82-EA1DDFCAFDFB@microsoft.com...
>> > 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
>>
>>
>>

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 wrote:
> 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.
>
> --
> Regards
>
> Roger Govier
>
>
> "mikelee101" <mikelee101@discussions.microsoft.com> wrote in message
> news:FC37F86D-744A-4CAA-9674-47AD23C32EB1@microsoft.com...
> > 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
> >
> > "Peo Sjoblom" wrote:
> >
> >> 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
> >>
> >>
> >> --
> >>
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >>
> >> "mikelee101" <mikelee101@discussions.microsoft.com> wrote in message
> >> news:8BB0E89E-9BAC-44B7-9E82-EA1DDFCAFDFB@microsoft.com...
> >> > 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
> >>
> >>
> >>

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

--
Regards

Roger Govier


<mikelee101@hotmail.com> wrote in message
news:1137896997.895315.200660@o13g2000cwo.googlegroups.com...
> 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 wrote:
>> 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.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "mikelee101" <mikelee101@discussions.microsoft.com> wrote in message
>> news:FC37F86D-744A-4CAA-9674-47AD23C32EB1@microsoft.com...
>> > 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
>> >
>> > "Peo Sjoblom" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> --
>> >>
>> >> Regards,
>> >>
>> >> Peo Sjoblom
>> >>
>> >>
>> >> "mikelee101" <mikelee101@discussions.microsoft.com> wrote in
>> >> message
>> >> news:8BB0E89E-9BAC-44B7-9E82-EA1DDFCAFDFB@microsoft.com...
>> >> > 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
>> >>
>> >>
>> >>
>