Comparison of Double values

[qixinzhi]

I encounter a strange problem when comparing
two double numbers. The code is below and it
gave me "1.4>1.4"! Anything speical for such
simple comparison?

Thanks.


----------------------------------------------------------
Dim aa1 As Double
Dim aa2 As Double
Dim bb As Double

Dim aa As Double

aa1 = 1.36
aa2 = 0.04

aa = aa1 + aa2

bb = 1.4


If aa > bb Then
MsgBox aa & ">" & bb
ElseIf aa = bb Then
MsgBox aa & "=" & bb
Else
MsgBox aa & "<" & bb
End If
----------------------------------------------------------

[blindlizard]

Wow that is strange. I ran the code, and had the same problem.

[phinds]

no, there is absolutely nothing strange about it. I don't mean to be rude but you clearly do not understand how computers deal with numbers. YOU think in decimal. THEY think in binary.

YOU think that when you say 1.34 that the computer sees that as the same thing you have written down, but in fact it does no such thing. What it does is get the closest it can to your decimal fraction using binary fractions, and close is NOT exact. In this case it could, for example, get 1.339999999999 as the decimal equivalent of the closest binary fraction.

To compare floating point numbers, NEVER compare for an exact match, always do this:

if abs(var1 - var2) < .00000001 then
close enough for government work
else
they're substantially different
end if

[Blinky Bill]

That is a really strange thing you've discovered. Must be something to do with the implementation of the compare function that VB uses.

On another front, you might find it easier to do comparisons like this easier if you use the Select statement instead of nested ifs

Select Case aa
        Case Is < bb
            MsgBox aa & "<" & bb
        Case Is = bb
            MsgBox aa & "=" & bb
        Case Is > bb
            MsgBox aa & ">" & bb
End Select 

Makes it easier to read and debug.

Just thought you might want to know.

[phinds]

bump, in case you missed my entry. It has nothing to do with the comparison function as blinky hypothesized, it's an artifact of the difference between decimal fractions and binary fractions (see my previous entry in the thread)

[Blinky Bill]

phinds, i was typing my response when you posted yours, i believe you are correct in the reason for this phenomenon.

[qixinzhi]

Thanks for all the information.

[KayJay]

Very interseting. Just changed each double to a single and guess what?

Private Sub Command1_Click()
Dim aa1 As Single
Dim aa2 As Single
Dim bb As Single

Dim aa As Single

aa1 = 1.36
aa2 = 0.04

aa = aa1 + aa2

bb = 1.4


If aa > bb Then
MsgBox aa & ">" & bb
ElseIf aa = bb Then
MsgBox aa & "=" & bb
Else
MsgBox aa & "<" & bb
End If

End Sub 

[phinds]

and I take it that you find that strange in some way, yes? It really appears that you guys just don't get it. doubles and singles won't act the same way because they have different numbers of significant digits ("significant bits", actually) so they will have differing rounding errors, so to expect them to behave identically is just silly. It all has to do with the difference between decimal fractions and binary fractions and the fact that computers NEVER have an unlimited number of significant digits.

If computers worked in decimal, they would make the same kind of mistakes, just with different numbers, again because of rounding errors.

Take the simplest imaginable decimal number, 1.0 --- Now you would think, since you think in decimal, that this would have an exact representation in the computer. Well, you would be wrong. The binary fraction that the computer generates has a decimal equivalent of .99999999... or thereabouts, depending on whether you use a single or a double. REMEMBER, I'm talking about floating point numbers, not integers. The integer 1 is prefectly representable in binary, but the floating point number 1.0 is not.

Expressed another way, decimal and binary have a different set of rational numbers. That is, numbers that can be represented by proper fractons in one radix cannot necessarily be expressed as proper fractions in the other radix, and when you add to that the fact that there are a limited number of significant digits (or bits) then you get the results that you are seeing, that you seem to find so puzzling.

[swatty]

appending on phinds reply ,changed your code a bit

