Problem with Averages in Pivot Table

[rafa1]

Hello,

I have a table of data in Excel 2010 that looks like this:

After transforming the data into a pivot table and adding a calculated field ‘Quality x Number’, it looks like this:

The field list looks like this:

What I am trying to do is add a ‘Real Quality’ column or calculated field to the table. ‘Real Quality’ should be the sum of ‘Quality x Number’ for each Fruit, divided by the total number of each Fruit.

For example, ‘Real Fruit Quality’ for Berries should be:

((4 x 100) + (8 x 5) + (9 x1)) / (100 + 5 + 1) = 449/106 = 4.24

And ‘Real Fruit Quality’ for Nut should be:

((4 x 3) + (2 x 4) + (8 x 150)) / (3 + 4 + 150) = 1220/157 = 7.77

So with the ‘Real Fruit Quality’ Field added, the table should look like:

I am really struggling to achieve this. Finding a way of taking the sum of the ‘Quality x Number’ field for each Fruit, and then dividing that figure by the total of Number for the Fruit, is proving difficult.

Does anybody know of a way that this can be achieved in Excel?

Any help would be greatly appreciated.

 

[RonB1111]

You can do this in your original table of data by adding 2 columns:
Col E (Quality x Number) Enter in E2: =C2*D2
Col F (Real Fruit Quality) Enter in F2: =SUMIF(A$2:A$7,A2,E$2:E$6)/SUMIF(A$2:A$7,A2,D$2:D$6)
then copy both formulas down (adjust ending ranges for your data)

 

[rafa1]

Thank you so much for the response! That's exactly what I was looking for.

 

[RonB1111]

You're welcome. Thank you for the feedback.