Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.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? |
#2
Posted to microsoft.public.excel.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 "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? |
#3
Posted to microsoft.public.excel.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 "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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.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 "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? |
#7
Posted to microsoft.public.excel.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 "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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.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 "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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hlookup function | Excel Worksheet Functions | |||
Can this adjusted GPA formual be accomplised? | Excel Discussion (Misc queries) | |||
Excel 2003 xy chart - problem plotting more than 330 points | Charts and Charting in Excel | |||
giving points by a result (function) | Excel Worksheet Functions | |||
Newbie to charts question - projecting values between data points | Excel Discussion (Misc queries) |