+ Reply to Thread
Results 1 to 4 of 4

Scoring (Ranking?) items that fall within a number of 39 parameters

  1. #1
    Registered User
    Join Date
    03-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    3

    Scoring (Ranking?) items that fall within a number of 39 parameters

    Hello Excel Forum; this is my first post.

    I need help building a formula.

    I have seen lots of Ranking help in the past forum topics (thanks) but couldn't find exactly what I was after and made an utter hash of trying to amend the previous responses to be what I wanted, so:

    In relatively plain English, my goal is apply a score/grade/rank to a list of circa 100 items depending on how much the value of each item is when put into circa 40 'buckets' of from-to values. So if an item costs 4500 it will fall into a bucket for items with value 4260 to 4750. The cheapest bucket has parameters of 0k up to 3250 so any items falling into this bucket will have a score of 1 (regardless of how many items fall into that bucket); the next cheapest bucket has parameters of 3260 to 3750 and any items falling into that bucket will have a score of 2. The next cheapest bucket has a score of 3 and so on. Even if there are no items which fall into a bucket I still want that bucket to have score/grade/rank assigned to it so that if any Items fall into bucket 4 they will still get a score/grade/rank of 4 even if there were no Items in bucket 3.

    Same question in slightly more technical language:
    Goal: to apply a sequentially numbered "Score" for each "Item" based on the "Value k" of any Item falling into the Parameters between "From k" to "Tok" rising from the cheapest to the most expensive.
    Notes A: the lowest "Score" is 1 which equates to the "Item" (or Items) falling to lowest Parameter (i.e. the cheapest); the highest "Score" number equates tom the "Item" (or Items) falling into the highest Parameter (i.e. the most expensive).
    Notes B: where more than one "Item" falls into the same Parameter as another "Item" then those "Items" should have the same "Score" and the next "Score" should increase sequentially and not skip any numbers.
    Notes C: where no "Item" falls into a bucket that bucket must still have a "Score" associated with it and any "Items" will only have a "Score" 1 digit sequentially lower or higher than it's neighbouring Parameter, so any "Item" falling into the lowest Parameter gets a "Score" of 1, if there are no "Items" in the second lowest Parameter, then no "Item" gets a "Score" of 2, any items falling into the third cheapest Parameter get a "Score" of 3.

    Below are two tables:
    The first table below with two columns contains the Items and the Values
    The second table below with three columns contains the Parameters of each bucket and the Score attached to each bucket.

    Item Value k
    A 4.5
    B 4.5
    C 6.0
    D 6.0
    E 3.0
    F 5.0
    G 4.0
    H 3.0
    I 3.0
    J 3.0
    K 6.0
    L 5.0
    M 2.9
    N 5.0
    O 6.0
    P 4.0
    Q 6.0
    R 6.0
    S 4.5
    T 6.0
    U 5.0
    V 9.5
    W 4.5
    X 9.0
    Y 8.8
    Z 7.5
    AA 3.0
    BB 8.0
    CC 2.8
    DD 15.0
    EE 5.2
    FF 15.0
    GG 4.8
    HH 10.5
    II 7.2
    JJ 6.0
    LL 4.5
    MM 4.7
    NN 8.0
    OO 9.0
    PP 7.2
    QQ 7.5
    RR 5.0
    SS 7.5
    TT 9.0
    UU 17.0
    VV 5.0
    WW 5.0
    XX 7.5
    YY 5.0
    ZZ 5.8
    AAA 3.0
    BBB 9.0
    CCC 9.5
    DDD 4.5
    EEE 6.0
    FFF 6.0
    GGG 6.0
    HHH 3.3
    III 3.5
    JJJ 4.2
    KKK 10.0
    LLL 7.0
    MMM 6.5
    NNN 5.0
    OOO 3.4
    PPP 13.5
    QQQ 4.7
    RRR 9.6
    SSS 6.5
    TTT 8.0
    UUU 6.2
    VVV 7.5
    WWW 11.0
    XXX 13.0
    YYY 8.2
    ZZZ 12.0
    AAAA 6.5
    BBBB 7.0
    CCCC 5.2
    DDDD 9.0
    EEEE 5.2
    FFFF 8.0
    GGGG 6.0
    HHHH 6.0
    IIII 5.6
    JJJJ 11.0
    KKKK 6.0
    LLLL 7.0
    MMMM 13.0

    Parameter
    From k To k Score
    - 3.25 1
    3.26 3.75 2
    3.76 4.25 3
    4.26 4.75 4
    4.76 5.25 5
    5.26 5.75 6
    5.76 6.25 7
    6.26 6.75 8
    6.76 7.25 9
    7.26 7.75 10
    7.76 8.25 11
    8.26 8.75 12
    8.76 9.25 13
    9.26 9.75 14
    9.76 10.25 15
    10.26 10.75 16
    10.76 11.25 17
    11.26 11.75 18
    11.76 12.25 19
    12.26 12.75 20
    12.76 13.25 21
    13.26 13.75 22
    13.76 14.25 23
    14.26 14.75 24
    14.76 15.25 25
    15.26 15.75 26
    15.76 16.25 27
    16.26 16.75 28
    16.76 17.25 29
    17.26 17.75 30
    17.76 18.25 31
    18.26 18.75 32
    18.76 19.25 33
    19.26 19.75 34
    19.76 20.25 35
    20.26 20.75 36
    20.76 21.25 37
    21.26 21.75 38
    21.76 22.25 39


    What formula can do what I want, or do I need to go through two or more stages?

    The spreadsheet the above comes from contains circa 50 columns and over 100 rows, so the above forms a relatively small part of the overall work. I could do it manually, but that would be cheating!

    Grateful for any help
    Last edited by chiefweasel; 03-01-2013 at 05:28 PM. Reason: awful English and need to tabulate data or put into a table, great start...

  2. #2
    Registered User
    Join Date
    03-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Scoring (Ranking?) items that fall within a number of 39 parameters

    PS - as you can probably see, I can't get the above two tables tabulated properly and whilst I can form a gridlined table I don't know how to get my data into the table once formed.

  3. #3
    Registered User
    Join Date
    01-30-2013
    Location
    Hobart, TAS
    MS-Off Ver
    Office 2003, XL2007
    Posts
    46

    Re: Scoring (Ranking?) items that fall within a number of 39 parameters

    Hope this helps

    Your 2 column table cols A&B. Add new col C (Score)
    In C2 enter =IF(B2="","",INDEX(F:F,MATCH(B2,E:E,1))) copy down to match data

    E1 enter the lowest value in each bucket ie 0,3.26,3.76,4.26 etc
    F1 enter 1,2,3,4 etc
    May need to add one more for any value more than 22.16 (instead of 40 put "Out of range" or similar.
    Use COUNTIF to count individual bucket totals.

  4. #4
    Registered User
    Join Date
    03-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Scoring (Ranking?) items that fall within a number of 39 parameters

    Brilliant! Thank you very much indeed; problem resolved!

+ 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