Ranking horizontally formula (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

ahhh, perfecto!

Reply | Quote