+ Reply to Thread
Results 1 to 9 of 9

Weighted Scoring System

  1. #1
    Registered User
    Join Date
    10-30-2017
    Location
    England
    MS-Off Ver
    MS Office 2013
    Posts
    4

    Weighted Scoring System

    Hi All,

    I'm trying to build a formula to give a weighted scoring system on a monthly turnover target. The turnover is in set brackets with points awarded for the higher the turnover.

    The scoring system i want to try and achieve is as follows.

    25 Points - £5,000+
    20 Points - £4,000 - £4,999
    15 Points - £3,000 - £3,999
    10 Points - £2,000 - £2,999
    5 Points - £1,000 - £1,999
    0 Points - £0 - £999

    What would be the best way of going about doing this?

    Any help appreciated. Thanks.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Weighted Scoring System

    =vlookup(c1,$a$1:$b$15,2)
    Attached Files Attached Files

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Weighted Scoring System

    Depending on real sheet layout you probablu shall use VLOOKUP function or INDEX and MATCH.
    You may check office help on how to use VLOOKUP - it shall be enough to solve your problem.
    Best Regards,

    Kaper

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Weighted Scoring System

    With turnover in A1 try this formula

    =MIN(25,FLOOR(A1/200,5))
    Audere est facere

  5. #5
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Weighted Scoring System

    Or,

    Try with 'LOOKUP'

    =LOOKUP(A2,O$1:O$7,P$1:P$7)

    See the attached file.
    Attached Files Attached Files

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Weighted Scoring System

    Or use lookup

    =lookup(a1,{0,1000,2000,3000,4000,5000},{0,5,10,15,20,25})
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  7. #7
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Weighted Scoring System

    Quote Originally Posted by daddylonglegs View Post
    With turnover in A1 try this formula

    =MIN(25,FLOOR(A1/200,5))
    @daddylonglegs

    Can you please explain how did you make logic ??
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Weighted Scoring System

    The points allocation is a “straight line” - 5 points for every whole 1,000 so if you divide the turnover by 200, but round down to previous 5 ( using FLOOR function) you will get that. MiN stops the points going higher than 25

    Another option....

    =MIN(5,INT(A1/1000))*5
    Last edited by daddylonglegs; 10-30-2017 at 09:00 AM.

  9. #9
    Registered User
    Join Date
    10-30-2017
    Location
    England
    MS-Off Ver
    MS Office 2013
    Posts
    4

    Re: Weighted Scoring System

    WOW. Thanks for all the help so quickly everyone!

    Went for this in the end =lookup(a1,{0,1000,2000,3000,4000,5000},{0,5,10,15,20,25}). Thanks nflsales.
    Last edited by andyjt1990; 10-30-2017 at 08:53 AM. Reason: spelling

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Yes/No/NA Weighted Scoring System
    By chaselo1987 in forum Excel General
    Replies: 8
    Last Post: 02-17-2019, 09:14 AM
  2. Scoring System in 2010
    By Daedra in forum Excel General
    Replies: 1
    Last Post: 08-30-2011, 06:56 AM
  3. Scoring System
    By DarksideEric in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-18-2011, 05:33 AM
  4. weighted scoring formula to exclude not applicables
    By sav1979 in forum Excel General
    Replies: 4
    Last Post: 06-15-2011, 01:11 PM
  5. Weighted Scoring, instead of the simple average
    By dandavis1 in forum Excel General
    Replies: 2
    Last Post: 12-11-2009, 05:49 AM
  6. Excel multi dimensional weighted scoring
    By TimothyA in forum Excel General
    Replies: 1
    Last Post: 03-15-2008, 06:09 PM
  7. Scoring System
    By betrayedslinky in forum Excel General
    Replies: 2
    Last Post: 07-06-2007, 08:02 PM

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