# Thread: Ranking horizontally formula (Excel 2003)

1. ## 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

2. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Ranking horizontally formula (Excel 2003)

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

8. ## 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
•