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
New 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