Link to home
Start Free TrialLog in
Avatar of Ian Bell
Ian BellFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

Avatar of Ian Bell
Ian Bell
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

OOps sorry I meant lowest number = 1
Avatar of byundt
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)

Open in new window


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)

Open in new window


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

Open in new window


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)

Open in new window

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)

Open in new window

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)

Open in new window


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.

ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

Rank-OrderQ29224015 (1).xlsx

Above is the sheet containing the formula criteria
=COUNTIFS(A$2:A$21000,A2,B$2:B$21000,">" & B2)+1

Open in new window

Rank-OrderQ29224015--1-.xlsx
Thanks Brad. I feel embarrassed discovering it just needed the less than char. changed to greater than.