+ Reply to Thread
Results 1 to 8 of 8

Looking for formula to group rankings, then label two groups by a letter.

  1. #1
    Registered User
    Join Date
    01-07-2021
    Location
    Canberra, Australia
    MS-Off Ver
    16.44
    Posts
    5

    Looking for formula to group rankings, then label two groups by a letter.

    Hey team, I'm looking to find a formula to turn two numbers within two ranges into a letter. For example, if Player A's ranking is 37 and Player B's ranking is 164, is there a formula that's something like: if B2 is between 31-50 and D2 is between 101-200, put an A; if B2 is between 51-100 and D2 is 200+, put a B. Thanks guys!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Looking for formula to group rankings, then label two groups by a letter.

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    01-07-2021
    Location
    Canberra, Australia
    MS-Off Ver
    16.44
    Posts
    5

    Re: Looking for formula to group rankings, then label two groups by a letter.

    It's not allowing me to attach a sample workbook, however hopefully I can explain the problem in detail here.

    If my data looks like the following:
    Player A Rank Player B Rank Rank Diff.
    Raonic 35 Querrey 45
    Kyrgios 26 Herbert 64

    In 'Rank Diff.' is there a formula I can enter to automatically enter a letter based off its rank bracket?
    For example, one of my brackets is rank 11-30 vs rank 51-100, which is an 'I', so in 'Rank Diff.' next to Kyrgios v Herbert, I want it to say 'I'. Is there a way to do this at all, and if so for multiple rank brackets?

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Looking for formula to group rankings, then label two groups by a letter.

    In E2 then drag down:

    Please Login or Register  to view this content.
    I wonder if there is any more criteria sets for other letter? this formula is just for single letter "I" only.
    Attached Files Attached Files
    Quang PT

  5. #5
    Registered User
    Join Date
    01-07-2021
    Location
    Canberra, Australia
    MS-Off Ver
    16.44
    Posts
    5

    Re: Looking for formula to group rankings, then label two groups by a letter.

    Oh yes, that's perfect! You're right, there more criteria sets for other letters, as follows:

    1-10:1-10 A
    1-10:11-30 B
    1-10:31-50 C
    1-10:51-100 D
    1-10:101-200 E
    1-10:201+ F

    11-30:11-30 G
    11-30:31-50 H
    11-30:51-100 I
    11-30:101-200 J
    11-30:201+ K

    Plus others. If you could please write the formula for two criteria sets, that would be brilliant and I'll be able to figure the rest out. Thanks so much for your help.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Looking for formula to group rankings, then label two groups by a letter.

    That is incorrect. There are NO restrictions on posting a sample sheet directly on the Forum. You just didn't stick EXACTLY to the guidance.

    NEXT TIME: please re-read the yellow banner (top) and post a sample sheet. As you can see the forum messes up any formatting in your data, so we have to recreate manually what YOU already have in front of you.

    So. Set up a correspondence table and use this formula:

    =INDEX($Q:$Q,AGGREGATE(14,6,ROW($Q$2:$Q$12)/((C3>=$O$2:$O$12)*(D3>=$P$2:$P$12)),1))
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 01-30-2021 at 04:17 AM.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  7. #7
    Registered User
    Join Date
    01-07-2021
    Location
    Canberra, Australia
    MS-Off Ver
    16.44
    Posts
    5

    Re: Looking for formula to group rankings, then label two groups by a letter.

    Sorry mate, it must be my computer and not the forum. I stuck EXACTLY to the guidance but was unable to have the OPTION to post a sample sheet. Using a forum is slightly EASIER than figuring out excel formulas, generally speaking of course. Slightly LESS condescending next time would be better received, YOU of course don't have to reply to me at all.

    With that said, thank you very much for your reply, that formula mostly worked so I'll have a tweak and try to fix the issue. I think the problem is that the smallest number is not always in the left column, but if I can't change the correspondence table accordingly then I'll just ensure the smallest number is in the left column.

    Thanks again and I appreciate your help, enjoy your weekend!

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Looking for formula to group rankings, then label two groups by a letter.

    Whatever... but you can post a sample sheet. Everyone can.

+ 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] Search Rankings in 4 groups of 4 names and insert rankings in spreadsheet
    By PhilBar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-25-2020, 09:14 AM
  2. [SOLVED] Formula to RANK groups of Values and return letter
    By hammer2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-19-2016, 04:36 AM
  3. Solver and group rankings
    By ACR0123 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-14-2014, 07:00 PM
  4. Solver and group rankings
    By ACR0123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-13-2014, 09:52 PM
  5. Replies: 11
    Last Post: 03-13-2014, 08:23 PM
  6. Change letter - group of numbers in formula
    By nunito in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-07-2013, 04:21 PM
  7. A Formula for classifying groups of numbers into a letter.
    By surfer1 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 07-05-2013, 09:48 AM

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