Dim aa1 As Double
   Dim aa2 As Double
   Dim bb As Integer
   
   Dim aa As Integer
   
   aa1 = 1.36
   aa2 = 0.04
   
   aa = Int((aa1 + aa2) * 100)
   
   bb = Int(1.4 * 100)
   
   
   If aa > bb Then
   MsgBox aa & ">" & bb
   ElseIf aa = bb Then
   MsgBox aa & "=" & bb
   Else
   MsgBox aa & "<" & bb
   End If 

try it and you'll see what is meant here

[phinds]

I REALLY don't seem to be getting my point across.

NEVER use a comparison for equality on two floating point numbers (whether single or double) because you WILL NOT ALWAYS GET WHAT YOU THINK IS THE CORRECT RESULT !!!

I just don't know how else to say it.

[brenaaro]

Originally posted by phinds
I just don't know how else to say it.

Try it without the attitude :-/

[CVMichael]

I agree with phinds, jecking for equality on floting point variables should always be done like "if abs(var1 - var2) < .00000001 then".

And this is NOT a problem with Visual Basic, it's just how the FPP (Floating-Point Processor) works...

In C/C++ you will get the same result when comparing two floating point variables...

[swatty]

Originally posted by phinds
I REALLY don't seem to be getting my point across.

NEVER use a comparison for equality on two floating point numbers (whether single or double) because you WILL NOT ALWAYS GET WHAT YOU THINK IS THE CORRECT RESULT !!!

I just don't know how else to say it.

What's your prob the code i gave demonstrates what you said.

I didn't comment on your reply it said appending, so adding a bit of code to make your remarks clearer.

Sorry if i pissed you of , maybe you can try to read other reply's more carefully before complaining.

[plenderj]

Decimal fractions ? What ?

I just can't believe that I'm afraid.
Do you have any sites I could refer to ...?

[phinds]

Swatty,

I apologize for my snippy comment. Your goal of shedding further light on a murky situation is admirable. I went off on a rant just because I once again saw the equality comparison and my point (as you clearly understood) was that it is a bad idea to use equality comparisons.

plenderj, I'll respond to your question next.

[swatty]

Originally posted by phinds
Swatty,

I apologize for my snippy comment. Your goal of shedding further light on a murky situation is admirable. I went off on a rant just because I once again saw the equality comparison and my point (as you clearly understood) was that it is a bad idea to use equality comparisons.

plenderj, I'll respond to your question next.

Accepted.

I hope qixinzhi can do something with it.

With the code i provided you can compare the values for the amount of decimal scale in the floating point.
Just multiply by 1...... , the points beeing the decimal scale.

Hope its more readable for anyone who want to use it.

[plenderj]

That's an urdan myth.
It used be said that 2/2 was .999999 but it rounded it up.
That's not true.

Quoting from the book "Logic and Computer Design Fundamentals - 2nd Edition Updated", by M.Morris Mano & Charles R. Kime,

The floating point number has two parts, one containing the sign of the number and a fraction (some-times called a mantissa) and the other designating the position of the radix point in the number and called the exponent.
For example the decimal number +6132.789 is represented in floating-point notation as :

Fraction      Exponent
+6.132789     + 04

Only the fraction adn the exponent are physically represented in computer registers; radix 10 and the decimal point of the fracion are assumed and are not shown explicitly. A floating-point binary number is represented in a similar manner, except that it uses radix 2 for the exponent.
For example, the binary number + 1001.11 is represented with an 8-bit fraction and 6-bit exponent as :

Fraction      Exponent
01001110      000100

So that which you refer to as a fraction, is actually just the number itself, without the decimal point.
There is no rounding.

I believe this issue with VB not comparing correctly is a different problem completely.

[phinds]

plenderj, I don't know of any web site, but a little simple thought will allow you to follow what I'm explaining, so here goes.

