This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
It has to do with the way Alteryx stores values and internal representation. When rounding to a fixed decimal, Alteryx accurately rounds to the nearest number based on the internal representation of the number. (For example, if 74.876925000 is internally represented as 74.8769249999999999955, converting to a string with five significant digits results in 74.87692, not 74.87693.)
I did see that, but not sure I can pull a solution from that. I am working with interest rates, and 1 basis point on hundreds of billions can make a difference.
Perhaps said a different way, is there a fool proof rounding function (or series of embedded functions) that replicates what Excel is doing? Excel's formulas seems to always return the expected result.
The multiple rounding approach, Round(Round([Field1]*100,.1), 1)/100, will work most of the time, but you'll always find an edge case that will break it.
A better approach is the use the weakness of the double field format against it.
In your examples, you see that the values are consistent to about 14 decimal places. This is just about the limit of accuracy of the internal floating point representation. If you add an epsilon value of 0.0000000000001(13 decimals) to your values before rounding you force the value to be marginally larger so that rounding operation will return the correct results. Since Epsilon is so much smaller than the value itself, it never affects the materiality of the operation, i.e the values will never end up so large that it rounds up to the next decimal.
Here are the results of applying this technique to multiple values, including some where the internal representation is actually a little larger than the value. 2.555->2.5550000000000001598721...
The formula I used in the RoundedWithEpsilon columns is
The last 2 entries show that the epsilon is small enough to not force the rounding up to the next decimal.
This method will work when rounding all the way down to about 12 decimal places. For values where you need more than 12 decimals of rounding accuracy, you'll need to roll your own routines
This also doesn't work to give you Banker's Rounding where 0.xx5 is rounded to the nearest even decimal