+ Reply to Thread
Results 1 to 3 of 3

Weighted average (files opened per person)

  1. #1
    Registered User
    Join Date
    09-23-2012
    Location
    Beira, Mozambique
    MS-Off Ver
    Excel:Mac 2011
    Posts
    2

    Question Weighted average (files opened per person)

    Good day all you awesome people!

    Have one that has my mind cracking:

    Lets say we have John and Pete, and they both opened 300 and 400 files respectively for the month, giving them a nice healthy 350 files per person. Woohoo!

    Now along comes Paul, opens a meager 1 file for the month, which makes it 3 people for 701 files. The average of this works out to be 233.67, which brings the KPIs for the other two guys down tremendously!

    What I would like to achieve is some sort of weighted average (if possible), which gives a much more accurate answer (closer to 350 files per person).

    The amount of people and files per month can vary, so this calculation needs to be super flexible.

    Now lets all put the thinking caps on!

    Thank you!!

    Kind regards,
    J

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,809

    Re: Weighted average (files opened per person)

    I dont know if theres a proper term for it but I always knew it as "Trimmed Mean" which involves removing the smallest and largest x% of the values and averaging the rest. Not sure however, how that works if theres only 3 numbers (Im guessing 300 would be the answer after you've removed the 1 and 400 values).

    In bigger data sets however you would get something like the following:

    Data
    1,20,20,30,30,30,50,70,100,270

    Averaging that would give you 62.1

    however, removing the top and bottom 10% would give a list of
    20,20,30,30,30,50,70,100

    which when averaged gives 43.75 which, is more indicative of the actual scores given ie 75% of the 8 numbers are in a range of 20 to 70
    If someone has helped you then please add to their Reputation

  3. #3
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2007 Enterprise
    Posts
    726

    Re: Weighted average (files opened per person)

    Try weighted %age

    That is file opened by the person divided by total files multiplied by 100
    In your example it it will be
    42.79
    57.06
    0.14

    You may replace 100 by another digit say 10 to get points out of 10
    Regards
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1