working with time in excel 2000

[scott]

trying to get a result for the following function

=180-(G2-H1)/3.4

where both g2 and h1 repesent the times 06:49.6 and 05:07.2 (minutes and
seconds) respectfully.
both 180 and 3.4 are general formated numbers representing the units for
heart beats per seconds.
why does the answer comes up 179.9994369 when it should be 175.3.
how can i format the cell or expression to show 175.3 or am i missing a
consant.

 

[JE McGimpsey]

In order for the result to be 175.3, (G2-H1)/3.4 has to equal 4.7, which
means (G2-H2) = 15.98.

6:49.6 is stored as 0.004740741 (times are stored as fractional days,
e.g., 409.6 seconds/(24*60*60 seconds)), and 5:07.2 = 0.003555556, so
the difference, 1:42.4 = 0.001185185, so I'm not sure how you came up
with your expected value.

 

[scott]

thanks, and sorry for the wrong expected value as i read from my note
incorrectly. none the less when i plug in your corrections, i got the value i
wanted and needed.

 

[Myrna Larson]

I get the same result as you did, and as JE did. As JE asked, how did you
manually calculate the result as 175.3?

I suspect the problem is with the units to be used for the numbers in G2 and
H1.

Let X represent the time difference, i.e. G2-H1

You say that 180-X/3.4 should equal 175.3, i.e

180-X/3.4 = 175.3

Solving that equation for X:

X/3.4 = 180-175.3
X/3.4 = 4.7
X = 3.4 * 4.7
X = 15.98

You've entered the data in G2 and H1 as times. Excel stores a time as a
fraction of a day, not as number of minutes or number of seconds. If you
remove the formatting from G2 and H1, you see the numbers are 0.004740741 and
0.003555556, i.e. about 4/1000 of a day and 3.5/1000 of a day.)

If you format the number 15.98 as a time, it's almost 16 *DAYS*, specifically
383 hours, 31 minutes and 12 seconds. I'm pretty sure the units aren't
supposed to be days. What units are you supposed to be using for the values in
G2 and H1?

The difference between your two times is 1 minute and 42.4 seconds. If the
formula is supposed to return 175.3, G2-H1 must equal 15.98. Your two times
differ by 1 minute + 42.4 seconds. That's 1.7067 minutes or 102.4 seconds.
Neither of those units is anywhere near 15.98, so the units would seem not to
be either minutes or seconds, either.

If I assume the times are supposed to be number of 1/10th minutes and change
the formula to

=180-(G2-H1)*14400/3.4

I get 174.98. That's getting closer to 175.3, but we're not there yet.