Pivot Table Average summary returns error

0

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) 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 ?

image

Feargal Hogan

Posted 2019-09-04T09:32:33.897

Reputation: 1

Could you please [Edit] your post & add the formula/function you have been used so far! – Rajesh S – 2019-09-04T09:35:06.043

if looking help for PT then instead fo SUM apply AVERAGE. – Rajesh S – 2019-09-04T09:36:11.140

I don't know what PT is? – Feargal Hogan – 2019-09-04T09:46:05.863

The formula in the crosstab is count(person) as I said above – Feargal Hogan – 2019-09-04T09:46:30.883

You mean across Sheets? – Rajesh S – 2019-09-04T09:47:59.593

PT is Pivot Table (your Screen Shot). – Rajesh S – 2019-09-04T09:57:28.477

Your data does not have any VALUES, you cannot take an average of text stings, you need numerical data – PeterH – 2019-09-04T10:05:26.050

Answers

0

I think the issue here is that you don't have a column with number values to average, so the error is actually correct. You are not able to create an average for data that is not numeric. There is no average when you look at the actual data (person 1, person 2, etc.) The easiest way to get an average would be to just create your own formula... =AVERAGE(cell range)

See example here

Monica Long

Posted 2019-09-04T09:32:33.897

Reputation: 1

I must be missing something here. The count(person) figure is the number of attendees in each row. And the total columns and total rows are displaying sum(count(person) in each row/column). Why can't it display average(count(person) in each row/column)? – Feargal Hogan – 2019-09-04T13:34:02.723

Because you are not summing a string of values. you are counting text entries. You cannot create an average number from a string of text. you can count the number of text entries, but you cannot average them. – Monica Long – 2019-09-04T19:37:17.440

And the right hand total column is not a total of the text values. Its a total (sum) of the count values. So why cant that total be an average instead of a sum? – Feargal Hogan – 2019-09-06T21:07:07.853

Asked: 2019-09-04T09:32:33.897

Viewed: 47 times

Active: 2019-09-05T00:42:04.737