Please read the entire thread - I did some bouncing around.

One - create a lookup table
Ending
Age time
0 28
19 29
40 30
46 33

The age 18 is in my cell b3 and the 33 is in cell c6

Clients age is keyed in cell b10
Clients time is keyed in cell c10
Cell d10 has the max time =VLOOKUP(B10,B3:C6,2)
Points in cells e10
=IF(C10<=18,100,IF(C10D10,0,100-100*ROUNDUP((D10-C10)/(D10-18),1)))

Does this make sense? Make sure you get the males (or females) to work
first and then add the other gender

One way to add the other gender
Note since the ages are the same in
cell c3 =if(a10="m",28,31)
cell c4 =if(a10="m",29,32)
cell c5 =if(a10="m",30,33)
cell c6 =if(a10="m",33,36)

Understand?

Then the last thing you have to do is to make the "18" is cell e10 variable

Lets make cell c7 =if(a10="m",18,21)

then equation e10 then would be

=IF(C10<=C7,100,IF(C10D10,0,100-100*ROUNDUP((D10-C10)/(D10-C7),1)))

There are slightly shorter ways - but this works

This also assumes that you are doing one case at a time and the lookup table
is okay to change. If you need to keep both tables - we can fix that as
well.....
--
Wag more, bark less


"LoveExcelButFrustrated" wrote:

Ok, so the conditions are this:

Males 26 and under have a run time of 18 minutes or less for a perfect 100
points. For every 10 seconds after that you subtract 1 point (so that a run
time of 18:01 - 18:10 = 99 points and a run time of 23:00 = 70 points)
Anything 28:01 and over is failing.

Males 27 to 39 have same perfect score but 29:01 and over is failing.

Males 40 - 45 have until 30:01 to pass.

Males 46 and over have until 33:01 to pass.

That's one. The other conditions a

Females 26 and under run a 21:00 or faster for 100 points. Same subtraction
rate for points. 31:01 is failing point.

Females 27 - 39 have till 32:01 and females 40 - 45 have until 33:01 to pass.

Females 46 and over have until 36:01.

I have the beginning I think. Here is what I have so far that I could figure
out:

L8 is my Age block, M8 is my Gender block, and Y8 is my run time block. AA
is the run score block.

=(--(L8<27)*(M8="M"))*IF(Y8<=1800,100

I hope the beginning I have can help someone to help me lol. Thanks in
advance to anyone who can help this jumble out.