Rounding Error when Calculating similar times

[Scotty]

I have been scouring the web trying to find out why there is a difference in
the way Excel does some rounding.

I am using this formula:
=ROUNDUP(IF(A1>B1,(B1+1-A1)*96,(B1-A1)*96),15)

I have it pasted to three sets of cells (C1, C2, C3)

My data is:
A1 = 11:00
A2 = 12:30

B1 = 23:00
B2 = 00:30

C1 = 10:00
C2 = 11:30

Cells A1, A2, B1, B2, C1, C2 have the format of hh:mm.

Cells C1, C2, C3 display the value out to 18 decimal places.

In each situation we have 1 hour and 30 minutes of difference. But the
value shown in the cells are:
C1 = 6.000000000000010000
C2 = 5.999999999999990000
C3 = 6.000000000000000000

Because of this difference in the way the rounding is accomplished the my
larger formula gives different answers.

My full formula is:
=IF(E30="CB", IF(((ROUNDUP(IF(B30>C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24)
<= 0.5), 1,
IF(AND(((ROUNDUP(IF(B30>C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24)<=2),(0.500001<=(ROUNDUP(IF(B30>C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24))),
2,
IF(B30="","",IF(C30="","",ROUNDUP(IF(B30>C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24)))),
IF(B30="","",IF(C30="","",ROUNDUP(IF(B30>C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24)))

It should show the time interval in a decimal format to two decimal places.

But the above full forulma gives these values:
C1 = 1.75 (incorrect)
C2 = 1.50 (correct)
C3 = 1.50 (correct)

Can you give me an idea how I can do a work around of this problem?

 

[JMB]

Some discussion on XL rounding errors here. Also, if you search this site
for rounding error, you should find several threads.

http://www.cpearson.com/Excel/rounding.htm

I would round to fewer decimal places. Do you require 15 decimal places of
precision??

Also, I don't see how your data can be laid out as you described. I am
assuming your intentions were:

A B
1 11:00 12:30
2 23:00 0:30
3 10:00 11:30


I believe you could shorten your fomula a little to:
=ROUNDUP(((A1>B1)+B1-A1)*96,15)

 

[Scotty]

The reason for the 15 decimal place in the rounding was to show how the
numbers are different. The actual calculation included further down used 0
for the rounding. That URL doesn't really answer the question either, but I
did search this site. I appoligise for not doing that to begin with.

I did find a solution to the problem. I figured I would post it here for
others to see.

More or less the solution was to multiple each number by 24 BEFORE I
subtracted them:
ROUNDUP(((A2*24)-(A1*24)),1)

My full calculation now looks like this:
=IF(E8="CB",IF(((ROUNDUP(IF(B8>C8,(((C8+1)*96)-(B8*96)),((C8*96)-(B8*96))),0)/96*24)
<= 0.5), 1,
IF(AND(((ROUNDUP(IF(B8>C8,(((C8+1)*96)-(B8*96)),((C8*96)-(B8*96))),0)/96*24)<=2),(0.500001<=(ROUNDUP(IF(B8>C8,(((C8+1)*96)-(B8*96)),((C8*96)-(B8*96))),0)/96*24))),
2,
IF(B8="","",IF(C8="","",ROUNDUP(IF(B8>C8,(((C8+1)*96)-(B8*96)),((C8*96)-(B8*96))),0)/96*24)))),
IF(B8="","",IF(C8="","",ROUNDUP(IF(B8>C8,(((C8+1)*96)-(B8*96)),((C8*96)-(B8*96))),0)/96*24)))

Thanks for your help.

 

[Jerry W. Lewis]

If you want this to be "bullet proof", you should multiply by 1440=24*60
(assuming times are ented to the minute, not the second) and round that to
zero decimal places, then do your subtractions and conversions.

Excel times are stored as decimal fractions of 24 hours. Most decimal
fractions have no exact binary representation and hence must be approximated.
Further precision is lost if dates are involved, since date/times are times
plus the number of days since 1900.

Jerry