0

My boss wants to have the average registrations for each countries for our courses. I have four fields in my table : "Course", "registration-ID", "Country", "year". In the pivot I created, I added "country" to rows, "year" to column and "Registration-ID" to values.

Here is an subset of the data, and a pivot table I created based on this subset: Data Pivot

I added my grand average column to the end. As we only have two years in this data subset, calculating the average is trivial, I only have to divide by two.

But as I want to create charts based on this, it would be much better if I could have that result directly in my pivot table. This way I can update the data and have my new results right away without any manual input.

If I right click on the pivot table and click on "Summarize value by>" then on "average", I get "#DIV/0!" errors everywhere on my pivot table. I guess it is linked to the fact that registration-ID is a text field.

Improve this question
3
  • Because you're average text value. I suggest you add auxiliary column in data source about Count.
    – Lee
    Commented Jan 9, 2020 at 9:48
  • Thanks for your answer Lee. I am not sure how to add a count to my dataset, and how it would help me? Could you please elaborate?
    – Ben Cor
    Commented Jan 10, 2020 at 9:57
  • Hey again Lee. I just saw this again, and now I understand completely what you meant. By adding a count in my dataset linked to each course, I can then do an average on the count, as this is a number. Thanks for your answer. I am not sure how I can accept your answer as we are several months after the fact. If a mod sees this, maybe thy can mark your answer as accepted?
    – Ben Cor
    Commented Jun 15, 2020 at 11:34

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .