making a rank equal a value

[emde84]

Hi,

Im running a competition where I need to allocate points. there are 21 people in the competition and I have used the rank function to quickly identify who has came first etc. What I'm wondering, is there a function that can be used to make the rank = a value

eg.
rank 1 would equal 21 points,
rank 2 would equal 20 points
rank 3 would equal 19 points
and so on..

Cheers

 

[PaddyD]

Hi - welcome to the board.

Simplest way would be to have a 'rank value' table - rank in one column, value for that rank in the next. You'd then use a vlookup to return the value for each rank:

=vlookup(a1,$b$1:$c$10,2,0)

where:

a1 is the rank you want a value for
b1:c10 is the two column table that holds ranks & values
2 tells the vlookup to return the value in the secxond column of the table
0 tells vlookup only to return exact mathes

...change references to suit

 

[emde84]

Hey, Cheers!

Very green at excel and have been playing around so that i can my comp just about automated. vlookup helped a lot!
much appreciated!!

 

[emde84]

Had another question, how can i make 1 = 20 and any other value = 0 ?

 

[PaddyD]

simpler.

if(rank(a1,$a$1:$a$10)=1,20,0)

 

[jimrward]

have i missed something here or does the following not suffice, negating the need for lookups

=22-(rank(a1,$a$1:$a$21)
or
=21-(rank(a1,$a$1:$a$21)

 

[PaddyD]

No, to extent that gives the answer. Perhaps, to exent that introducing novice to the wonders of vlookup() increases the summum bonum.