FIRST, my example of 1.0 was incorrect so if that explains the problem to you, read no more. . I knew the concept to be correct, so I was hasty in my example. I should have used 1/10th, which IS a correct example for what I'm talking about.

A human, looking at the fraction 1/10th would be reasonably inclined to believe that it would be hard to mess up something so simple, but as you will see from my example, the real world is counter-intuitive in this regard because of the conversion from decimal to binary and the limited number of significant digits (bits).

Forgetting totally about binary for a moment, just think about the fact that foating point numbers are stored in a format that dictates that the precision part is always stored as a decimal fraction with the first significant digit immediately to the right of the decimal point. Thus 1/10th is stored as .1 x 10^1. In decimal, that's not a problem because the fraction 1/10th is a rational number.

Now lets move that over to binary. Once again, we have the situation that the precision part is stored as a binary floating point number with the precision stored with the first significant digit immediately to the right of the decimal point ("binary point", actually, in this case). So, if we wanted to store the number that we think of in decimal as .5, which we would store in decimal as .5 x 10^0, we store it in binary as .1 * 2^0.

This works just fine in binary, because the number 1/2 is a rational number in binary. That is, it can be expressed as a proper fraction in that radix system.

NOW comes the part that gets confusing, and that's to do the decimal number .1 (one tenth) as a binary floating point number which we CANNOT do precisely because 1/10th is not rational in binary. If you do the longhand division in binary, you'll see that when you divide 1 by 1010, you get an irrational, and in fact infinitely repeating, floating point number that looks like:

.00011001100110011001100 forever (... 1100 ...)

this gets stored as a binary floating point number as

.110011001100 ... * 2^3

and if you carry that out to 24 significant bits and then turn it back into decimal, you get something like .0999999999, which is obviously close to .1, but not quite. As an infinite series, it converges on .1, so if computers had infinite significant bits, then there would be no problem although they'd need infinite speed to go along with it, else things would slow down a bit :-)

The point is that computers don't store floating point numbers the way many users think they do and that leads to the kind of confusion that started this thread.

[phinds]

our paths crossed, so I'll just add this. The "rounding" you refer to would as you say not take place if it we were dealing only with numbers that are rational fractions in radix 2, but as I pointed out, this is not the case, so there IS rounding because of the limited number of significant digits (bits).

[phinds]

Ah, I see an even better way of saying what I'm trying to say. WHOLE numbers don't have the problem because they never require rounding. It's only fractions that cause the problem, as explained in my example, so your statement is correct, but ONLY for whole numbers.

[plenderj]

But the fact is that that which is referred to as the "fraction" is just the number stripped of the decimal point.
Rounding would only occur towards the end of the number to make it fit into the required "fraction" section.

[Muddy]

you can use the decimal data type if you want to avoid this type of behavior. The following changes make the code snippet work:

Dim bb As Double

Dim aa As Double

aa1 = 1.36
aa2 = 0.04

aa = aa1 + aa2

bb = 1.4


If CDec(aa) > CDec(bb) Then
MsgBox aa & ">" & bb
ElseIf CDec(aa) = CDec(bb) Then
MsgBox aa & "=" & bb
Else
MsgBox aa & "<" & bb
End If 

[phinds]

Rounding would only occur towards the end of the number to make it fit into the required "fraction" section

you are quite correct. Is it then your conclusion that since the rounding occurs way out in the less significant digits (or bits) that the equality should work? I think you are missing my point still. When you say

that which is referred to as the "fraction" is just the number stripped of the decimal point

you seem to be implying that this is true for fractions, but as I have demonstrated, it is not. You are taking a statement which is true for whole numbers and applying it to fractions, where is is NOT true and yet you still seem to believe it is true.

The point is that decimal fractions will NOT always store precisely in binary. How about decimal fractions in a decimal computer? Well, suppose we HAD a decimal computer and wanted to add 1/9 plus 8/9 and compare the result to 1.0 --- WE WOULD GET INEQUALITY. Think it through.

[plenderj]

