Excel - Comparing Numbers

Zak 21:44 09 Oct 2003
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 2003

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 2003

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 2003

VoG is invisible again!

FRANKMAC 21:57 09 Oct 2003

jazzypop 21:58 09 Oct 2003

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 2003

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 2003

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

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

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?