Calculating a weighted average

[lugnutmonkey]

Hello,



I have a problem creating a calculated field. I would like to create a field that contains the weighted average of phone call duration. Currently every month has upto three phone call types; Incoming, Outgoing and Extension. I have set it up so that the data is grouped by month with a total for each month and call type. Each record also contains a average of duration.

The problem is that once they calls are summerized by month and call type; the AVG. function no longer returns an accurate value. I need a way to calculate a weighted average so that when I look at all calls in a month I can get an accurate average of Call Duration.

I have tried :

Avg Duration: ([SumOfCall Count]*[AvgOfDuration])/[SumOfCall Count]

This code returns a value that is equuivilant to the AVG function.

Anybody have Suggestions?

[Stingaway]

If this is a calculated field on a report or a form, you might consider doing the calculations in a query - that way you can reference the query instead of relying on the form or the report to do the calculations on the fly and affect your summary data.

[lugnutmonkey]

I used the code

Sum([SumOfCall Count]*[AvgOfDuration])/(Sum(SumOfCall Count]))

That appears to have worked. A method of calculated a weighted average is

(([Weight]*[Count])+([Weight2]*[Count2])+...ETC) / (Count+Count2+...ETC

By applying the SUM Function to the divisor and dividend, Access will output a proper weighted average value.

Thanks for the help.