But the fact remains we're not adding 1/9 and 8/9.
We're specifying the exact number that we want to use.

For a number like 1.4, there is no rounding to be done.
1.4 is just 1.4

The fraction part, ie. 14 fits easily for a fraction.
The exponent would then be 1 or something.


For for any floating point value n, the fraction part is simply removing the decimal place from that value.
The IDE will automatically round the fraction part of the decimal value for you, so the CPU would only end up being given the exact same value you're looking at on the screen

[wrack]

Option Explicit

Private Sub Command1_Click()
    Dim aa1 As Double
    Dim aa2 As Double
    Dim bb As Double
    
    Dim aa As Double
    
    aa1 = 1.36
    aa2 = 0.04
    
    aa = CDbl(aa1) + CDbl(aa2)
    
    aa = Format(aa, "0.00")
    
    bb = 1.4
    
    bb = Format(bb, "0.00")
    
    If CDbl(aa) > CDbl(bb) Then
        MsgBox aa & ">" & bb
    ElseIf CDbl(aa) = CDbl(bb) Then
        MsgBox aa & "=" & bb
    Else
        MsgBox aa & "<" & bb
    End If
End Sub 

Cheers...

[phinds]

plenderj, if your point were correct, and it is not, then this thread would never have started in the first place. It started because, as I continue to repeat, decimal fractions do not always store exactly in binary. If they did, and your point were correct, then the problem that started this thread would never have occurred. I have explained why and rather than look at my explanation in the detail it apparently requires, you just keep saying that it isn't true. Math is math, and if you do the math, you'll see that I have given a correct expanation.

[plenderj]

I don't agree with that you're saying, and simply saying that this problem wouldn't exist if what you're saying was not true is not true either.

I believe that there is a different, yet unknown issue at hand.

[Muddy]

The interesting thing about this particular problem (to me at least) is that when you debug run the code, both aa and bb show to be exactly 1.4.

When its the typical VB rounding problem associated with the way computers store numbers you usually see something like

aa=1.400000000000000000000001
bb=1.4

in the debugger.

Strange problem indeed ...

[wrack]

Originally posted by wrack

Option Explicit

Private Sub Command1_Click()
    Dim aa1 As Double
    Dim aa2 As Double
    Dim bb As Double
    
    Dim aa As Double
    
    aa1 = 1.36
    aa2 = 0.04
    
    aa = CDbl(aa1) + CDbl(aa2)
    
    aa = Format(aa, "0.00")
    
    bb = 1.4
    
    bb = Format(bb, "0.00")
    
    If CDbl(aa) > CDbl(bb) Then
        MsgBox aa & ">" & bb
    ElseIf CDbl(aa) = CDbl(bb) Then
        MsgBox aa & "=" & bb
    Else
        MsgBox aa & "<" & bb
    End If
End Sub 

Cheers...

if u use the approach I have used u will get the right result and I always use this thing...even though this was the first time I found that this is also possible with VB...

I was stumped...but I am glad that I am using the right method or atleast its a right method I think...

Cheers...

[Leather]

Here you go - Straight from MSDN:

'-----------------------------------------------------------------------------------
The Floating-Point Data Types
VBA provides two floating-point data types, Single and Double. The Single data type requires 4 bytes of memory and can store negative values between -3.402823 x 1038 and -1.401298 x 10-45 and positive values between 1.401298 x 10-45 and 3.402823 x 1038. The Double data type requires 8 bytes of memory and can store negative values between -1.79769313486232 x 10308 and -4.94065645841247 x 10-324 and positive values between 4.94065645841247 x 10-324 and 1.79769313486232 x 10308.

The Single and Double data types are very precise—that is, they allow you to specify extremely small or large numbers. However, these data types are not very accurate because they use floating-point mathematics. Floating-point mathematics has an inherent limitation in that it uses binary digits to represent decimals. Not all the numbers within the range available to the Single or Double data type can be represented exactly in binary form, so they are rounded. Also, some numbers can't be represented exactly with any finite number of digits—pi, for example, or the decimal resulting from 1/3.

