I don’t know where to start with this one.

To recap, I have two variables dimmed as Double. They both apparently equal 37,089.60, but they don’t equal each other. What’s the next step?

Skip to content
# Daily Dose of Excel

## Haphazardly Posted Excel Information and Other Stuff

# WTF

##
17 thoughts on “WTF”

### Leave a Reply

I don’t know where to start with this one.

To recap, I have two variables dimmed as Double. They both apparently equal 37,089.60, but they don’t equal each other. What’s the next step?

How about precision? What if you formatted display with max decimal places we have? Or what if you truncated value to whatever significant figures you want and then compare the values?

Dick –

?dParentSubtotal – dTotalRepLineSales

…mrt

It’s the old “floating-point math is not exact” problem see http://www.cygnus-software.com/papers/comparingfloats/comparingfloats.htm

You’ll get away with comparing doubles most of the time but you’ll eventually hit the problem, even a change of compiler (as might happen when upgrading Excel to another version)can cause what were once working equivalents to fail.

The only safe way is to either use Currency data type or to always perform a conversion to Longs (* by 100 or 1000 etc. depending on decimal precision required) – Currency is in effect a 64bit integer * 10000.

Tom

I’m guessing Tom’s most likely to be right here.

What happens if you do this:

Round(dparentsubtotal, 2) = Round(dtotalreplinesales,2)

?

Another option is dim your variable as variants and then use cdec to convert to the decimal data type.

?dparentsubtotal – dtotalreplinesales

-7.27595761418343E-12

You guys are really smart. Thanks.

I’ve run into this problem before trying to balance a spreadsheet for invoicing and balancing against the remittance advice from the customer. Rounding to the significant digits solved the problem.

I found the “same” numbers were off in the 5th or 6th decimal place.

Dick –

Easy fix…change them to singles.

Change ’em back if you have to.

…mrt

I wouldn’t coerce them into integers… That is like saying you only care if the values are within +/- 1 of each other. (Which may be what you want, but suppose you really care whether they are within a penny of each other. Dick, I’m assuming that your values are cash, since you’re an accountant.)

Instead, I would set a tolerance, and check whether the values are within that interval. E.g.:

Const tol = 0.0001

If Abs(var1 – var2) < tol Then

‘The variables are approximately equal…

‘Do stuff

Else

‘They’re not equal…

‘Do different stuff

End if

Hi Matt –

I wrote singles, you read integers. CSng(var) might still not work though.

…mrt

I’d go with Matt H. Computers work in binary not decimal so this problem raises its ugly head every now and again when we force them to represent numbers as decimals. Set an acceptable tolerance and move on…

I think you are putting our collective leg. But, just in case you are not, this is a long standing problem with binary computers (just as the decimal system has a problem with certain fractions like 1/3).

When working with real numbers (represented in VBA as Single or Double) check if the absolute difference between the 2 numbers is less than some acceptable threshold. I often modularize this with a a RealEqual function

Function RealEqual(A As Double, B As Double, _

Optional Threshold As Double = 0.00000001) As Boolean

RealEqual = Abs(A – B) < = Threshold

End Function

— Dick Kusleika says:

July 02, 2010 at 9:50 am (Edit)

?dparentsubtotal – dtotalreplinesales

-7.27595761418343E-12

You guys are really smart. Thanks.

@Tushar: But the same behavior can happen with any other number, 0.5 for example. Here the numbers differ by 2^-37 but ?37089.6-2^-37 still returns 37089.6 (rounded to 15sf). The problem is that 15 digits isn’t enough to uniquely identify IEEE 754 doubles (kb/78113). In fact, 37089.6 can refer to any of 13 floating point numbers (centered around 0×40E21C3333333333).

There also seems to be a common misconception that computers can’t work precisely with decimal numbers since they mostly can’t be exactly represented in standard binary formats. Exact decimal floating point operations do exist and appear in the new 2008 IEEE standard. Excel can handle some simple decimal calculations, whereas VBA always seems to use the internal FPU function values eg ?10*0.1-1 equals 2^-54 in VBA but the equivalent in Excel equals zero.

Try this in excel cell

=1-0.4-0.4-0.2 (this equals 0)

now give =(1-0.4-0.4-0.2)=0 (this gives false)

Kedar: Try both formulas with and without parentheses (…), this can change decimal rounding. Agree it’s unintuitive, you can follow others’ suggestions above for workarounds.

Precision always is a problem for those of us coding and formulating for CAD products.

Often our problem stems from Textbox value to math value conversion. I always create a class to contain all the math formulas and doubles. I only ever round the formula results to display on the textbox. Changes to the textbox values are saved to the class as the full double entered for math purposes.

I NEVER use a textbox value for math formulas. They are only entry and display fields.

Interesting!

wouldn’t casting both of them string, only for comparison purpose, resolve the issue?

But i liked Matt H and Tushar Mehta’s solution i.e. having a treshold defined for decimal comparison.