Actually, that would be rounding up.
Ok, assume your table is in the range D1:G91. Pts in column G, times in
column G.
Try this formula in A2:
=IF(A1="","",IF(A1<=TIME(0,18,0),100,IF(A1TIME(0, 33,0),0,INDEX(D:D,MATCH(CEILING(A1,10/86400),G:G)))))
--
Biff
Microsoft Excel MVP
"Darryl_Neeley" wrote in message
...
Round down. Hence, if the time is 18:01, it should be 99 points. 18:15
would
be 98.
"T. Valko" wrote:
What do you want to do if the time doesn't exactly match the times in the
table?
For example, entered time is 18:15. Should this time be awarded 99 pts or
98
pts?
--
Biff
Microsoft Excel MVP
"Darryl_Neeley" wrote in message
...
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.