Weighted Avg If using SUMPRODUCT

[vbaisdaxing]

ColumnA(amount)	ColumnB(return%)
3100000	.0403
4800000	.1178
9200000	.0403
10300000	.1517
11000000	.0743
11200000	.0462
11700000	.0461
12000000	.0412
12800000	.0485
13400000	.0620

<tbody>
</tbody>

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.