Making calculations with times.

[Guest]

I'm currently creating a Physical Fitness Test roster for my work. I'm making
it to where as you fill in the information, it automatically puts the point
values into the corresponding cell.

I have three categories: Pullups, Crunches, 3-Mile Run. I have the Pullups
and the Crunches completed. Those were no problem. Simple calculations.

However, with the 3 mile run, I am having trouble finding the correct
function for what I need. The times will range from under 18:00 to 33:00.
18:00 and under run time will result in 100 points, whereas 33:00 run time
will result in 10 points, and over 33:00 will result in 0. The full list can
be found at http://usmilitary.about.com/od/marines/l/blfitmale.htm .

What I want is when I type the time into cell A1, cell A2 will automatically
display the correct amount of points for the run.

Thank you.

 

[Guest]

I pasted your chart into a worksheet in columns A. I put 18:15 in G4

then this formula

=IF(G4>TIME(28,0,0)+1,0,IF(G4<TIME(18,0,0),100,INDEX(A:A,MATCH(G4,D,1)+(INDEX(D,MATCH(G4,D,1),1)<>G4),1)))

returned 98 points

This assumes that your times look like minutes, but are being stored as
hours. It would only require slight adjustments if they are actually stored
as minutes.

If you happen to be near Quantico and need more help, I will be teaching a
class there tonight. In any event, you can Contact me/send a sample sheet to
(e-mail address removed)

 

[Dave D-C]

How about
Format rows 1 and 4: time HH:MM:SS
A4:C4 is 0:0:0 0:18:01 0:33:01
A5:C5 is 100 10 0
A2 is =HLOOKUP(A1,$A4:$C5,2,TRUE)
Then entering a time in A1 (B1 ..) gives the score in A2 (B2 ..)
D-C