Calculations using Times in Excel 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Darryl_Neeley - 26 Sep 2007 18:18 GMT

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.

Reply to this Message

T. Valko - 26 Sep 2007 19:05 GMT

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?

Signature

Biff
Microsoft Excel MVP

> I'm currently creating a Physical Fitness Test roster for my work. I'm
> making
[quoted text clipped - 17 lines]
>
> Thank you.

Reply to this Message

Darryl_Neeley - 26 Sep 2007 19:14 GMT

Round down. Hence, if the time is 18:01, it should be 99 points. 18:15 would
be 98.

> What do you want to do if the time doesn't exactly match the times in the
> table?
[quoted text clipped - 23 lines]
> >
> > Thank you.

Reply to this Message

Bob Phillips - 26 Sep 2007 19:27 GMT

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

Signature

---
HTH

Bob

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

> Round down. Hence, if the time is 18:01, it should be 99 points. 18:15
> would
[quoted text clipped - 32 lines]
>> >
>> > Thank you.

Reply to this Message

T. Valko - 26 Sep 2007 19:30 GMT

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(A1>TIME(0,33,0),0,INDEX(D:D,MATCH(CEILING(A1,10/86400),G:G)))))

Signature

Biff
Microsoft Excel MVP

> Round down. Hence, if the time is 18:01, it should be 99 points. 18:15
> would
[quoted text clipped - 32 lines]
>> >
>> > Thank you.

Reply to this Message

Darryl_Neeley - 26 Sep 2007 19:38 GMT

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

> Actually, that would be rounding up.
>
[quoted text clipped - 41 lines]
> >> >
> >> > Thank you.

Reply to this Message

T. Valko - 26 Sep 2007 19:51 GMT

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.

Signature

Biff
Microsoft Excel MVP

> Thank you very much for your help. I've got my problem fixed now. Thank
> you,
[quoted text clipped - 52 lines]
>> >> >
>> >> > Thank you.

Reply to this Message