Weighted average per entity

[pitinswiss]

Hello,
I browsed many conversations on weighted averages and did not find a solution to my problem. My apologizes if I jumped over it.

Over 3 columns I got:
col 1 = product (text) ; col 2 = price (number) ; col 3 = volume (number)

For each product there can be many prices and different volumes.

My goal is to calculate the weighted average for each product automatically.

I know SUMPRODUCT(blabla)/sum(blabla) does weighted averages but I cannot find the syntax to do it on a per product base.

I hope I am clear

p.

[Fotis1991]

Did you try to use AVERAGEIF function?

=AVERAGEIF($A$2:$A$10,A2;$C$2:$C$10)

[pitinswiss]

Thanks for the quick reply.

I just played with the averageif but if only gives me the average price. I need to weight it vs volumes sold. is there a way to use averageif with sumproduct, maybe ?

[Fotis1991]

Pls,attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[estige]

Hi, maybe: With Product in A2:A16, Price in Column B and Volume in column C
In D1 and copy Down:

Please Login or Register  to view this content.

[pitinswiss]

Cool! Thank you estige, it works !

And thanks to Fotis as well for the prompt help,