Weighted average rate of a particular item

[RAMU]

Dear Friends,

Am trying to get weighted average rate of a particular item in a multiple cell range.

Suppose, From A1 following are the data:

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]FRUIT[/TD]
[TD="class: xl63, width: 64"]RATE[/TD]
[TD="class: xl63, width: 64"]QTY[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]GUAVA[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]MANGO[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]9.5[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]75[/TD]
[/TR]
</tbody>[/TABLE]

In E1 APPLE is mentioned & in F1 I need the weighted average rate of apple from the cell range of A2 to C6.

Kindly help.

Regards
RAMU

 

[steve the fish]

I think you mean something like this:

=SUMPRODUCT(--(A2:A6=E1),B2:B6,C2:C6)/SUMIF(A2:A6,E1,C2:C6)

 

[RAMU]

Thanks, it's gr8. But Why are your dividing by SUMIF, kindly let me know.

 

[steve the fish]

You need total paid (rate x quantity) which is the sumproduct part and then you need to divide by quantity (the sumif part) to get average.

 

[RAMU]

Yes, Thanks a Ton.