# Excel - PivotTable average of count

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.

-

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.

-
@mtone: Agree. Automation is good, I went for quick-and-dirty :) The only downside to your method is that if a new fruit is introduced, you would have to set up the calculation for the new fruit. So that part of it isn't automated, where using the sort & subtotal functions will natively handle new fruit types. – DWong Feb 1 '10 at 23:49
Sorry, I had deleted my previous comment after re-reading and agreeing. – mtone Feb 2 '10 at 1:38
Thanks to both, you've provided very useful suggestions. – alexsome Feb 2 '10 at 13:56

PivotTables are not well suited for that sort of stuff. The fundamental problem is your final calculation: 3 oranges / 2 days. How in the world can Excel figure out that you want to divide by 2 days? You have to tell it somehow, and PivotTables can't really do such "vertical" comparisons.

I suggest you use regular formulas to achieve your goal.

``````A1 Fruit    B1 Date
2 Orange     01-12
3 Orange     01-12
4 Orange     01-13
5 Banana     01-12
6 Banana     01-13

A10: Daily Orange Intake
B10 (DaysElapsed): =MAX(B2:B6)-MIN(B2:B6) +1
C10 (OrangeConsumed): {=SUM((A2:A6="Orange")*1)}
D10 (DailyOrangeIntake): = E3/E2       -> Result: 1.5
``````

*C10 is an array formula to count how many oranges in your list (technique taken from cpearson article). CTRL+Enter to enter it as such.

You could automate the final results even further by having an array formula list all individual fruits on a separate columns (see Listing distinct elements in a list), and then copying the C10 formula above next to each cell. These series of formulas could update themselve nicely as you add new data to your spreadsheet. Basically just like a pivot table.

-
Or could be accomplished with SUMIF. The great part about this is the comment to not use a pivot table. – DaveParillo Feb 2 '10 at 0:13