Newton Excel Bach, not (just) an Excel Blog

Some of the issues raised in this post:
https://newtonexcelbach.wordpress.com/2011/12/20/when-does-35-not-equal-35/
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: https://www.interactiveds.com.au/software/CombineArray.zip

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
    Else
        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.