Weighted average rate of a particular item

RAMU

Active Member
Joined
Dec 11, 2009
Messages
345
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I think you mean something like this:

=SUMPRODUCT(--(A2:A6=E1),B2:B6,C2:C6)/SUMIF(A2:A6,E1,C2:C6)
 
Upvote 0
Thanks, it's gr8. But Why are your dividing by SUMIF, kindly let me know.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,703
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top