# Excel - Comparing Numbers

Zak 21:44 09 Oct 03
Locked

Column A has 10 different names and column B has respective totals for each name. These totals are obtained from other calculations and vary on a daily basis.
I would like Excel to automatically enter: 1st, 2nd 3rd etc in column C in the appropriate row.

VoG II 21:52 09 Oct 03

If your totals are in B1:B10

in C1 enter

=RANK(B1, \$B\$1:\$B\$10, 1)

and copy the formula down into cells C2 to C10.

This will give you "1", "2", "3" etc., not "1st", "2nd", "3rd".

FRANKMAC 21:54 09 Oct 03

THE FORMULA IN COLUMN C SHOULD BE AS FOLLOWS

=RANK(B1,B1:B10)
=RANK(B2,B1:B10)
=RANK(B3,B1:B10)
=RANK(B4,B1:B10)

ETC...

THIS WILL RANK THE HIGHEST NO 1ST

TO RANK THE LOWEST NO. 1ST, CHANGE THE FORMULA TO READ

=RANK(B1,B1:B10,1)

ETC

VoG II 21:55 09 Oct 03

VoG is invisible again!

FRANKMAC 21:57 09 Oct 03

jazzypop 21:58 09 Oct 03

Open Excel and search the Help file for the Rank function.

Assuming your values in Column B are from B3 to B12, enter in cell C3

RANK(B3,B3:B12,1)

This sorts the numbers in ascending order. Changing the final 1 to a 0 in the example above will reverse the sort order.

Then copy the formula down from C3 to C12.

Standard Disclaimer: - VOG is the true Excel guru round here - if he says anything different to the above, listen to him, not me :)

VoG II 21:59 09 Oct 03

Just my little joke.

Anyway I think I got it wrong. If you want the highest total to be "1" then =RANK(B1, \$B\$1:\$B\$10, 0) as FRANKMAC rightly says.

Zak 22:26 09 Oct 03

Thank you all for your very quick replies. The spreadsheet works a treat.

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4