Because of these limitations to floating-point mathematics, you may encounter rounding errors when you perform operations on floating-point numbers. Compared to the size of the value you're working with, the rounding error will be very small. If you don't require absolute accuracy and can afford relatively small rounding errors, the floating-point data types are ideal for representing very small or very large values. On the other hand, if your values must be accurate—for example, if you're working with money values—you should consider one of the scaled integer data types.

'----------------------------------------------------------------------------------

Proof, if ever it were needed of phinds statements.

[plenderj]

That's not proof of his statements.

"...some numbers can't be represented exactly with any finite number of digits—pi..."

The fact is though that we're specifying a precise value.
1.4

We're not telling VB to work with 1.4444444444444444444444444, but rather 1.4

If it were that long value, then it would be rounded.
But 1.4 fits into the range for the single and double datatypes, so it wouldn't need to be rounded

[Muddy]

Originally posted by plenderj
That's not proof of his statements.

"...some numbers can't be represented exactly with any finite number of digits—pi..."

The fact is though that we're specifying a precise value.
1.4

We're not telling VB to work with 1.4444444444444444444444444, but rather 1.4

If it were that long value, then it would be rounded.
But 1.4 fits into the range for the single and double datatypes, so it wouldn't need to be rounded

could it be that the accurracy is being lost with the 1.36 assignment before the math operation???

[plenderj]

Well I mean, for nearly whole numbers, its going to be nearly perfectly accurate.
We're talking about small roundings when we get to trivially small differences...

[Leather]

Well I mean, for nearly whole numbers, its going to be nearly perfectly accurate.

So

nearly perfectly accurate

is not the same as accurate.

[plenderj]

Yeah but in this case we're not even rounding.
Its just 1.4

There is no rounding to be done.

[KayJay]

Well apparently it was I who made Phinds go beserk

So let me do it again.

The attachment please Phinds..........

Why is it so? Why are there any equalities at all? and even if there are to any equalities why is it apparently so random?

[nemaroller]

These are all good explanations, but still, this code raises questions.... the binary equivalents are still equal..

Private Sub Command1_Click()
Dim aa1 As Double
Dim aa2 As Double
Dim bb As Double

Dim aa As Double

aa1 = 1.36
aa2 = 0.04

aa = aa1 + aa2

bb = 1.4
Debug.Print ("bb= " & Num2Bin(bb))
Debug.Print ("aa1=" & Num2Bin(aa1))
Debug.Print ("aa2=" & Num2Bin(aa2))
Debug.Print ("aa= " & Num2Bin(aa))
Debug.Print ("1.4=" & Num2Bin(1.4))
If aa > bb Then
MsgBox aa & ">" & bb
ElseIf aa = bb Then
MsgBox aa & "=" & bb
Else
MsgBox aa & "<" & bb
End If
End Sub


Public Function Num2Bin(ByVal q As Variant, _
Optional ByVal Precision As Integer = 13) As String

'Declarations
Dim ln2 As Double 'Cache the value of Log(2)
Dim sResult As String 'Temp variable to hold the result
Dim fStart As Boolean 'Flag to indicate if we have started the number
Dim i As Long
Dim l As Long
Dim qL As Long, qD As Double

'Implementation
If IsNumeric(q) Then

'Cache this value, it's very useful!
ln2 = Log(2)

'Don't use Int(...), as this limits the range to integers
i = Log(q) / ln2

If i > 30 Then
'Overflow

Err.Raise 6, "Num2Bin", "Overflow"

Else

'Bitwise operators use CLng on the operands
'e.g. 0.75 And 1 = 1
'To get around this, use a Long copy of q
'and check for rounding up
qL = CLng(q)

If qL > q Then qL = qL - 1

Do While i >= 0

l = Exp(i * ln2)

If (qL And l) Then
  sResult = sResult & "1"
  qL = qL - l
  q = q - l
  'Have started the number
  fStart = True
