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.
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…
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.
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 :
then array enter and fill down.
To adjust the precision you could add an appropriate value eg G22+1E-14.
Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog