More formula problems

[LoveExcelButFrustrated]

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 are:

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.

 

[Brad]

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(C10>D10,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(C10>D10,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.....

 

[Rick Rothstein \(MVP - VB\)]

How are you entering your times... without colons? You do realize you can't
do simple subtraction on numbers entered that way and get correct time
calculations, right? Anyway, let me know what your time entries actually are
so I can either use them as entered (with colons) or construct the
appropriate corrections for them (with no colons and assuming the digits
represent MMSS).

Rick

 

[Brad]

One correction to my formula

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

I haven't worked with time much and will defer to Rick - but with a few
examples that I did it seemed to work - but maybe I'll learn something as
well.

 

[Rick Rothstein \(MVP - VB\)]

Well, I have been holding on to this solution waiting for you to come back
to me with an answer to my question... but since you haven't, I'll give you
the "generalized" answer and you will have to substitute actual function
calls to replace the indicated values shown in angle brackets. This formula
should calculate the "run score" (assuming you use "F" for female in M8)...

=100-MAX(0,INT((<minutes>+<seconds>/60-17.834-3*(M8="F"))*6))

Rick

 

[Brad]

Rick, what is 17.834 for?

 

[Rick Rothstein \(MVP - VB\)]

It is approximately 10 seconds less than 18 minutes and is subtracted from
the runners time so that the interval calculations can be made.

Rick

 

[Brad]

Duh - I was doing one second not 10.

What (if anything)is wrong with my solution?