Replacing sorted values with 1,2,3 etc

Can you attach your spreadsheet?

Try in AE2

=SMALL(INDEX($AB$2:$AB$25+$AD$2:$AD$25,),ROWS($1:1))

Thanks, but I would like the values to be from 1 to 24, so the smallest value in column AE would show as "1", the 2nd smallest would show "2" and the highest value would show as "24". Then the football club in column A could be sorted by the value in column AE so that for example the club with best trend would be Bournemouth in the above example. Then I could note down Bournemouth are 1, Millwall are 24 and all inbetween.

Sorry for not explaining it well (:

Wow that is really hard on the eyes with all those colors

I would just use your currect column AE as a helper and then use this in AF, copied down...
=RANK(AE2,$AE$2:$AE$25,1)

If you dont want duplicate ranks, theu use this instead...
=RANK(AE2,$AE$2:$AE$25,1)+COUNTIF($AE$2:$AE$25,AE2)-1

whatever works for you Im happy I was able to help

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)