Ian Bell

asked on

# Rank order highest to lowest using index

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

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.

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

`=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
```

ASKER

Hi Brad, I may not have explained the question too well.

Please refer to example on amended sheet attached

ThanksRank Order.xlsx

Please refer to example on amended sheet attached

ThanksRank Order.xlsx

ASKER

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.

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)`

ASKER

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)
```

ASKER

That doesn't work.

Would you mind copying down on the sheet and post up

Thanks

Would you mind copying down on the sheet and post up

Thanks

ASKER

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.

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Great ! thanks Brad. Works a treat.

ASKER

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.

Now I would like to post the original title again as I would now like a formula

to rank Highest to Lowest.

ASKER

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

Rank-OrderQ29224015--1-.xlsx
ASKER

Thanks Brad. I feel embarrassed discovering it just needed the less than char. changed to greater than.

ASKER