Excel – PivotTable average of count

microsoft excelpivot table

I need to create a PivotTable that will display the average of the count of rows for each item category/subcategory. To be specific, let's say I have the following list of fruit consumption:

Orange 12-Jan
Orange 12-Jan
Orange 13-Jan
Banana 12-Jan
Banana 13-Jan

I'd like to know the average number of each type of fruit that were eaten per day. In this case that would be 3 oranges / 2 days = 1.5 oranges / day and 2 bananas / 2 days = 1 banana / day. My file has about 1500 rows and about 10 types of fruit over some 100 days.

How could I get a PivotTable that looks something like

Orange 1.5
Banana 1
Apple  ...

and so on? I could live with a PivotTable that contained other additional fields if that was necessary.

I've been messing around with this for a while and I'm starting to get frustrated. I'd happily accept an embarrassingly trivial answer.

Thanks a lot.

Best Answer

No pivot table needed. Order by fruit type, use the Excel Subtotal functionality, and have it count by fruit type. You'll end up with a list like this:

      FRUIT    DATE
      Apple    12-Jan
      Apple    13-Jan
 Apple subtotal     2
      Orange   12-Jan
      Orange   13-Jan
      Orange   13-Jan
 Orange subtotal    3

Then simply divide each subtotal by the total number of days.

mtone's answer shows how to calculate total number of days.

Related Question