Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Scoring three groups, each with 6 levels
I am using a worksheet to track a Baseball event. One of the sub-events is a
timed event. Scoring will be based on where you fall into the time breakdowns. But this is also broken into three age groups. What I need is to be able to enter a time, then have excel go through this process: If age=8 and time is less than 15.5, they get 25 pts, if between 15.51 and 16, 20 pts, etc down to 0 pt. Then, if age=10 and time is less than 12.75, 25 pt, 12.76-13.25, 20 pts, etc. Then again if age is 12 with a different set of times. How can I do this in one formula? Too many "IF"s! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Scoring three groups, each with 6 levels
one way:
=if(age=8,lookup(time,{lower values of time range}.{points}),if(age=10,lookup(...),If(age=12,l ookup(...))),"") See LOOKUP in HELP "Hoytmedic" wrote: I am using a worksheet to track a Baseball event. One of the sub-events is a timed event. Scoring will be based on where you fall into the time breakdowns. But this is also broken into three age groups. What I need is to be able to enter a time, then have excel go through this process: If age=8 and time is less than 15.5, they get 25 pts, if between 15.51 and 16, 20 pts, etc down to 0 pt. Then, if age=10 and time is less than 12.75, 25 pt, 12.76-13.25, 20 pts, etc. Then again if age is 12 with a different set of times. How can I do this in one formula? Too many "IF"s! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Scoring three groups, each with 6 levels
This is the formula I used, it worked UNTIL I added the second two age
groups....what did I miss? =IF(C3=8,LOOKUP(J3,{0,15.5,15.51,16.01,16.51,17.01 ,20.1},{25,25,20,15,10,5,2})),IF(C3=10,LOOKUP(J3,{ 0,12.75,12.76,13.26,13.76,14.26,15},{25,25,20,15,1 0,5,1})),IF(C3=12,LOOKUP(J3,{0,12.5,12.51,12.81,13 .11,13.41,14},{25,25,20,15,10,5,0})) See LOOKUP in HELP "Hoytmedic" wrote: I am using a worksheet to track a Baseball event. One of the sub-events is a timed event. Scoring will be based on where you fall into the time breakdowns. But this is also broken into three age groups. What I need is to be able to enter a time, then have excel go through this process: If age=8 and time is less than 15.5, they get 25 pts, if between 15.51 and 16, 20 pts, etc down to 0 pt. Then, if age=10 and time is less than 12.75, 25 pt, 12.76-13.25, 20 pts, etc. Then again if age is 12 with a different set of times. How can I do this in one formula? Too many "IF"s! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Scoring three groups, each with 6 levels
The easiest way is to set up a lookup table, and use VLOOKUP function for
your formula. "Hoytmedic" wrote: I am using a worksheet to track a Baseball event. One of the sub-events is a timed event. Scoring will be based on where you fall into the time breakdowns. But this is also broken into three age groups. What I need is to be able to enter a time, then have excel go through this process: If age=8 and time is less than 15.5, they get 25 pts, if between 15.51 and 16, 20 pts, etc down to 0 pt. Then, if age=10 and time is less than 12.75, 25 pt, 12.76-13.25, 20 pts, etc. Then again if age is 12 with a different set of times. How can I do this in one formula? Too many "IF"s! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Scoring three groups, each with 6 levels
Do you not have any 9 or 11 year olds? If so, your second IF could be
IF(C3<=10 ... etc, as you have already tested for 8, and your third could be IF(C3<=12 ... etc. Hope this helps. Pete On Jul 4, 5:46 pm, Hoytmedic wrote: This is the formula I used, it worked UNTIL I added the second two age groups....what did I miss? =IF(C3=8,LOOKUP(J3,{0,15.5,15.51,16.01,16.51,17.01 ,20.1},{25,25,20,15,10,5,*2})),IF(C3=10,LOOKUP(J3, {0,12.75,12.76,13.26,13.76,14.26,15},{25,25,20,15, 1*0,5,1})),IF(C3=12,LOOKUP(J3,{0,12.5,12.51,12.81, 13.11,13.41,14},{25,25,20,1*5,10,5,0})) See LOOKUP in HELP "Hoytmedic" wrote: I am using a worksheet to track a Baseball event. One of the sub-events is a timed event. Scoring will be based on where you fall into the time breakdowns. But this is also broken into three age groups. What I need is to be able to enter a time, then have excel go through this process: If age=8 and time is less than 15.5, they get 25 pts, if between 15.51 and 16, 20 pts, etc down to 0 pt. Then, if age=10 and time is less than 12..75, 25 pt, 12.76-13.25, 20 pts, etc. Then again if age is 12 with a different set of times. How can I do this in one formula? Too many "IF"s!- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scoring function not quite right? | Excel Worksheet Functions | |||
Auto Scoring | Excel Worksheet Functions | |||
Auto Scoring Sheet | Excel Discussion (Misc queries) | |||
how do i view all groups under excel in google groups | Excel Discussion (Misc queries) | |||
Scoring multiple cells | Excel Worksheet Functions |