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.

Any ideas please?

  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

SORRY YOUR REPLY WASN'T THERE WHEN I POSTED

  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.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Why ecommerce hasn't taken off on social media

New MacBook Pro 2016 review | MacBook Pro with Touch Bar review: Apple's expensive and powerful…