Need help formulating weighted averages using Access 1

[TheTexan]

The following is an example query with Columns A1 and A2 having the following numeric values:

A1 A2

73 21
77 21
117 21
140 21


My task is to come up with a weighted average for columns A2 and A1...
In other words:
I need to multiply across columns A2 A1 and add the next multiplied pair ans so on etc...etc...

then divide by the sum of A1

for example using the above data:

21*73 + 21*77 + 21*117 + 21*140 / 73+77+117+140

1533 + 1617 + 2457 + 2940 / 407

8547 / 407

21

In Excel ther is a function called SUMPRODUCT that does the following:


A B C D
1 Array 1 Array 1 Array 2 Array 2
2 3 4 2 7
3 8 6 6 7
4 1 9 5 3
Formula Description (Result)
=SUMPRODUCT(A2:B4, C24)
Multiplies all the components of the two arrays and then adds the products— that is, 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3. (156)

My question is...
Is there a function in Access that will perform weighted averages in which I discussed above?

Or perhaps a formula someone could help me out on this one?

Thanks in advance....

 

[PHV]

Something like this ?
SELECT Sum(A1*A2)/Sum(A1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244