ElseIf fStart Then
  'Do not write leading zeros
  'This is needed because CLng(Log(q) / Log(2))
  'may round up.
  sResult = sResult & "0"
End If

i = i - 1

Loop

'If we haven't got a value yet, the integer part is 0
If sResult = vbNullString Then sResult = "0"

If q > 0 And Precision > 0 Then
'Need to deal with fractional part
sResult = sResult & "."
i = -1

'Convert q to a double
qD = CDbl(q)

'Stop when q=0 or have reached max precision
Do While qD > 0 And Precision > 0
qD = qD * 2
If Int(qD) = 1 Then
sResult = sResult & "1"
qD = qD - 1
Else
sResult = sResult & "0"
End If

i = i - 1
Precision = Precision - 1
Loop
End If

Num2Bin = sResult
End If
Else
'q is not numeric
Err.Raise 13, "Num2Bin", "Type Mismatch"
End If
End Function 

[phinds]

OK guys, I'll give it one more try. I think we're at the point now where egos have gotten in the way of objectivity so if everyone could just take a deep breath and look at the math for a minute, you'll see the point.

But first:

That's not proof of his statements.

you are quite correct. The fact that Microsoft and I agree on something does not make either on of us right, and God knows I don't like being in agreement with Microsoft on anything. Still, in this particular case, what make BOTH of us right is the underlying math. You can expostulate all you want but at the end of the day math is math and there's nothing you can do to change the fundamental correctness of my explanation.

Well I mean, for nearly whole numbers, its going to be nearly perfectly accurate

again, you are correct, but as leather pointed out, my entire point was that close is not exact. If errors of the type being discussed in this thread were very large, then computers would be worthless for numeric computations, but as you have correctly stated and as Microsoft has also point out, the errors are quite small. Again, I'm talking about exactitude and you keep sliding off into correct statements about the smallness of the error and making incorrect statement about the fundamental issue concerning exactitude, which you clearly refuse to understand.

Yeah but in this case we're not even rounding.
Its just 1.4

There is no rounding to be done.

brings us back to the point that I have been, apparently unsuccessfully, trying to make. You are absolutely incorrect in this statement. The decimal fraction 1.4, when turned into binary, is an irrational number. The computer representation is 2^1 * .10110110110110110 ... (1100 forever) and when you turn that back into decimal, you do NOT get 1.4 although you get something very close, call it 1.399999999. Again, I would agree with you that this is quite a small error, but that's not the point of this thread. The point is that 1.4 is NOT the same as 1.39999999 and that is the fundamental reason for this thread having been started in the first place. You postulate that there is some other reason. I assure you that you will search in vain for any other reason. I have explained the reason.

For further help in understand this, I point back to my earlier statement about a decimal computer getting the wrong answer if you were to add 1/9 and 8/9. The point there is that 1/9 is irrational in decimal, so it will have rounding errors. It is .1111111111 ... forever and at some point you have to truncate it and that makes it incorrect.

kayjay, the answer to your question is very simple in concept and not a lot of use in practice. The answer is this: all numbers that are rational IN BINARY avoid rounding errors. All numbers that are irrational IN BINARY will always have rounding errors. The big problem we face is that it is very tedious to determine whether or not a number is rational in binary. Plenderj, for example, automatically assumed that since 1.4 is rational in decimal it is also rational in binary, a "fact" which I have gone to the trouble to show is not the case. I HATE doing long division and doing it in binary is a REAL pain but anyone who cares to do the math can see conclusively that 1.4 does NOT get represented in the computer as 1.4 but as 1.399999... which is why this whole discussion started in the first place.

If anyone has any further quesitons on this subject ... fugeddaboudit !!! I'm sick of the whole thing. I first encounted this problem in about 1963 and I've explained the whole thing so many times in my career that once more made little difference, but enough is enough.

[KayJay]

Thats really baffling nemaroller