Pivot table question regarding calculations

[Sayth]

I have a question about pivotables.

In essence when I add a field to the pivot table it gives me a new field "sum of field" or "average of field" etc.

It doesn't appear I can reference these values though. If I can I want to know how. I was thrying to find out the individual variation of each persons score from the average.

I have attached a sample sheet to assist with understanding of my question if needed.

http://www.mediafire.com/file/a8ujn2iof7gvb8b/ExamplePivot.xlsx

 

[pedie]

Drag those field out of the box if you dont want those to show in pivot table...

 

[Norie]

Sayth

Where/how do you want to reference the results of these calculations?

 

[Sayth]

I have 2 projects that I will need to do similar calculations on. In the sheet attached what I am trying to determine is:

1. What is the average of the group (in the sheet thats group1, group2 etc)
2. Difference between each member of the group and the average (in the sheet I have simply minused there value from the average.)
3. Rank the values in each group in order of best to worst.

The sheet I have posted is the basic version. WHAT I am notiving is that with th pivot table seems to work down columns. alot of my sums and queiries will be across rows. This will be similar iny work project as well. Will want to know how ach member peforms in target per prduct(motor, home, landlord etc) but wuld also want the sum of all products to see how the perform as a whole. So seeing how tey track as a whole but identifying potential opportunitiesfor improvement in keuy criteria

But I guess thats further down the track. In the sheet the pivot helpfully works out the average of each group for me but I can't seem to reference the group average in calculations to have each members score minus the average posted in a column.

 

[Norie]

Isn't a lot of that information already in the pivot table?

If you want sums/counts/whatever as well as averages try adding fields more than once.

eg add a field and set it for average, add the field again but set it for sum etc

If you want to extract data from the pivot table you could use GETPIVOTDATA, but with that you might end up repeating a lot of data.

For example I used it to get the results you indicated in the sample workbook and had to create a list for the horse names to use with GETPIVOTDATA.

You might want to look at working with the original data.

Pivot tables are very useful but can become complicated the more complex the calculations become.

Some might not even be possible using a pivot table.

 

[Sayth]

Well the basics is in the pivotable. The pivotable holds promise I just need to be able to extract some basic calculations.

I was looking down this path to avoid a complex macro using the advnaced filter on the data relates to this thread http://www.mrexcel.com/forum/showthread.php?t=489962

I guess my problem could be solved if the value averages could be used in a new calclulated field. But when I start into the calculated field it shows on pre exisitng columns not ones that are the result of the pivotable. So Sum of and Average of columns.

Is there a way to reference these columns?

 

[Norie]

Sayth

I looked into calculated fields, a topic I've never been much good with, but couldn't find anything.

I also looked into Value Field Settings... where you have the option of Show Values As.

I've used that in the past it's always been a bit hit and miss though.

I tried with the example workbook but couldn't come up with anything, though I only tried a few options.

Like I said you could try GETPIVOTDATA, here's how I did it.

Formula L5 : GETPIVOTDATA("average of career",A4,"number", 1)

Formula L6 : =GETPIVOTDATA("average of career", A4,"horse", K6 )-$L$5

Values K6:K12 : horse's names

This was the result which appears to match yours.

<TABLE style="WIDTH: 150pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=200><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 88pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=117></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=83 align=right>269474.5714</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>DAYTONA BEACH</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>-$103,244.57</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>ENZEDEX EAGLE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>$71,735.43</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>GOD'S HAND</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>$20,405.43</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Hidden Wonder</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>$123,797.43</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>STREETFIGHTER</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>$55,815.43</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>SUPERIOR INSTINCT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>-$140,814.57</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>TRIPLE ELEGANCE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>-$27,694.57</TD></TR></TBODY></TABLE>

Obviously having to repeat the names isn't ideal, I'm going to look further into the other options.

 

[Sayth]

Thanks, I had manually calculated values as you did above. Couldn't use absolute values in pivotable so as you said had to manually enter each name I couldn't copy down.