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:
Then simply divide each subtotal by the total number of days.
mtone's answer shows how to calculate total number of days.