assigning points to rankings
 

I have ranked the times of races for 30 participants. The results are in a
column. I want to display the points given in the next column. The only
points given are for 1st through 6th place. 1st=6pts, 2nd=5pts, 3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt, 7th place through 30th place =0. Times,
therefore ranks, will change with each race, so this information needs to be
in every cell in that column.

NAME TIME RANK PTS
Joe 12.05 2 5
Mary 13.00 3 4
Sue 10.57 1 6
Sam 15.04 8 0

I figured out the rank formula, but I can't figure out how to assign the
points. Can someone help me, please?

assigning points to rankings
 

You could just use a lookup

=VLOOKUP(C7,{1,6;2,5;3,4;4,3;5,2;6,1;7,0},2)

where C7 is the rank

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Nan-C" wrote in message
...
I have ranked the times of races for 30 participants. The results are in a
column. I want to display the points given in the next column. The only
points given are for 1st through 6th place. 1st=6pts, 2nd=5pts, 3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt, 7th place through 30th place =0. Times,
therefore ranks, will change with each race, so this information needs to
be
in every cell in that column.

NAME TIME RANK PTS
Joe 12.05 2 5
Mary 13.00 3 4
Sue 10.57 1 6
Sam 15.04 8 0

I figured out the rank formula, but I can't figure out how to assign the
points. Can someone help me, please?

assigning points to rankings
 

Hi!

Try this:

Ranks are in column C.

=IF(C2="","",LOOKUP(C2,{1;2;3;4;5;6;7},{6;5;4;3;2; 1;0}))

Biff

"Nan-C" wrote in message
...
I have ranked the times of races for 30 participants. The results are in a
column. I want to display the points given in the next column. The only
points given are for 1st through 6th place. 1st=6pts, 2nd=5pts, 3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt, 7th place through 30th place =0. Times,
therefore ranks, will change with each race, so this information needs to
be
in every cell in that column.

NAME TIME RANK PTS
Joe 12.05 2 5
Mary 13.00 3 4
Sue 10.57 1 6
Sam 15.04 8 0

I figured out the rank formula, but I can't figure out how to assign the
points. Can someone help me, please?

assigning points to rankings
 

Thank you very much. It worked perfectly. I wish I had asked a few hours ago.

"Peo Sjobom" wrote:

You could just use a lookup

=VLOOKUP(C7,{1,6;2,5;3,4;4,3;5,2;6,1;7,0},2)

where C7 is the rank

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Nan-C" wrote in message
...
I have ranked the times of races for 30 participants. The results are in a
column. I want to display the points given in the next column. The only
points given are for 1st through 6th place. 1st=6pts, 2nd=5pts, 3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt, 7th place through 30th place =0. Times,
therefore ranks, will change with each race, so this information needs to
be
in every cell in that column.

NAME TIME RANK PTS
Joe 12.05 2 5
Mary 13.00 3 4
Sue 10.57 1 6
Sam 15.04 8 0

I figured out the rank formula, but I can't figure out how to assign the
points. Can someone help me, please?

assigning points to rankings
 

This worked, too. Thanks for your help.

"Biff" wrote:

Hi!

Try this:

Ranks are in column C.

=IF(C2="","",LOOKUP(C2,{1;2;3;4;5;6;7},{6;5;4;3;2; 1;0}))

Biff

"Nan-C" wrote in message
...
I have ranked the times of races for 30 participants. The results are in a
column. I want to display the points given in the next column. The only
points given are for 1st through 6th place. 1st=6pts, 2nd=5pts, 3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt, 7th place through 30th place =0. Times,
therefore ranks, will change with each race, so this information needs to
be
in every cell in that column.

NAME TIME RANK PTS
Joe 12.05 2 5
Mary 13.00 3 4
Sue 10.57 1 6
Sam 15.04 8 0

I figured out the rank formula, but I can't figure out how to assign the
points. Can someone help me, please?

assigning points to rankings
 

I you sort the list for best time first,
you could then us something like this


=IF(F6=1,7,IF(G5=0,0,G5-1))

where rank is in column F and Points are in Column G
then all you have to enter is a 1 for the top rank entrant
and the rest is automatic




"Nan-C" wrote in message
...
I have ranked the times of races for 30 participants. The results are in a
column. I want to display the points given in the next column. The only
points given are for 1st through 6th place. 1st=6pts, 2nd=5pts, 3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt, 7th place through 30th place =0. Times,
therefore ranks, will change with each race, so this information needs to
be
in every cell in that column.

NAME TIME RANK PTS
Joe 12.05 2 5
Mary 13.00 3 4
Sue 10.57 1 6
Sam 15.04 8 0

I figured out the rank formula, but I can't figure out how to assign the
points. Can someone help me, please?

assigning points to rankings
 

1st=6pts, 2nd=5pts, 3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt,
7th place through 30th place =0.

Perhaps another option with numbers in A1

=MOD(14,7+MAX(MIN(A1,7),0))

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Nan-C" wrote in message
...
I have ranked the times of races for 30 participants. The results are in a
column. I want to display the points given in the next column. The only
points given are for 1st through 6th place. 1st=6pts, 2nd=5pts, 3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt, 7th place through 30th place =0. Times,
therefore ranks, will change with each race, so this information needs to
be
in every cell in that column.

NAME TIME RANK PTS
Joe 12.05 2 5
Mary 13.00 3 4
Sue 10.57 1 6
Sam 15.04 8 0

I figured out the rank formula, but I can't figure out how to assign the
points. Can someone help me, please?

assigning points to rankings
 

You could also try

=MAX(0,7-C2)*(C20)

or even

=(7-C2)*(C20)*(C2<7)

...but what happens if you have ties? If you have 2 participants tied
for 1st place do they both get 6 points or share the 6 points for 1st
and the 5 points for 2nd, thereby getting 5½ points each?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=533096

assigning points to rankings
 

Dana,

=MOD(14,7+MAX(MIN(A1,7),0))

May I ask why you included the Max() function? The formula seems to work
just as well without it unless A1 is negative and as A1 is being generated
by a RANK() function surely it can never be negative.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Dana DeLouis" wrote in message
...
1st=6pts, 2nd=5pts, 3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt,
7th place through 30th place =0.

Perhaps another option with numbers in A1

=MOD(14,7+MAX(MIN(A1,7),0))

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Nan-C" wrote in message
...
I have ranked the times of races for 30 participants. The results are in
a
column. I want to display the points given in the next column. The only
points given are for 1st through 6th place. 1st=6pts, 2nd=5pts, 3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt, 7th place through 30th place =0. Times,
therefore ranks, will change with each race, so this information needs to
be
in every cell in that column.

NAME TIME RANK PTS
Joe 12.05 2 5
Mary 13.00 3 4
Sue 10.57 1 6
Sam 15.04 8 0

I figured out the rank formula, but I can't figure out how to assign the
points. Can someone help me, please?

assigning points to rankings
 

Cause I, ahhhh...never thought about that. :)0
Your right! The Rank function would never be negative.
Thanks for the catch. :)
If the op wanted to go this route, then perhaps just: =MOD(14,7+MIN(A1,7))

Thanks again.
--
Dana DeLouis
Windows XP, Office 2003


"Sandy Mann" wrote in message
...
Dana,

=MOD(14,7+MAX(MIN(A1,7),0))

May I ask why you included the Max() function? The formula seems to work
just as well without it unless A1 is negative and as A1 is being generated
by a RANK() function surely it can never be negative.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Dana DeLouis" wrote in message
...
1st=6pts, 2nd=5pts, 3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt,
7th place through 30th place =0.

Perhaps another option with numbers in A1

=MOD(14,7+MAX(MIN(A1,7),0))

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Nan-C" wrote in message
...
I have ranked the times of races for 30 participants. The results are in
a
column. I want to display the points given in the next column. The only
points given are for 1st through 6th place. 1st=6pts, 2nd=5pts,
3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt, 7th place through 30th place =0. Times,
therefore ranks, will change with each race, so this information needs
to be
in every cell in that column.

NAME TIME RANK PTS
Joe 12.05 2 5
Mary 13.00 3 4
Sue 10.57 1 6
Sam 15.04 8 0

I figured out the rank formula, but I can't figure out how to assign the
points. Can someone help me, please?