Starting with why I think the formula from #16 is the better of the 2, it's less convoluted and has a higher degree of accuracy. Although the error is not noticeable on this data sample, the adjustments used to 'correct' the rounding would return incorrect results with other values, for example a value of .x33 would incorrectly round to .x00 instead of the expected .x50. Whilst the formula could be changed to rectify this, the formula from #16 doesn't use adjusted values so will not encounter the same problem. Also it's easier to change for different criteria.
Next, with your formula,
You had 1 small error, an extra closing paranthesis at the end of the section I've marked in red, which should have been at the end of the formula. The corrected version above should work, but you might still have some errors with .x25 values.
With certain types of calculation, there is a small degree of decimal inaccuracy, for example, subtracting a negative from another negative, a result of 0.025 would be returned as something like 0.02499999999999998, as you can see the difference is almost insignificant, but it is still a difference, so your formula wouldn't find the match.
To try and exlpain my formula,
=MROUND(ABS(C3-C2),LOOKUP(ROUND(MOD(ABS(C3-C2),0.1),3),{0,0.0251,0.075},{0.1,0.05,0.1}))
You already know how the ABS function works,
MOD(ABS(),0.1) gives the remainder of the ABS() result when divided by the defined value, in this case, 0.1, i.e. 0.0xx, now we only have to compare 2 digits, not 3, as an example, MOD(ABS(C17-C16),1) is the same as ABS(C17-C16)-ABS(INT(C17-C16)).
ROUND(MOD(ABS(...),0.1),3) rounds the current result value to 3 decimal places, this corrects any inaccuracy (0.024999999..) to elimintate possible errors.
See http://support.microsoft.com/kb/78113
At this point, the formula will hold a value of 0.000, 0.025, 0.050 or 0.075, lookup looks at the array {0,0.0251,0.075} for the highest value that is less than or equal, then uses the corresponding result from the second array as the factor for MROUND
0.00 to 0.025, lookup returns 0, corresponding value = 0.1 (MROUND(value,0.1)
0.0251 to 0.0749999, lookup returns 0.0251, corresponding value = 0.05 (MROUND(value,0.05)
0.075 to 0.099999, lookup returns 0.075, corresponding value = 0.1 (MROUND(value,0.1)
Hopefully that all makes sense.
In case it got missed before, I did mention in an earlier post that if required, there is a simple way to retain the result as negative where applicable,
=MROUND(ABS(C3-C2),LOOKUP(ROUND(MOD(ABS(C3-C2),0.1),3),{0,0.0251,0.075},{0.1,0.05,0.1}))*SIGN(C3-C2)
SIGN() simply returns 1 0 or -1 for positive zero and negative values respectively, multiplying the ABS() result by the sign of the original result is a simple, yet effective way to convert rounded values back to negative.
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by vlady
Bookmarks