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:
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: http://www.interactiveds.com.au/software/CombineArray.xls
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:
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.