0

I have a question which I just can't figure out. I have a pivot table and want to create a weighted average from that data. I have researched and people suggest to do this by using a calculated field. However, my data is structured differently, I have one measure per column. This is what my data looks like:

City        Product Measure Amount
Miami       Apple   Price   $4.0
Miami       Apple   Rating  50.0%
Miami       Pear    Price   $3.00
Miami       Pear    Rating  15.0%
Miami       Banana  Price   $13.00
Miami       Banana  Rating  85.0%
New York    Apple   Price   $2.00
New York    Apple   Rating  75.0%
New York    Pear    Price   $11.00
New York    Pear    Rating  15.0%
New York    Banana  Price   $9.00
New York    Banana  Rating  10.0%

This is what my pivot table looks like right now:

http://abload.de/img/weightedaek08.jpg

Obviously the weighted average of the Rating is different than the regular average. Is there a way to get the weighted averages in the sum fields? I don't even need the Apple, Banana and Pear in the columns, in the end what I want to achieve is just show the cities (New York, Miami) and their weighted averages.

pnuts
  • 58,317
  • 11
  • 87
  • 139
robs
  • 649
  • 4
  • 13
  • 28
  • Which 150% do you mean? The percentage (rating) is just a fictitious measure as in customer rating or something – robs Aug 07 '15 at 13:12
  • Its the weighted average which I calculated manually: http://abload.de/img/weightedavg221s39.jpg This number is what I want to show in the pivot instead of the regular average – robs Aug 07 '15 at 13:45

1 Answers1

0

What you need is you need your weighting column that lines up with price so that it looks like this:

City    Product Measure $ Ammount   % to Total
Miami   Apple   Price       $4.00   50.00%
Miami   Pear    Price       $3.00   15.00%
Miami   Banana  Price       $13.00  85.00%
New York    Apple   Price   $2.00   75.00%
New York    Pear    Price   $11.00  15.00%
New York    Banana  Price   $9.00   10.00%

You can then add in the calculated Weighted average in the pivot table by telling excel that it's the $ Amount * the percentage

You can also use the sumproduct formula for a weighted average. In this example write =SUMPRODUCT(D2:D7,E2:E7)

tc_NYC
  • 192
  • 1
  • 2
  • 11
  • pnuts, the weighted averages are calculated manually outside of the pivot table. For the 67.50% (Miami) I made a sumproduct of Miami Apple, Banana, Pear ratings and Miami amounts, then divided by the sum of Miami amounts. – robs Aug 07 '15 at 13:09
  • Thomas, how can I get a pivot in that format? So basically all the amounts where the measure says rating needs to go in a new column somehow right? – robs Aug 07 '15 at 13:10
  • Yea, you can add in a calculated field in the pivot table by telling it is the $ ammount x the % to total. In Excel 2010, I go to Options, fields, items and sets and then insert calculated field – tc_NYC Aug 15 '15 at 00:59