Results 1 to 3 of 3
  1. #1
    lugnutmonkey is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2013
    Posts
    42

    Calculating a weighted average

    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 :
    Code:
    Avg Duration: ([SumOfCall Count]*[AvgOfDuration])/[SumOfCall Count]
    This code returns a value that is equuivilant to the AVG function.

    Anybody have Suggestions?

  2. #2
    Stingaway is offline Efficiency Junkie
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Location
    The deep south. Keep going until you hit water basically.
    Posts
    224
    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.

  3. #3
    lugnutmonkey is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2013
    Posts
    42
    I used the code
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Weighted Average in SQL
    By Ashe in forum Forms
    Replies: 9
    Last Post: 10-11-2011, 12:46 AM
  2. Weighted-average inventory costing.
    By evander in forum Database Design
    Replies: 9
    Last Post: 01-03-2011, 08:32 AM
  3. Calculating average in table
    By prv in forum Database Design
    Replies: 1
    Last Post: 12-14-2010, 01:35 PM
  4. Finding a weighted average
    By oldman in forum Queries
    Replies: 3
    Last Post: 09-10-2010, 08:41 PM
  5. Weighted Average - Challenges
    By edmund_xue in forum Access
    Replies: 0
    Last Post: 04-02-2008, 12:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums