Weighted Avg If using SUMPRODUCT

[vbaisdaxing]

[TABLE="width: 500"]
<tbody>[TR]
[TD]ColumnA(amount)[/TD]
[TD]ColumnB(return%)[/TD]
[/TR]
[TR]
[TD]3100000[/TD]
[TD].0403[/TD]
[/TR]
[TR]
[TD]4800000[/TD]
[TD].1178[/TD]
[/TR]
[TR]
[TD]9200000[/TD]
[TD].0403[/TD]
[/TR]
[TR]
[TD]10300000[/TD]
[TD].1517[/TD]
[/TR]
[TR]
[TD]11000000[/TD]
[TD].0743[/TD]
[/TR]
[TR]
[TD]11200000[/TD]
[TD].0462[/TD]
[/TR]
[TR]
[TD]11700000[/TD]
[TD].0461[/TD]
[/TR]
[TR]
[TD]12000000[/TD]
[TD].0412[/TD]
[/TR]
[TR]
[TD]12800000[/TD]
[TD].0485[/TD]
[/TR]
[TR]
[TD]13400000[/TD]
[TD].0620[/TD]
[/TR]
</tbody>[/TABLE]


Hello everyone,

I wanted to to calculate the weighted average based on a specified range. For example, I would like to be able to enter into cell C1 and D1 which will be the beginning and ending amounts and have a weighted average calcualte based off of my cell selection. For example, assuming i wanted a amount of range of $9,000,000 - $12,000,000, I was able to attain the calculation utilizing SUMPRODUCT(a3:a8,b3:b8)/SUM(a3:a8) and attained 6.58% weighted average. Hence, I'm trying to make this formula more dynamic based off of my selection in cell C1 and D1.

Any suggestions would be greatly appreciated.

Thanks,

 

[Marcelo Branco]

Try

=SUMPRODUCT(A1:A10,B1:B10,--(A1:A10>=C1),--(A1:A10<=D1))/SUMIFS(A1:A10,A1:A10,">="&C1,A1:A10,"<="&D1)

where
C1=9,000,000
D1=12,000,000

M.

 

[vbaisdaxing]

Thank you very much for the help Marcelo! Greatly appreciated!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.