Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Ranking horizontally formula (Excel 2003)

    Is there a way to rank rows 2:5 of the attached to make them like rows 11:14. I need to sorty by row 2, highest value in column B, next highest in column C. Is there an index or match formulas to do this in Excel 2003? Thanks
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ranking horizontally formula (Excel 2003)

    You can select range A1:M5.
    Select Data | Sort...
    Click Options...
    Specify that you want to sort from left to right and click OK.
    Specify that you want to sort on Row 2, descending.
    Click OK.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Ranking horizontally formula (Excel 2003)

    Is there a way to do it with formulas? I need to do several like this that will automatically update. Thanks for the help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ranking horizontally formula (Excel 2003)

    You'd need two ranges for each table: one with the unsorted data and one with formulas that sort the data. That doesn't seem very attractive to me.

    You could record a macro that sorts the data, and modify it to suit your needs.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Ranking horizontally formula (Excel 2003)

    actually, the ugly way is what I need. A macro won't work for my purposes. Thanks - Joan

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ranking horizontally formula (Excel 2003)

    You can use a combination of MATCH, LARGE and COLUMN to find where the n-th largest item is, and then use INDEX to return the corresponding item in another row, but since there are ties, it gets pretty hairy MATCH doesn't distinguish between tied values. A macro would be MUCH more convenient here.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ranking horizontally formula (Excel 2003)

    How about this. This is an approach similar to what Hans alludes to.

    Row 1 has an adjusted value. It rounds the percentage in row 2 to 4 decimals and then add the row as the next 3 decimals (thus there are no ties). It then looks up where the column for the largest 1st, 2nd 3rd, etc values. Using this column number it indexes from each row to pull up the data from that column.

    So it has the original 4 rows, the 4 output rows, and 2 additional rows.

    Steve
    Attached Files Attached Files

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Ranking horizontally formula (Excel 2003)

    ahhh, perfecto!

Posting Permissions

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