+ Reply to Thread
Results 1 to 6 of 6

Return Name from row based on rank from total of columns

  1. #1
    Registered User
    Join Date
    09-18-2020
    Location
    Eastlake, Ohio
    MS-Off Ver
    Microsoft 365
    Posts
    2

    Angry Return Name from row based on rank from total of columns

    I am running a league where players score points for the position they finish. I am trying to generate a formula so I can show their current standings off to the side based on their current total points. Each column is headed by their name and below their names are the points they earn each week and at the bottom of the column is their total points accumulated. The names are in cells B1 to J1. The players corresponding total points are in cells B37 to J37 respectively. Here is the function I have been trying to tweak:=INDEX($B$1:$J$1,,MATCH(SMALL(RANK(B37,$B$37:$J$37,0)+COLUMN($B$1:$J$1)/1000,COLUMNS($L13:L13)),RANK(B37,$B$37:$J$37,0)+COLUMN($B$1:$J$1)/1000,0)). It seems to work (not getting any errors), but it doesn't put them in rank order of highest to lowest. I added in the (+column($B$1:$J$1)/1000) part so that ties wouldn't cause a problem. Also, the other issue I am having with this formula is that I have to click and drag the cells horizontally to get the names to transfer. If I try to click and drag the formula vertically (which is how I would like it to display) it gives me a duplicate in the 2nd cell and then the "#VALUE!" in the rest.
    Attached Files Attached Files
    Last edited by DRA008; 09-18-2020 at 02:09 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,822

    Re: Return Name from row based on rank from total of columns

    It would help if you attached a sample Excel workbook. Follow the instructions given in the yellow banner at the top of the screen.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    09-18-2020
    Location
    Eastlake, Ohio
    MS-Off Ver
    Microsoft 365
    Posts
    2

    Re: Return Name from row based on rank from total of columns

    Sorry, just attached the excel file. Thanks!

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Return Name from row based on rank from total of columns

    Here's what I did.
    Below your data (row 38), I did the trick with adding the column/100 to stratify them. In B38 copied right
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then in M3 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I formatted the numbers to 0 decimal places.
    Then in N3 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Does that work for you?
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Australia
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,498

    Re: Return Name from row based on rank from total of columns

    L13 cell , array formula,copy and drag right

    HTML Code: 
    Look at the name of your worksheet that is duplicated name , do you need to add it up and rank again, if it is a formula
    HTML Code: 
    Last edited by wk9128; 09-18-2020 at 07:37 PM.

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

    Re: Return Name from row based on rank from total of columns

    Alternative non array solution

    In L13 then drag down

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Quang PT

+ 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. Sum based on rank and total can't be more than 100
    By wazza1010 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-11-2019, 04:45 AM
  2. Replies: 4
    Last Post: 07-13-2017, 05:45 PM
  3. Replies: 2
    Last Post: 04-27-2015, 08:50 AM
  4. Named Rank based on Total
    By Adam Schaefer in forum Excel General
    Replies: 6
    Last Post: 07-24-2014, 10:02 AM
  5. Sum from multiple items in a list, based on rank, until a total is met
    By tim_71 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-19-2013, 09:17 PM
  6. Return text value based upon quantitative rank
    By cmyoung in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-18-2013, 05:41 PM
  7. [SOLVED] RANK function - to return data based on 2 different columns
    By Adsup01 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-30-2013, 08:17 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