Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

westy - 07 Mar 2007 16:06 GMT

Firstly - I am a big fan of this site, have searched previous questions and
just cant find/understand my problem.
My question - I want to obtain two averages from pivot table data displayed
in cells to the left of pivot table. They are an Average for the entire year
(Avg YTD B6:B8) and an Average for one season (Avg Aut C6:C8) against each
Name(A6:A8) from the Pivot table(D3:J17). The PT has two Field columns,
Season(E3) with two items Aut(E4) and Sum(H4) and Game(F3) with 6 items,
Games 1 - 3 for both seasons(E5:J5). Example Below

A B C D E F G    H
I J
3    Runs Season Game
4    Aut Sum
5 Name Avg YTD Avg Aut Name 3 2 1 3 2 1
6 Kym    ??    ?? Kym -6 5 4 8 6 7
7 Jimmy    ??    ?? Jimmy 13 31 5 1 11 5
8 Craig    ??    ?? Craig 15 2 11 8 19 17

So I want to display the average for the entire year (both seasons - Aut &
Sum) in Avg YTD and only for one season in Avg Aut. Also it needs to still
capture the averages as the PT updates and grows larger when the PT cells
expand to the left. More games will be added to the current season which in
this example will be Aut.

I have chosen to place the formulas outside and to the left of PT for
formatting reasons. I am happy to have them inside PT if they can be
displayed to the left. Also I want to maintain PT data and layout as in
example. Using 2003 version.

I hope I have explained this clearly and look forward to the always talented
response.

Reply to this Message

Debra Dalgleish - 07 Mar 2007 19:37 GMT

This will create an average for each season, and an overall average:

Double-click on the Season field button, and for Subtotals, choose
Average, then click OK
In the source data, add a column with the heading YTD, and leave all the
cells blank in that column.
Refresh the pivot table, and add the YTD field to the column area,
before Season
Double-click on the YTD field button, and for Subtotals, choose Average,
then click OK
Select the YTD cell that says (Blank), and type a couple of space
characters, then press the Enter key.

> Firstly - I am a big fan of this site, have searched previous questions and
> just cant find/understand my problem.
[quoted text clipped - 27 lines]
> I hope I have explained this clearly and look forward to the always talented
> response.

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

westy - 07 Mar 2007 23:08 GMT

This result changed the layout of PT and placed the subtotals on the right of
data. Also I do not want the subtotal of both seasons, only one season and
YTD.
The resulting layout should be

Name,YTD AV, Season AV, Pivot data (Game and Season)

So is there any way to have subtotals on left. I want to keep the layout of
pivot and Avgs as per example for formatting/printing reasons.

> This will create an average for each season, and an overall average:
>
[quoted text clipped - 40 lines]
> > I hope I have explained this clearly and look forward to the always talented
> > response.

Reply to this Message

Debra Dalgleish - 08 Mar 2007 00:46 GMT

The subtotals can't be moved to the left of the items, and to extract
the data, e.g. Avg YTD, from the pivot table with a GetPivotData
formula, the values must be visible in the pivot table.

You could make a copy of your pivot table on another worksheet, with a
setup as I described.
Then, in the cells to the left of you main pivot table, use GetPivotData
formulas to extract the required averages from the second pivot table.

For example, with the second pivot table on Sheet2, and the name Kym in
cell A6, enter this formula in cell B6:

=GETPIVOTDATA(Sheet2!$A$4,$A6 & " YTD[' ';Average]")

Put the season name, Aut, in cell C4
In cell C6, enter the formula:

=GETPIVOTDATA(Sheet2!$A$4,$A6 &" Season[" &C$4 &";Average]")

> This result changed the layout of PT and placed the subtotals on the right of
> data. Also I do not want the subtotal of both seasons, only one season and
[quoted text clipped - 50 lines]
>>>I hope I have explained this clearly and look forward to the always talented
>>>response.

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

westy - 10 Mar 2007 00:08 GMT

Am having some trouble with this, have no problem with pivot change you have
suggested however what is the exact range or cell that I am putting the PT
into on in Sheet two. The formuals return errors, I'm not sure if pivot cells
is where pivot is meant to be pasted according to your formula ie what should
be in Sheet 2 A4

Also I tried the get pivot function on my existing layout - just entering =
and then clicking the average subtotal cell - the resulting formula was an
error.

> The subtotals can't be moved to the left of the items, and to extract
> the data, e.g. Avg YTD, from the pivot table with a GetPivotData
[quoted text clipped - 69 lines]
> >>>I hope I have explained this clearly and look forward to the always talented
> >>>response.

Reply to this Message

Debra Dalgleish - 10 Mar 2007 01:31 GMT

You're welcome.
The sample formula was based on a pivot table that started in cell A2,
and had a heading in cell A4.
There's a problem with the automatically generated GetPivotData formula
if you're using custom subtotals. It creates a formula like this:
=GETPIVOTDATA($A$4,"Kym YTD[' ';Data,Average]")
and you have to remove the Data, portion, to get this:

=GETPIVOTDATA($A$4,"Kym YTD[' ';Average]")

> Am having some trouble with this, have no problem with pivot change you have
> suggested however what is the exact range or cell that I am putting the PT
[quoted text clipped - 79 lines]
>>>>>I hope I have explained this clearly and look forward to the always talented
>>>>>response.

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message