Forum Discussion
pivot table average summary returns error
I'm creating a very simple analysis of attendance at my membership organisation meetings. We have a meeting register in excel with 3 columns:
- MeetingDate
- Person
- Category (member or visitor)
So I create a simple crosstab, with meetingdate for the rows and Category for the columns, and count(person) for the values
The data is fine (see screenshot)
except the column totals are slightly meaningless. What would be much better is to have column averages.
When I choose "Summarize by average" in the context menu, the entire crosstab shows #DIV/0! values.
Any suggestions as to why? And as to how I can show average instead of sum in the bottom row ?
3 Replies
That's because the column Person has the text entries which can be counted but not averaged.
Subodh_Tiwari_sktneer except that the total columns/rows do not total the names. They total the count. Effectively they say =sum(count(person))
what I would like them to say is =average(count(person))
is that not possible?
You may try inserting a helper column in your data and fill that column with 1 and then drag that column into the Values area in the pivot table and change the Value Field Setting to show it as Avg and see if that resolved your purpose.
