Rounding up causing abnormal/inconsistent results

[Meghan]

I need some help figuring out why my Excel timesheet formula is
occasionally returning abnormal results.
The formula calculates the total time (C1), then rounds the result up
to the nearest quarter hour (D1). (Formulas are below)
The formula works correctly most of the time, however, when I enter
certain time values, the results are not correct!
If I calculate time passed between 1:00 am and 2:00 am, the formula
returns the correct result: 1.00
However, if I calculate time passed between 1:00 pm and 2:00 pm, the
formula returns an incorrect result: 1.25
If I calculate time passed between 1:30 pm and 2:30 pm, the formula
returns the correct result: 1.00
I have done some testing, and found four time-spans that cause the
rounding error (below).
I am at a complete loss as how to explain the inconsistent results!
Can someone please help me? A thousand Thank You's!!!


(FORMULAS)
A1: hh:mm
B1: hh:mm
C1: =SUM((B1-A1)*24)
D1: =ROUNDUP((SUM(C1))/0.25,0)*0.25

(CORRECT RESULT)
A1: 1:00 am
B1: 2:00 am
C1: 1.00
D1: 1.00

(INCORRECT RESULT)
A1: 1:00 pm
B1: 2:00 pm
C1: 1.00
D1: 1.25

(Time-spans that cause inaccurate results)
1:00 pm - 2:00 pm
4:00 pm - 5:00 pm
7:00 pm - 8:00 pm
10:00 pm - 11:00 pm

 

[Mike]

C1: =B1-A1 with Format of hh:mm
D1: =ROUNDUP(ROUND(C1/"0:15",10),0)*0.25 with Format of General

 

[Meghan]

I appreciate the formulas (I haven't had a chance to test them
yet...), however, I was more curious as to *why* the problem was
occurring. My analytical brain can't sleep

 

[Ron Rosenfeld]

I am at a complete loss as how to explain the inconsistent results!
Can someone please help me? A thousand Thank You's!!!

Most likely, the problem you are seeing comes under the category of Rounding
Problems or Rounding Errors induced by the fact that XL (and other spreadsheet
programs) comply with an IEEE standard and have a limited precision.

If you do a MSKB search for this you will find some explanatory articles.

One workaround is to ROUND things in such a way so as not to impair the
precision which you require.

So, for example, you could change your equations:

C1: =ROUND((B1-A1)*24,n)
n can be any number depending on your desired precision. I would
suggest 3 or higher.

D1: =ROUNDUP(C1/0.25,0)*0.25

Note that in your original equations, the use of the SUM function is
superfluous.


--ron

 

[Greg Lovern]

I am at a complete loss as how to explain the inconsistent results!

Can someone please help me? A thousand Thank You's!!!

Hi Meghan,

The reason this is happening is because the numbers are converted from
decimal (base 10) to binary (base 2) so that the microprocessor can
perform the arithmetic, then the result is converted back from binary
to decimal for display in Excel. Since many numbers that can be
represented exactly in decimal can only be represented approximately
in binary (and vice versa), sometimes calculations are slightly
incorrect.

In all of your examples, '(SUM(C1))/0.25' is returning
4.00000000000001 instead of 4. '(SUM(C1))/0.25' can be simplified to
'C1/0.25', but it still returns 4.00000000000001.

Rounding is often helpful but not always bulletproof. The only
bulletproof way to avoid the problem is to never convert the numbers
to binary in the first place, and instead perform the calculations in
decimal.

Excel can't do that on it's own, but my Excel Add-In called
xlPrecision can. You can download the free edition of xlPrecision from
http://PrecisionCalc.com.

With xlPrecision, you can use this formula instead:

=ROUNDUP(xlpDIVIDE(C1,0.25),0)*0.25

Or if you prefer:

=ROUNDUP(xlpMULTIPLY(xlpDIVIDE(C1,0.25),0.25),0)

Those formulas both return 1 as desired (instead of 1.25), because
'xlpDIVIDE(C1,0.25)' correctly returns 4 instead of 4.00000000000001.
The reason 'xlpDIVIDE(C1,0.25)' returns 4 instead of 4.00000000000001
is because it never converts the numbers to binary. Instead, it does
the arithmetic in decimal.

Since xlPrecision does not use the microprocessor to perform its
arithmetic, it's slower than Excel's calculations. But it's fast
enough that you might not notice the difference.

Hope this helps,


Greg Lovern
http://PrecisionCalc.com
Get Your Numbers Right