Home

pivot table average summary returns error

%3CLINGO-SUB%20id%3D%22lingo-sub-841898%22%20slang%3D%22en-US%22%3Epivot%20table%20average%20summary%20returns%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-841898%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20creating%20a%20very%20simple%20analysis%20of%20attendance%20at%20my%20membership%20organisation%20meetings.%20We%20have%20a%20meeting%20register%20in%20excel%20with%203%20columns%3A%3C%2FP%3E%3COL%3E%3CLI%3EMeetingDate%3C%2FLI%3E%3CLI%3EPerson%3C%2FLI%3E%3CLI%3ECategory%20(member%20or%20visitor)%3C%2FLI%3E%3C%2FOL%3E%3CP%3ESo%20I%20create%20a%20simple%20crosstab%2C%20with%20meetingdate%20for%20the%20rows%20and%20Category%20for%20the%20columns%2C%20and%20count(person)%20for%20the%20values%3C%2FP%3E%3CP%3EThe%20data%20is%20fine%20(see%20screenshot)%20%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20738px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130555i07AF64AF19F5B72C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%227F032D15-F224-4651-86E7-A4D0F706FCE1.png%22%20title%3D%227F032D15-F224-4651-86E7-A4D0F706FCE1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eexcept%20the%20column%20totals%20are%20slightly%20meaningless.%20What%20would%20be%20much%20better%20is%20to%20have%20column%20averages.%3C%2FP%3E%3CP%3EWhen%20I%20choose%20%22Summarize%20by%20average%22%20in%20the%20context%20menu%2C%20the%20entire%20crosstab%20shows%20%23DIV%2F0!%20values.%3C%2FP%3E%3CP%3EAny%20suggestions%20as%20to%20why%3F%20And%20as%20to%20how%20I%20can%20show%20average%20instead%20of%20sum%20in%20the%20bottom%20row%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-841898%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-841939%22%20slang%3D%22en-US%22%3ERe%3A%20pivot%20table%20average%20summary%20returns%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-841939%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F404960%22%20target%3D%22_blank%22%3E%40Feargal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20because%20the%20column%20Person%20has%20the%20text%20entries%20which%20can%20be%20counted%20but%20not%20averaged.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-842454%22%20slang%3D%22en-US%22%3ERe%3A%20pivot%20table%20average%20summary%20returns%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842454%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3Bexcept%20that%20the%20total%20columns%2Frows%20do%20not%20total%20the%20names.%20They%20total%20the%20count.%20Effectively%20they%20say%20%3Dsum(count(person))%3C%2FP%3E%3CP%3Ewhat%20I%20would%20like%20them%20to%20say%20is%20%3Daverage(count(person))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20that%20not%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-842652%22%20slang%3D%22en-US%22%3ERe%3A%20pivot%20table%20average%20summary%20returns%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F404960%22%20target%3D%22_blank%22%3E%40Feargal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20inserting%20a%20helper%20column%20in%20your%20data%20and%20fill%20that%20column%20with%201%20and%20then%20drag%20that%20column%20into%20the%20Values%20area%20in%20the%20pivot%20table%20and%20change%20the%20Value%20Field%20Setting%20to%20show%20it%20as%20Avg%20and%20see%20if%20that%20resolved%20your%20purpose.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Feargal
Occasional Contributor

I'm creating a very simple analysis of attendance at my membership organisation meetings. We have a meeting register in excel with 3 columns:

  1. MeetingDate
  2. Person
  3. 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) 7F032D15-F224-4651-86E7-A4D0F706FCE1.png

 

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

@Feargal 

 

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?

 

@Feargal 

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
11 Replies