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.

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.

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.

=208-(ROUND(A1*144,0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"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.

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.

Thank you very much for your help. I've got my problem fixed now. Thank you,
and take care.

"T. Valko" wrote:

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.

Typo:

assume your table is in the range D1:G91.
Pts in column G, times in column G.

Should be: Pts in column D, times in column G.

--
Biff
Microsoft Excel MVP


"Darryl_Neeley" wrote in message
...
Thank you very much for your help. I've got my problem fixed now. Thank
you,
and take care.

"T. Valko" wrote:

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.