weighted average yield

[William]

Is there something like sumproduct in Excel that works in Access to do
weighted averages?

I have two columns of data: Loan balances and Yields.
Col A Col B Col C
64,000.00 6.5 4
97,000.00 6.2 4
72,000.00 6.4 4

I use this in Excell =sumproduct(b1:b3,a1:a3)/sum(a1:a3) = 6.344 but I am
trying to move everything into Access for better management.

I have forms that pull the data from a query so that I can query by type
(Col C) and I can easily sum the total balances, but I have been unable to
get a weighted average yield. I am using 2003 and 2007 Access.

Thanks,

 

[Ken Sheridan]

The equivalent in Access would be:

SUM([Col A]*[Col B])/SUM([Col A])

This only works because you are applying the SUMPRODUCT function to
one-dimension arrays in Excel. It couldn't be done in this way with arrays
of more than one dimension such as SUMPRODUCT(A1:B3,C1:C3).

Ken Sheridan
Stafford, England