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!

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!

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!

Scoring three groups, each with 6 levels
 

You have closed off each IF() statement. Leave the final ) until the end of
the formula:

=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,10 ,5,1}),IF(C3=12,LOOKUP(J3,{0,12.5,12.51,12.81,13.1 1,13.41,14},{25,25,20,15,10,5,0}))))

By the way you have no FALSE component and so if C3 is none of the values
you will get a FALSE returned.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Hoytmedic" wrote in message
...
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!

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!

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 -