|
help with IF statement
I need a cell to calculate points based on position finished example if
racer finished 1st then he gets 50 points. another racer finishes 2nd he gets 49 point and so on. Here what Ive tried but it only lets me enter up to 7 th place. I need it to go to 50th place at least. =IF(C5<1,"",IF(C5<2,"50",IF(C5<3,"49",IF(C5<4,"47" ,IF(C5<5,"46",IF(C5<6,"45",IF(C5<7,"44",IF(C5<8,"4 3")))))))) Thanks |
help with IF statement
Maybe just =(50-C5)+1
or if there are gaps in the number of points assigned then create a lookup table which holds the position and number of points assigned and then use a vlookup formula. See http://www.contextures.com/xlFunctions02.html Hope this helps Rowan "Kelly********" wrote: I need a cell to calculate points based on position finished example if racer finished 1st then he gets 50 points. another racer finishes 2nd he gets 49 point and so on. Here what Ive tried but it only lets me enter up to 7 th place. I need it to go to 50th place at least. =IF(C5<1,"",IF(C5<2,"50",IF(C5<3,"49",IF(C5<4,"47" ,IF(C5<5,"46",IF(C5<6,"45",IF(C5<7,"44",IF(C5<8,"4 3")))))))) Thanks |
help with IF statement
everything you wrote is over my head I dont have a clue, what I had so far
took hours to figure out. Ill paist =(50-C5)+1 in a cell and see what that does, but lookup and vlookup is a big huh to me. "Rowan Drummond" wrote: Maybe just =(50-C5)+1 or if there are gaps in the number of points assigned then create a lookup table which holds the position and number of points assigned and then use a vlookup formula. See http://www.contextures.com/xlFunctions02.html Hope this helps Rowan "Kelly********" wrote: I need a cell to calculate points based on position finished example if racer finished 1st then he gets 50 points. another racer finishes 2nd he gets 49 point and so on. Here what Ive tried but it only lets me enter up to 7 th place. I need it to go to 50th place at least. =IF(C5<1,"",IF(C5<2,"50",IF(C5<3,"49",IF(C5<4,"47" ,IF(C5<5,"46",IF(C5<6,"45",IF(C5<7,"44",IF(C5<8,"4 3")))))))) Thanks |
help with IF statement
Hi Kelly
If 1st place gets 50 points, 2nd 49 points, 3rd 48 points and so on then with the position in cell C5 the formula =(50-C5)+1 pasted in any other cell should give you your required answer. If 1st place gets 50 points, 2nd 49 points, 3rd 46 points, 4th 30 points etc (i.e there is no fixed pattern) then you will probably need to use the vlookup formula. Excel's help and the link I posted to Debra Dalgleish's site have pretty comprehensive advice on using a vlookup. Regards Rowan "Kelly********" wrote: everything you wrote is over my head I dont have a clue, what I had so far took hours to figure out. Ill paist =(50-C5)+1 in a cell and see what that does, but lookup and vlookup is a big huh to me. "Rowan Drummond" wrote: Maybe just =(50-C5)+1 or if there are gaps in the number of points assigned then create a lookup table which holds the position and number of points assigned and then use a vlookup formula. See http://www.contextures.com/xlFunctions02.html Hope this helps Rowan "Kelly********" wrote: I need a cell to calculate points based on position finished example if racer finished 1st then he gets 50 points. another racer finishes 2nd he gets 49 point and so on. Here what Ive tried but it only lets me enter up to 7 th place. I need it to go to 50th place at least. =IF(C5<1,"",IF(C5<2,"50",IF(C5<3,"49",IF(C5<4,"47" ,IF(C5<5,"46",IF(C5<6,"45",IF(C5<7,"44",IF(C5<8,"4 3")))))))) Thanks |
help with IF statement
All that trial and error and it ends up being =(50-C7)
can you help expand on that a bit =(50-C7),IF(B7<=20, "30") I tried this thinking anything over 20th would just get 30 points but it errors. "Rowan Drummond" wrote: Maybe just =(50-C5)+1 or if there are gaps in the number of points assigned then create a lookup table which holds the position and number of points assigned and then use a vlookup formula. See http://www.contextures.com/xlFunctions02.html Hope this helps Rowan "Kelly********" wrote: I need a cell to calculate points based on position finished example if racer finished 1st then he gets 50 points. another racer finishes 2nd he gets 49 point and so on. Here what Ive tried but it only lets me enter up to 7 th place. I need it to go to 50th place at least. =IF(C5<1,"",IF(C5<2,"50",IF(C5<3,"49",IF(C5<4,"47" ,IF(C5<5,"46",IF(C5<6,"45",IF(C5<7,"44",IF(C5<8,"4 3")))))))) Thanks |
help with IF statement
1st gets 50, 2nd get 49, 3rd get 48, 4th get 47. basicly if 50 is 1st place
point then every place under 1st would drop a point. I could also figure any one from 20th place to infinity could simply get 30 points see previous post. I tried =(50-C7),IF(B7<=20, "30") but it errors "Rowan Drummond" wrote: Hi Kelly If 1st place gets 50 points, 2nd 49 points, 3rd 48 points and so on then with the position in cell C5 the formula =(50-C5)+1 pasted in any other cell should give you your required answer. If 1st place gets 50 points, 2nd 49 points, 3rd 46 points, 4th 30 points etc (i.e there is no fixed pattern) then you will probably need to use the vlookup formula. Excel's help and the link I posted to Debra Dalgleish's site have pretty comprehensive advice on using a vlookup. Regards Rowan "Kelly********" wrote: everything you wrote is over my head I dont have a clue, what I had so far took hours to figure out. Ill paist =(50-C5)+1 in a cell and see what that does, but lookup and vlookup is a big huh to me. "Rowan Drummond" wrote: Maybe just =(50-C5)+1 or if there are gaps in the number of points assigned then create a lookup table which holds the position and number of points assigned and then use a vlookup formula. See http://www.contextures.com/xlFunctions02.html Hope this helps Rowan "Kelly********" wrote: I need a cell to calculate points based on position finished example if racer finished 1st then he gets 50 points. another racer finishes 2nd he gets 49 point and so on. Here what Ive tried but it only lets me enter up to 7 th place. I need it to go to 50th place at least. =IF(C5<1,"",IF(C5<2,"50",IF(C5<3,"49",IF(C5<4,"47" ,IF(C5<5,"46",IF(C5<6,"45",IF(C5<7,"44",IF(C5<8,"4 3")))))))) Thanks |
help with IF statement
If your place is in C7 then try: =IF(C719,30,(50-C7)+1)
Regards Rowan "Kelly********" wrote: 1st gets 50, 2nd get 49, 3rd get 48, 4th get 47. basicly if 50 is 1st place point then every place under 1st would drop a point. I could also figure any one from 20th place to infinity could simply get 30 points see previous post. I tried =(50-C7),IF(B7<=20, "30") but it errors "Rowan Drummond" wrote: Hi Kelly If 1st place gets 50 points, 2nd 49 points, 3rd 48 points and so on then with the position in cell C5 the formula =(50-C5)+1 pasted in any other cell should give you your required answer. If 1st place gets 50 points, 2nd 49 points, 3rd 46 points, 4th 30 points etc (i.e there is no fixed pattern) then you will probably need to use the vlookup formula. Excel's help and the link I posted to Debra Dalgleish's site have pretty comprehensive advice on using a vlookup. Regards Rowan "Kelly********" wrote: everything you wrote is over my head I dont have a clue, what I had so far took hours to figure out. Ill paist =(50-C5)+1 in a cell and see what that does, but lookup and vlookup is a big huh to me. "Rowan Drummond" wrote: Maybe just =(50-C5)+1 or if there are gaps in the number of points assigned then create a lookup table which holds the position and number of points assigned and then use a vlookup formula. See http://www.contextures.com/xlFunctions02.html Hope this helps Rowan "Kelly********" wrote: I need a cell to calculate points based on position finished example if racer finished 1st then he gets 50 points. another racer finishes 2nd he gets 49 point and so on. Here what Ive tried but it only lets me enter up to 7 th place. I need it to go to 50th place at least. =IF(C5<1,"",IF(C5<2,"50",IF(C5<3,"49",IF(C5<4,"47" ,IF(C5<5,"46",IF(C5<6,"45",IF(C5<7,"44",IF(C5<8,"4 3")))))))) Thanks |
help with IF statement
BIG THANKS
Seems to work Thanks again "Kelly********" wrote: All that trial and error and it ends up being =(50-C7) can you help expand on that a bit =(50-C7),IF(B7<=20, "30") I tried this thinking anything over 20th would just get 30 points but it errors. "Rowan Drummond" wrote: Maybe just =(50-C5)+1 or if there are gaps in the number of points assigned then create a lookup table which holds the position and number of points assigned and then use a vlookup formula. See http://www.contextures.com/xlFunctions02.html Hope this helps Rowan "Kelly********" wrote: I need a cell to calculate points based on position finished example if racer finished 1st then he gets 50 points. another racer finishes 2nd he gets 49 point and so on. Here what Ive tried but it only lets me enter up to 7 th place. I need it to go to 50th place at least. =IF(C5<1,"",IF(C5<2,"50",IF(C5<3,"49",IF(C5<4,"47" ,IF(C5<5,"46",IF(C5<6,"45",IF(C5<7,"44",IF(C5<8,"4 3")))))))) Thanks |
help with IF statement
You're welcome.
"Kelly********" wrote: BIG THANKS Seems to work Thanks again "Kelly********" wrote: All that trial and error and it ends up being =(50-C7) can you help expand on that a bit =(50-C7),IF(B7<=20, "30") I tried this thinking anything over 20th would just get 30 points but it errors. "Rowan Drummond" wrote: Maybe just =(50-C5)+1 or if there are gaps in the number of points assigned then create a lookup table which holds the position and number of points assigned and then use a vlookup formula. See http://www.contextures.com/xlFunctions02.html Hope this helps Rowan "Kelly********" wrote: I need a cell to calculate points based on position finished example if racer finished 1st then he gets 50 points. another racer finishes 2nd he gets 49 point and so on. Here what Ive tried but it only lets me enter up to 7 th place. I need it to go to 50th place at least. =IF(C5<1,"",IF(C5<2,"50",IF(C5<3,"49",IF(C5<4,"47" ,IF(C5<5,"46",IF(C5<6,"45",IF(C5<7,"44",IF(C5<8,"4 3")))))))) Thanks |
1 more and I think Im done
After all points are added and the total is in cell ac5 thru ac64, cell ae5
thru ae64 needs to rank the points with 265 being the highest posable and 0 being the lowest. if the total for example is 265 then the ranking is 1, 264 would be 2, 263 would be 3 and so on. I dont know what function to use. "Kelly********" wrote: BIG THANKS Seems to work Thanks again "Kelly********" wrote: All that trial and error and it ends up being =(50-C7) can you help expand on that a bit =(50-C7),IF(B7<=20, "30") I tried this thinking anything over 20th would just get 30 points but it errors. "Rowan Drummond" wrote: Maybe just =(50-C5)+1 or if there are gaps in the number of points assigned then create a lookup table which holds the position and number of points assigned and then use a vlookup formula. See http://www.contextures.com/xlFunctions02.html Hope this helps Rowan "Kelly********" wrote: I need a cell to calculate points based on position finished example if racer finished 1st then he gets 50 points. another racer finishes 2nd he gets 49 point and so on. Here what Ive tried but it only lets me enter up to 7 th place. I need it to go to 50th place at least. =IF(C5<1,"",IF(C5<2,"50",IF(C5<3,"49",IF(C5<4,"47" ,IF(C5<5,"46",IF(C5<6,"45",IF(C5<7,"44",IF(C5<8,"4 3")))))))) Thanks |
THANKS
You all are great thanks a lot
"Nick Hodge" wrote: Kelly Use The RANK function (In AE5 and copy down) =RANK(AC5,$AC$5:$AC$64) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "Kelly********" wrote in message ... After all points are added and the total is in cell ac5 thru ac64, cell ae5 thru ae64 needs to rank the points with 265 being the highest posable and 0 being the lowest. if the total for example is 265 then the ranking is 1, 264 would be 2, 263 would be 3 and so on. I dont know what function to use. "Kelly********" wrote: BIG THANKS Seems to work Thanks again "Kelly********" wrote: All that trial and error and it ends up being =(50-C7) can you help expand on that a bit =(50-C7),IF(B7<=20, "30") I tried this thinking anything over 20th would just get 30 points but it errors. "Rowan Drummond" wrote: Maybe just =(50-C5)+1 or if there are gaps in the number of points assigned then create a lookup table which holds the position and number of points assigned and then use a vlookup formula. See http://www.contextures.com/xlFunctions02.html Hope this helps Rowan "Kelly********" wrote: I need a cell to calculate points based on position finished example if racer finished 1st then he gets 50 points. another racer finishes 2nd he gets 49 point and so on. Here what Ive tried but it only lets me enter up to 7 th place. I need it to go to 50th place at least. =IF(C5<1,"",IF(C5<2,"50",IF(C5<3,"49",IF(C5<4,"47" ,IF(C5<5,"46",IF(C5<6,"45",IF(C5<7,"44",IF(C5<8,"4 3")))))))) Thanks |
| All times are GMT +1. The time now is 03:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com