Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 5 down vote accepted

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.

share|improve this answer
@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.

share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .