Weighted Rate

[Scott M.]

This should be easy... I would like to find a worksheet
function that will calc a "weighed rate" for a list of
balances and associated rates.

1000 4.50
2000 5.00
3000 6.00
4000 7.00
5000 8.00

15000 6.70

6.70 is the number that is calculated by "weighting" each
row but isn't there an Excel function that can do this
easily? Thanks!

 

[Ken Wright]

=SUM((A1:A5)*(B1:B5))/SUM(A1:A5) array entered CTRL+SHIFT+ENTER

or without having to array enter:-

=SUMPRODUCT((A1:A5)*B1:B5)/SUM(A1:A5)

 

[JohnI in Brisbane]

Scott,

try

=SUMPRODUCT(A2:A6,B2:B6)/SUM(A2:A6)

regards,

JohnI

 

[Paul Corrado]

Scott,

Not sure if there is a specific function, but this formula seems simple
enough.

=SUMPRODUCT((A1:A5)*(B1:B5))/SUM(A1:A5)

PC

 

[Peo Sjoblom]

One possible way

=SUMPRODUCT(A1:A5,B1:B5)/SUM(A1:A5)