rounding is not consistant

  • Thread starter Thread starter Mr.B
  • Start date Start date
M

Mr.B

I have asked this before, but I'm still having trouble understanding this.
For example I work in Pharmacuticles and I need to know how long a drug has
been out of the cold room. (I need to record this in hours rounding to the
nearest half hour) the exact rounding method is as follows.
00-14 minutes rounds down to 0.0
15-29 minutes rounds up to 0.5
30-44 minutes rounds down to 0.5
45-59 minutes rounds up to 1.0

this is the problem
A24= 12/10/08 7:00 (out time)
A23= 12/10/08 15:15 (in time)

=ROUND((A24-A23)*24*2,0)/2
results in 8.5 hours (this is correct)
so why is it if A24 gets moved 1 hour (8:00) the result moves 1.5 hours
(7.0)
SOMEONE PLEASE HELP!!!!
Thanks
 
I think you mixed in and out times. If out time is really in A24 and in time
is in A23 then time out of the cold room is not A24-A23 but A23-A24.

=ROUND((A23-A24)*24*2,0)/2

returned for me the right 7.0 with 8:00 in A24.

Regards,
Stefi

„Mr.B†ezt írta:
 
I'm sorry I did mix up the cells A24 is the in time and A23 is the out time
but am I doing something wrong. using the formula below I don't understand
why if cell A23 is 07:00 the results are 8.5
and just moving the the time 1 hour to 08:00 it results in 7.0 (1 and a half
hour)
Am I just confused or is something not right here.
it seems that by moving just the hour it would only move the amount of hours
in the results.
thanks
 
Hi,

Computers work in binary, we work in decimals

The difference between 7 and 15:15 is exactly 0.34375
but the difference between 8 and 15:15 is approximently
0.302083333328483

Try:
ROUND((ROUND(A2,3)-ROUND(A1,3))*24,0)
where A2 is the time in and A1 is the time out

Here is everything you need to know about this issue (and more):

http://support.microsoft.com/kb/78113/en-us
http://support.microsoft.com/kb/42980
http://support.microsoft.com/kb/214118
http://www.cpearson.com/excel/rounding.htm
http://docs.sun.com/source/806-3568/ncg_goldberg.html

If this helps, please cliick the Yes button.

Cheers,
Shane Devenshire
 
Back
Top