roundup function rounding when not necessary.

[frostuzy]

I am working on a spreadsheet that has two sets of time in and time out
which I then total and roundup. On certian time frames namely 1:00 P
- 2:00 PM when rounded returnes 1:15 instead of 1:00. This onl
happens on PM times and for these hours (that I have found so far) 1-2
4-5, 7-8, 10-11.

Here this is the formula that I am using for roundin
"=ROUNDUP((C13)*96,0)/96"

I also took a screen shot (attached) on a sample test sheet that show
what I mean and the other formulas that I used on the sheet.

Also I am using Excel 97. If anybody has any ideas why this i
happening I would be grateful for any opinions, I am hoping it is jus
something simple that I am failing to see.

Thanks in advance for any assistence

Attachment filename: formulaexample.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=54400

 

[JE McGimpsey]

This is due to small rounding errors (since not all base 60 values can
be exactly represented in base 2). If all your times are in minutes, try:

=CEILING(A2-A1-"0:0:1","0:15")

 

[Ron Rosenfeld]

I am working on a spreadsheet that has two sets of time in and time outs
which I then total and roundup. On certian time frames namely 1:00 PM
- 2:00 PM when rounded returnes 1:15 instead of 1:00. This only
happens on PM times and for these hours (that I have found so far) 1-2,
4-5, 7-8, 10-11.

Here this is the formula that I am using for rounding
"=ROUNDUP((C13)*96,0)/96"

I also took a screen shot (attached) on a sample test sheet that shows
what I mean and the other formulas that I used on the sheet.

Also I am using Excel 97. If anybody has any ideas why this is
happening I would be grateful for any opinions, I am hoping it is just
something simple that I am failing to see.

Thanks in advance for any assistence.

Most folk will not download attachments due to the possibility of viral
infection.

However, it is likely that the number you are rounding is the result of a
formula, and not the result of merely entering those times. So the "true"
result of the formula is a very small amount greater than 1:00 due to rounding
errors inherent in the decimal to binary conversion, and the IEEE standard to
which Excel adheres.

Possibly you should first round to nearest minute, and then roundup:

=ROUNDUP(ROUND(C13*1440,0)/1440*96,0)/96

But post your formulas, input and output, here in TEXT and maybe we could make
some sense of it.


--ron

 

[JE McGimpsey]

The values don't have to be formulas.

Due to XL's precision limits, If you enter 13:00 in A1, 14:00 in A2 and
=A2-A1 in A3,

=A3*96 ===> 4.00000000000001

so ROUNDUP(A3*96,0) will return 5, not 4. Then when divided by 96 and
formatted as time, the result will be 1:15, not 1:00.

 

[Ron Rosenfeld]

The values don't have to be formulas.

Due to XL's precision limits, If you enter 13:00 in A1, 14:00 in A2 and
=A2-A1 in A3,

=A3*96 ===> 4.00000000000001

so ROUNDUP(A3*96,0) will return 5, not 4. Then when divided by 96 and
formatted as time, the result will be 1:15, not 1:00.

What I wrote was that "it is likely that the number you are rounding is the
result of a formula".

In your example, the number you are rounding is in A3.

A3 is the result of a formula -- hence my statement.


--ron

 

[JE McGimpsey]

A3 is the result of a formula -- hence my statement.

OK... since the OP had already stated that the error occurred from using
a cell that contained a formula, I interpreted your qualification
("likely...not the result of merely entering those times") to refer to
the inputs to that formula.

 

[frostuzy]

post your formulas, input and output, here in TEXT and maybe we coul
make
some sense of it.


--ron [/B]

Thanks for the tips folks here is a sample of what I am doing and
little explanation. This is a timesheet for people that are doin
child care. In this situation it is possible that people could d
child care twice in one day (if a child leaves to go to school, o
other appointments). Therefore in one column I have a time in, tim
out, total then time in, time out, total, then I sum the two total
then round to the nearest quarter hour. This is how the proces
looks:

A1=> time in Off
A2=> time out Off
A3=> total 0:00 <="=IF(ISTEXT(A1),0,A2-A1+IF(A1>A2,1))"
A4=> time in 1:00 PM
A5=> time out 2:00 PM
A6=> total 1:00 <="=IF(ISTEXT(A4),0,A5-A4+IF(A4>A5,1))"
A7=> total time 1:00 <="=SUM(A3,A4)"
A8=> billed time 1:15 <="=ROUNDUP((A7)*96,0)/96"

Thanks again and I am going to try out the other two function
discussed in the previous responses and report back

 

[frostuzy]

Possibly you should first round to nearest minute, and then roundup:

=ROUNDUP(ROUND(C13*1440,0)/1440*96,0)/96

--ron [/B]

Ron I tried this and it worked correctly on the times that wer
returning the erronious rounded times before. I think I can see ho
this is working by rounding to the minute then rounding up, but I a
still shakey on the process. If anyone could talk me through thi
process to help me better understand I would be grateful. Thank yo
for all your help and the replies

 

[Ron Rosenfeld]

Ron I tried this and it worked correctly on the times that were
returning the erronious rounded times before. I think I can see how
this is working by rounding to the minute then rounding up, but I am
still shakey on the process. If anyone could talk me through this
process to help me better understand I would be grateful. Thank you
for all your help and the replies.

The problem is that your formula does not return the result you think it does.
In other words, if you subtract 1PM from 2PM, your display shows 1 hour.

But since many decimal numbers cannot be represented precisely in a binary
notation (much the same as 1/3 cannot be represented exactly as a decimal), the
mathematical operation results in a number that is very slightly more than you
expect it to be (like in the 15th+ decimal place). Hence the ROUNDUP sees that
this number is very slightly more than 1 hr, and rounds it up to the next
quarter.

By first rounding to the nearest minute, one gets rid of the noise.



--ron