Custom Function for Weighted Average

Posted by Shamsuddeen. P.K on November 22, 2001 4:10 PM

Column A of my worksheet shows the amount of Loan and Column B shows Interest Rate.

I would like to know how to create a custom function to find out the weighted average of the given range of numbers(formula something like, WAVERAGE(A1:A10,B1:B10)

The function should return the following result :

(GrandTotal of (Loan*Interest))/grandtotal of Loan

Help is highly appreciated.

Regards,
Shamsuddeen. P.K

Posted by s-o-s on November 22, 2001 4:31 PM

Hi,

You can do this by creating a "CSE" formula.
I am not certain about the loan & interest scenario you are quoting for arriving at a weighted average so have created my own scenario using Price & Quantity as follows.

If you have a qty in cells F1 to F10
and a Price interest in cells G1 to G10
enter the following formula into G12

=SUM(F1:F10*G1:G10)/SUM(F1:F10)
DO NOT PRESS ENTER !!

Hold down CTRL+SHIFT then Press Enter

you should now see the formula as below within the curvy brackets.
{=SUM(F1:F10*G1:G10)/SUM(F1:F10)}

if so you have just created an Array Formula which will give you the answer you are looking for. There is loads of Help on syntax etc look under Array or search for CSE formulas.

Hope this helps
s-o-s

======