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,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 >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?

 Posted: Sat Apr 15 10:09:28 CDT 2006 Top
 worksheet functions >> assigning points to rankings You could also try =MAX(0,7-C2)*(C2>0) or even =(7-C2)*(C2>0)*(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?action=getinfo&userid=30486 View this thread: http://www.excelforum.com/showthread.php?threadid=533096

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? >> >> > >