Weighted Averages

[H57]

If in one column I have a list of loans of various sizes and the interest charged in another column relating to each deal, how can I work out the average interest rate charged for say 50 loans in a way that is weighted towards the size of the loan.

For example if $100,000 is charged at 2% and another deal for only $5,000 is charged at 10% the average isn't really 6% is it!

To add to the task I need to calculate this weighted average only for loans that as classed as 'completed' or 'pending' in a single column but excluding 'not proceeding' as a 3rd choice in the same colummas completed or pending.

Thanks in advance

[daddylonglegs]

If the loans are in A2:A100 and the %s in B2:B100 then you can use this formula

=SUMPRODUCT(A2:A100,B2:B100)/SUM(A2:A100)

[Richard Buttrey]

Hi,

To find the weighted average interest rate you need to multiply each loan value by the interest rate to compute the interest charged. Then sum the interest amounts and divide this sum by the sum of the loan amounts.

Use the SUMIF() function to identify the completed or pending loans and only compute with those.

Rgds