Results 1 to 8 of 8

Rank Data in Multiple Columns

This is a discussion on Rank Data in Multiple Columns within the Excel Questions forums, part of the Question Forums category; B C D E F G Jack $156.00 Jack $157.04 Jack $99.12 Joe $131.28 Joe $150.79 Joe $92.56 Mary $128.93 ...

  1. #1
    New Member
    Join Date
    Feb 2006
    Posts
    16

    Default Rank Data in Multiple Columns

    B C D E F G
    Jack $156.00 Jack $157.04 Jack $99.12
    Joe $131.28 Joe $150.79 Joe $92.56
    Mary $128.93 Mary $128.94 Mary $66.06
    Mark $124.00 Mark $101.31 Mark $52.43

    I need to rank the data in columns c,e,g and return the answer in column a next to Jack, Joe, Mary and so on. Here is an example of the formula that is not working for me, =rank(C2,C2:C5,F2:F5,I2:I5,0), it returns to tell me I have used to many instances. Thanks for the help.

  2. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default Re: Rank Data in Multiple Columns

    Hi, your post is a little unclear... what are you trying to return ? The rank of the SUM of each person ? If so I'd suggest creating another column to consolidate and rank that, ie:

    Sheet13

     ABCDEFGH
    11Jack156Jack157.04Jack99.12412.16
    22Joe131.28Joe150.79Joe92.56374.63
    33Mary128.93Mary128.94Mary66.06323.93
    44Mark124Mark101.31Mark52.43277.74

    Spreadsheet Formulas
    CellFormula
    A1=RANK($H1,$H$1:$H$4,0)
    H1=SUM(C1:G1)


    Excel tables to the web >> Excel Jeanie HTML 4

    If that's not what you want please elaborate.

  3. #3
    New Member
    Join Date
    Feb 2006
    Posts
    16

    Default Re: Rank Data in Multiple Columns

    each number represents sales for the month, I would like to rank each month individualy and have the rank show up beside each name in every month.

  4. #4
    Board Regular
    Join Date
    Jul 2005
    Posts
    333

    Default Re: Rank Data in Multiple Columns

    to rank values in column C, put the following in A1
    =RANK(C1,$C:$C)

    you can then copy this formula down column A. I am still somewhat confused as to how you are trying to rank the 3 months in column a, or if A is just for the first month. If it is just for the first month, then this should work. If not, please explain further, and we can go from there.
    "Give a man a fish and he will eat for a day. Teach a man to fish . . . and he may never go back to work."
    Excel 2003 SP3 running on XP

  5. #5
    New Member
    Join Date
    Feb 2006
    Posts
    16

    Default Re: Rank Data in Multiple Columns

    I want to know where Jack's first month ranks compared to all 3, and where his 2 month ranks compared to all 3, and so on for each person. Thanks for all the help.

  6. #6
    New Member
    Join Date
    Feb 2006
    Posts
    16

    Default Re: Rank Data in Multiple Columns

    There should be columns inserted between each month.


    How did Lasw10 capture the excel screen?

  7. #7
    Board Regular
    Join Date
    Jul 2005
    Posts
    333

    Default Re: Rank Data in Multiple Columns

    One more thing - if you are trying to rank the current month sales for 1 person (i.e. Jack )against all people for all months, then I would suggest re-arranging data so that all are in the same 3 columns.

    Just a thought . . . again, not sure what your goal is. Perhaps you can explain what the output should look like based on your example, and how you got to those results.(eg - Jack should have a rank of 2 because if you . . . Mary should have a rank of 1 because if you . . . )
    "Give a man a fish and he will eat for a day. Teach a man to fish . . . and he may never go back to work."
    Excel 2003 SP3 running on XP

  8. #8
    Board Regular
    Join Date
    Jul 2005
    Posts
    333

    Default Re: Rank Data in Multiple Columns

    If you are inserting columns for each month and just ranking the person against the others for the month, then my first formula should work for the first month (in C), and then you will have to adjust values for the other columns where you insert.
    "Give a man a fish and he will eat for a day. Teach a man to fish . . . and he may never go back to work."
    Excel 2003 SP3 running on XP

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com