Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Biff
 
Posts: n/a
Default Another rounding issue

Hi Folks!

Here's another rounding issue I just discovered:

A2 = 9:00 PM (not calculated, manually entered)
B2 = 10:00 PM (not calculated, manually entered)
C2 formula: =B2-A2+(B2<A2) Formatted as h:mm returns as expected 1:00
D2 formula: =IF(C2<1/24,1/24-C2,0)

Without preformatting cell D2 it defaults to GENERAL and unexpectedly
returns 3.46945E-17 which is the value_if_true argument of the IF function.

When formatted as h:mm it returns 0:00.

If cell D2 is formatted as h:mm and is tested to be = 0 FAILURE!

Here's how it breaks down:

=IF(0.0416666666666666<0.0416666666666667,1/24-C2,0)

=(B2-A2+(B2<A2))*24 returns 1 as expected. However, if formatted as number
to 15 decimal places returns 0.999999999999999. So, if that formula was
tested to return = 1 it would also fail.

But........ change:

A2 = 11:00 AM
B2 = 12:00 PM

(not drag filled either!) D2 returns 0 as expected and when tested for = 0
passes.

Hmmmm!

Just be aware!

Biff




  #2   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

Biff wrote:
Hi Folks!

Here's another rounding issue I just discovered:

A2 = 9:00 PM (not calculated, manually entered)
B2 = 10:00 PM (not calculated, manually entered)
C2 formula: =B2-A2+(B2<A2) Formatted as h:mm returns as expected 1:00
D2 formula: =IF(C2<1/24,1/24-C2,0)

Without preformatting cell D2 it defaults to GENERAL and unexpectedly
returns 3.46945E-17 which is the value_if_true argument of the IF function.

When formatted as h:mm it returns 0:00.

If cell D2 is formatted as h:mm and is tested to be = 0 FAILURE!

Here's how it breaks down:

=IF(0.0416666666666666<0.0416666666666667,1/24-C2,0)

=(B2-A2+(B2<A2))*24 returns 1 as expected. However, if formatted as number
to 15 decimal places returns 0.999999999999999. So, if that formula was
tested to return = 1 it would also fail.

But........ change:

A2 = 11:00 AM
B2 = 12:00 PM

(not drag filled either!) D2 returns 0 as expected and when tested for = 0
passes.

Hmmmm!

Just be aware!

Biff





Not an "anomaly" or "bug" though. Rather a mental (cognitive) trap we
easily fall into with relational tests (comparisons) regarding
fractions. ROUND(X,n) < ROUND(Y,n) is the remedy with added costs of
making two function calls.


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
  #3   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

The reason that you need to ROUND for this operation (as suggested by
Aladin) is that computers do not have infinite precision, and some
fractions cannot be exactly represented without it.

For example, consider a hypothetical computer that carries only 4
significant figures. Then 4/3 would evaluate to 1.333 and 1/3 would
evaluate to 0.3333. Because of finite precision, 4/3-1 would then
evaluate to 0.333 which is not exactly the same as what 1/3 evaluates to.

1/24 = 1/8*1/3, so you should expect finite precision effects. The same
thing can happen where you might not expect it since computers do math
in binary rather than decimal, so numbers involving 1/5 (and hence 1/10)
will be non-terminating binary fractions (that must be approximated)
even though they are terminating decimal fractions.

If you want to learn more about this, there are several Knowledge Base
articles on the subject

http://support.microsoft.com/kb/48606
Comparison of Values Does Not Return Correct Result

http://support.microsoft.com/kb/42980
(Complete) Tutorial to Understand IEEE Floating-Point Errors

http://support.microsoft.com/kb/165373
OFF97: Rounding Errors in Visual Basic For Applications

http://support.microsoft.com/kb/69333
How To Work Around Floating-Point Accuracy/Comparison Problems

http://support.microsoft.com/kb/214118
How to correct rounding errors in floating-point arithmetic

http://support.microsoft.com/kb/78113
Floating-point arithmetic may give inaccurate results in Excel

Jerry

Biff wrote:

Hi Folks!

Here's another rounding issue I just discovered:

A2 = 9:00 PM (not calculated, manually entered)
B2 = 10:00 PM (not calculated, manually entered)
C2 formula: =B2-A2+(B2<A2) Formatted as h:mm returns as expected 1:00
D2 formula: =IF(C2<1/24,1/24-C2,0)

Without preformatting cell D2 it defaults to GENERAL and unexpectedly
returns 3.46945E-17 which is the value_if_true argument of the IF function.

When formatted as h:mm it returns 0:00.

If cell D2 is formatted as h:mm and is tested to be = 0 FAILURE!

Here's how it breaks down:

=IF(0.0416666666666666<0.0416666666666667,1/24-C2,0)

=(B2-A2+(B2<A2))*24 returns 1 as expected. However, if formatted as number
to 15 decimal places returns 0.999999999999999. So, if that formula was
tested to return = 1 it would also fail.

But........ change:

A2 = 11:00 AM
B2 = 12:00 PM

(not drag filled either!) D2 returns 0 as expected and when tested for = 0
passes.

Hmmmm!

Just be aware!

Biff






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rounding numbers up or down boyshanks Excel Discussion (Misc queries) 13 April 8th 09 03:03 PM
how to get the rounding function off Cynthia A Excel Worksheet Functions 1 June 4th 05 03:36 AM
Percentage rounding error in charts Tracey Excel Discussion (Misc queries) 4 May 14th 05 04:01 AM
Banker's Rounding - need help! Somecallmejosh Excel Discussion (Misc queries) 3 January 20th 05 10:53 PM
How do I make Excel stop rounding off my numbers that are 16 digi. Aida Excel Discussion (Misc queries) 1 December 6th 04 05:34 PM


All times are GMT +1. The time now is 12:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"