One Minus The Next Highest Number

[ExcelRaceRatings]

Hello,

I am looking for advice on a formula to basically minus one from the next highest number.

If A1 to A8 have the numbers:

3
5
1
8
4
6
2
7

if the number 2 is removed from the above then i want a formula to correct the order in cells B1 to B8 so they would read:

2
4
1
7
3
5

6

Any advice much appreciated.

 

[jasonb75]

Try this in B1 and fill down.

=IFERROR(RANK(A1,$A$1:$A$8,1),"")

 

[ExcelRaceRatings]

Try this in B1 and fill down.

=IFERROR(RANK(A1,$A$1:$A$8,1),"")

Yep perfect, well played!