Comparing floating point numbers

Some of the issues raised in this post:
arose when writing the functions for the previous post on combining arrays.  In particular, when values from the two arrays are “equal” the function should create one entry in the combined array, not two, and I wanted the user to have some control over the tolerance that would be regarded as equal.

The result was three new User Defined Functions (UDFs), EqualT(), LTEqualT(), and GTEqualT().  The input and criteria for the new functions are shown in the screenshot below:

Floating Point comparison UDFs, click for full size view

The basis of the function is to find the value abs(a/b -1), where b is the lesser of the two values, and a is the greater.  Is this value is less than the specified tolerance then the two values are treted as being equal.  If the tolerance is not specified it defaults to 1E-14.

The use of a relative tolerance raises a problem if both numbers are very close to the minimum values allowed by the floating point number system.  For this reason the numbers are treated as being equal if the absolute value of the difference is less than a constant, MinReal, currently set to 1E-300.  The code for the EqualT function is shown below, anad all code is available in the download file:

Private Const MinReal As Double = 1E-300
Private Const DefaultTol As Double = 0.00000000000001
Function EqualT(Value1 As Variant, Value2 As Variant, Optional Tol As Double = DefaultTol) As Boolean
' Test if Value1 is equal to Value2 within Tol
    If Abs(Value1 - Value2)         EqualT = True
        Exit Function
    End If

    If DiffRatio(Value1, Value2) < Tol Then         EqualT = True     Else         EqualT = False     End If End Function Function DiffRatio(Value1 As Variant, Value2 As Variant) As Double Dim BVal As Double, TVal As Double     If Abs(Value1) > Abs(Value2) Then
        BVal = Value1
        TVal = Value2
        BVal = Value2
        TVal = Value1
    End If
    DiffRatio = Abs((TVal / BVal) - 1)

End Function

Finally, in a comment on the previous post Lori Miller provided an on-sheet solution that will combine multi-column arrays in the same way as the UDF, other than that the precision of the comparison is fixed. The formulas are included in the spreadsheet, and the output is shown, compared with the CombineArray function in the screenshot below:

CombineArray UDF and on-sheet functions

Note that the on sheet formulas return values a 5 from the first array, and just over 5 from the second, whereas for the UDF results the tolerance has been set high enough for these two values to be treated as equal.

This entry was posted in Arrays, Excel, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

4 Responses to Comparing floating point numbers

  1. Georg says:

    Hi Doug, your DiffRatio function seems to cause trouble when one of the values is slightly negative and the other is slightly postive, for example. This may well happen if you look at the sum of forces acting on a part at rest, e.g. I’d like to propose to apply a second abs on the inner bracket: DiffRatio = Abs(Abs(TVal / BVal) – 1). But it’s difficult to obtain a logically stringent scheme combing absolute as well as relative differences for all magnitudes of the smaller value… which might even be exactly zero…


  2. dougaj4 says:

    Thanks Georg, I’ll have a look at it.

    I was actually in two minds as to whether I should bother with testing for differences in the 1E-300 range at all, because I couldn’t see a practical application where it would arise, but I decided I had better do it “properly”.

    I’m surprised there is not more on the Net about this. I would have thought that there would be a standard solution in VBA somewhere, but I couldn’t find anything.


  3. lhm says:

    Interesting observations. With repect to the worksheet method, since =5+1E-15=5 returns TRUE these two values fall within Excel’s default 15sf precision tolerance but it seems the RANK function treats the two values as distinct. To get the same results as the UDF you could adjust the formula to use a comparison based on Excel operations.

    One way would be to select G23 and :
    replace: RANK(G22,(A$12:A$18,D$12:D$16))-1
    with: COUNT(1/(IF({1,0},A$12:A$18,D$12:D$16)>G22))
    then array enter and fill down.
    To adjust the precision you could add an appropriate value eg G22+1E-14.


  4. Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s