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