# Rank order highest to lowest using index

Rank Order.xlsx

Hi,

I would like a ranking formula to sort col B highest to lowest using column A index Racedatetime.

Highest =1 etc.

Please refer attached sheet and place formula in column C

Many Thanks

Ian

Ian Bell

OOps sorry I meant lowest number = 1
If you are using Microsoft 365, you have the SORT function that is perfect for this purpose. Put this formula in one cell and it returns two columns of results very quickly.
``=SORT(A2:B21000,1,1)``

With older versions of Excel, you are probably better off using the RANK function, then manually sorting your data on its results.

``=RANK(A2, A:A,1)``

If you want a formula to do the sorting and are using an older version of Excel, here are two agonizingly slow formulas that do the trick.
``````=AGGREGATE(15,6,A\$2:A\$21000,ROWS(I\$2:I2))  returns the dates

=INDEX(B\$2:B\$21000,AGGREGATE(15,6,ROW(B\$2:B\$21000)/(A\$2:A\$21000=I2)-ROW(B\$2)+1,COUNTIF(I\$2:I2,I2)))   returns the odds``````

Hi Brad, I may not have explained the question too well.
Please refer to example on amended sheet attached
ThanksRank Order.xlsx

By the way I use Excel 2019 Pro 64 bit. I have no use for other software in the 365 range.
I reproduce your column C rankings using this formula:
``=RANK(B2,B\$2:B\$16,1)``
Commercial for Microsoft 365:

Excel in Microsoft 365 has dynamic arrays. This lets you put a formula in one cell and have it return an entire column of results.
``=RANK(B2:B16,B2:B16,1)``

Thanks but I need it for the whole column some 21k+ rows
RANK ignores blank cells, so you could use a reference range that extends beyond your current data to allow for future data.
``````=RANK(B2,B\$2:B\$21000,1)   formula for a single cell. Copy it down.

=RANK(B2:B20861,B2:B20861,1)   formula for Microsoft 365 (calculates column of results)``````

That doesn't work.
Would you mind copying down on the sheet and post up
Thanks

Seems to be total confusion here Brad :)

I was wanting it to be ranked according to the index for each series and not for the total number of rows.
The example I gave was for rows  A2:A16  you will notice the time 16:50 for each of those rows.
Rows 17 to 30 is another series with time being 17.25

Maybe I didn't explain it clearly apologies if that was the case.

byundt

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

Great !  thanks Brad. Works a treat.

The formula is correct for lowest to highest as per my correction of subject title.
Now I would like to post the original title again as I would now like a formula
to rank Highest to Lowest.

``=COUNTIFS(A\$2:A\$21000,A2,B\$2:B\$21000,">" & B2)+1``