I have a pivot table with one column that contains average percentage values.
The grandtotal shows "29.62" but I don't know how this is calculated as it's not the average of the sum of that column otherwise it would be "29.58" (591.52 divided by 20)?
Average % value
29.29
29.04
28.81
29.01
29.23
29.09
29.18
29.30
29.17
29.01
29.58
29.89
29.56
29.50
29.43
30.30
31.22
31.11
30.01
29.79
29.62
Hi,
Could it be the 29.62 is the average of all the values that comprised the 20 numbers above?
See the simple example to see what I mean. Each row is the average for the row label but the total average is for all the numbers that are considered from the table. Look at the example and do your example above on it.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Makes sense. Thank you!
Sorry to reopen the thread but I have just been told that I actually need the averages of only the data that is being displayed in the pivot as opposed to the averages of all the values behind the scene.
How can I do this if my data is dynamic as depending on what user select in the report filters, the list of numbers will change so I can't just simply use =AVERAGE(E8:E27).
I'd go back and make sure that is what they really mean. Ask them if they want the Average of Averages. Then do a few both ways and show them the answer is so close (or so different) they don't know what they mean.
If they don't agree then start some concern that taking the average in the first place is a problem. Tell them they should really do Z-Scores or Stutent T scores to rank each on a Bell Shaped curve. This is where an average score is 50 and 10 is the standard deviation. This is a much beter statistic than Averages in the first place.
I agree in checking that they *really* want an average of averages. That can really throw off the true average of your original data as all of the weighting is thrown out the window. This would be especially evident in data sets that contain very wide ranges of values.
That's what the example off Marvin P does.
See the example.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
I can see now that the datasource spreadsheet contains a large number of duplicates. Otherwise, how can I make my Pivot table omit all duplicates so that I can use the value in my Grand Total of my average column?
Please make an example with the disered solution.
A very small sample attached. I need only the Avg of what's being displayed and not of every applicable values from the data source.
My real data source will be over 1000 rows in length and on a weekly basic the data source tab will be cleared and new data is pasted in.
Because of this, I can not define a set range so I need the function =Ave() to cater for a dynamic pivot table.
I hope this makes sense.
Hi,
If you move the pivot table down, turn off the totals and put the averages above the Pivot you can do any size of rows. see attached for the answer.
So simple! Thank you Marvin!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks