assigning points to rankings 

Author 
Message 
JamesThomas

Posted: Fri Apr 14 22:47:27 CDT 2006 
Top 
worksheet functions >> 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?
Excel162





Peo

Posted: Fri Apr 14 22:47:27 CDT 2006 
Top 
worksheet functions >> 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
>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?





Biff

Posted: Fri Apr 14 22:54:17 CDT 2006 
Top 
worksheet functions >> 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
>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?





NanC

Posted: Fri Apr 14 23:23:02 CDT 2006 
Top 
worksheet functions >> assigning points to rankings
Thank you very much. It worked perfectly. I wish I had asked a few hours ago.
> 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
>
>
> >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?
>
>
>





NanC

Posted: Fri Apr 14 23:25:02 CDT 2006 
Top 
worksheet functions >> assigning points to rankings
This worked, too. Thanks for your help.
> 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
>
> >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?
>
>
>





Verne

Posted: Sat Apr 15 00:55:40 CDT 2006 
Top 
worksheet functions >> 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,G51))
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
>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?





Dana

Posted: Sat Apr 15 09:37:47 CDT 2006 
Top 
worksheet functions >> 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
>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?





daddylonglegs

Posted: Sat Apr 15 10:09:28 CDT 2006 
Top 



Sandy

Posted: Sat Apr 15 13:39:56 CDT 2006 
Top 
worksheet functions >> 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
>> 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
>
>
>>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?
>
>





Dana

Posted: Sat Apr 15 13:59:21 CDT 2006 
Top 
worksheet functions >> 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
> 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
>
>
>
>>> 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
>>
>>
>>>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?
>>
>>
>
>






