Weighted Average Based on Multiple Criteria

[zach9208]

I need help writing a formula to do a weighted average base on multiple criteria. I have 4 main columns. Below is an small sample with dummy data. I really have about 500+ records.

Marginal Revenue (bps) | Product Code | Trade Month | Loan Amt |
.34 | C30 | 1511| 150,000
.36 | C30 | 1511| 190,000
.31 | C30 | 1508| 150,000
.50 | C30 | 1511| 140,000
.34 | C15 | 1510| 150,000
.36 | C30 | 1511| 190,000
.31 | C20 | 1512| 150,000
.50 | C30 | 1511| 140,000

I need to do a weighted average based on marginal revenue and loan amt. However, I must meet the criteria listed below.

Product Code=C30 & Trade Month=1511


Thanks in advance!!!!

 

[oldbrewer]

easy to sum the loan amount for your criteria, but how are you going to do your weighted average as opposed to 810,000 divide by 5

 

[Aladin Akyurek]

Always mention what the weights are...

=SUMPRODUCT($D$2:D$9,$A$2:$A$9,--($B$2:$B$9="C30"),--($C$2:$C$9=1511))/
    SUMIFS($A$2:$A$9,$B$2:$B$9,"C10",$C$2:$C$9,1511)

Is this what you are after?

 

[zach9208]

That is the problem. I think I need to nest a (sumifs) inside a sumproduct to filter out to show just C30 and the trade month 1511.

Doing this manually, I believe the answer is a weighted average of 0.40469/

I should have mentioned this before. I need the weighted average of marginal revenue by loan amount.

 

[zach9208]

I have been playing around with a formula just like that. I will give it a try and let you know.

 

[zach9208]

Appears to be working. I have to modify it a little bit. Thanks, Aladin.

Always mention what the weights are...

=SUMPRODUCT($D$2:D$9,$A$2:$A$9,--($B$2:$B$9="C30"),--($C$2:$C$9=1511))/
    SUMIFS($A$2:$A$9,$B$2:$B$9,"C10",$C$2:$C$9,1511)

Is this what you are after?

 

[Aladin Akyurek]

Appears to be working. I have to modify it a little bit. Thanks, Aladin.

You are welcome.