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.
Hello everyone,
I have been pretty odd behavior from Alteryx on rounding numbers. At first I thought there was something wrong with the calculation, but apparently it is a defect. So I wanted to share this with the community to get feedback. I tested it on version 9.5, 10.5 and 11
so if you take an average of two numbers of (100, and 124.83) it will end up in 112.415. You can either do it manually or use function "average".
Now when you try to round the result to hundredth with round ( [X] , 0.01) it will end up at 112.41 instead of 112.42
Interesting part is if you code the number 112.415 manually then the result of rounding will be 112.42
It gets more interesting when you take the average of 100 and 224.83 instead of 124.83. The result of course is 162.415 and if we round it this time it will end up as 162.42. in other words "whole number" is affecting the decimal rounding which does not make sense to me.
I guess this should be probably a defect on the way that numbers are restored.
Fun fact: we found this issue on Halloween !
Solved! Go to Solution.
@Pasha_Maleknia This isn't a bug, but rather a limitation with how computers store and perform math operations on floating point numbers. Let's consider the number '1.025'. Due to limitations in precision with variable storage and the conversion to binary when this value is stored as a double the number actually gets stored as '1.024999999999999911182158029987476766109466552734375'. As such when rounded to 2 decimal places the number would round to 1.02 where you may expect it to round to 1.03. This is because computers can't store floating point values with exact precision, and some fractions are infinite in length (1/3 for example is 0.33333... repeating). Because of finite storage space if the value doesn't convert to an exact binary number or if it exceeds storage available for the variable it gets rounded. There is an excellent article here on the Community written by one of our developers explaining this in detail. I have included a link for that article below along with some third party article that go into more of the math behind the behavior.
Community Article:
Floating point numbers are surprisingly strange
Additional reading:
Why 0.1 Does Not Exist In Floating-Point
Inconsistent Rounding of Printed Floating-Point Numbers
Stack exchange discussion with a good simple explanation:
What causes floating point rounding errors
I'm having a similar issue with rounding. (Sample workflow attached and images pasted below)
There doesn't seem to be consistency in how decimal numbers ending in a 5 are rounded. In my test, two numbers were incorrectly rounded down and one was correctly rounded up. All us end-users expect to see "5 or more, raise the score. 4 or less, let it rest."
I understand the explanation, but my question is, regardless of the why, how do we get Alteryx to do what we expect it to do! :) Why is it that Excel can handle rounding? Has anyone discovered a workaround they'd be prepared to share?
Many thanks in advance
@TeePee The rounding isn't inconsistent and it also isn't incorrect. As I previously stated this comes down to how fractional number are stored in binary by computers. For details on how this works I highly recommend reading the resources I linked to in my previous post.
As for your specific example none of the rounding you performed is wrong. It just wasn't what you expected based on the input and because you aren't considering the real value of the number as stored by the computer. For example 206.95 when stored as a double in binary is actually '206.94999999999998863131622783839702606201171875' and when round at the first decimal place 206.94 becomes 206.9. 1.45 when stored as a double is actually '1.4499999999999999555910790149937383830547332763671875' which rounds as you saw to 1.4, and 1.55 when stored as a double is '1.5500000000000000444089209850062616169452667236328125' which rounds to 1.6. So the numbers are rounding exactly as you are telling them to in your formula, its just the that underlying number being used by the formula isn't what you expect because the number can't be stored exactly by the computer. As such the computer stores the number as the closest possible approximation that fits into the designated memory space. Sometimes this value is slight smaller than the actual number and sometimes it slightly larger.
To get the results you want/expect you need to be more precise with your rounding so that the stored value is greater than 206.950 when your do your final rounding. You can easily do this by rounding the number multiple time with multiple decimal places of precision prior to rounding at the single decimal place you want for your end result. See the screenshot below for an example.
If you are curious what a specific decimal value translates to when stored as a float or double by a computer you can use this link to find the actual value.
KevinP
While I did not read those articles, your explanations don't seem to address the bottom-line issue of this thread. Using Pasha_Maleknia's example above, the average of the numbers 100 and 124.83 rounded to two decimal places using mathematical rounding rules is 112.42. The Excel formula "=ROUND(AVERAGE(100,124.83),2)" returns the correct result 112.42; however, the Alteryx formula "ROUND(AVERAGE(100,124.83),0.01)" return the incorrect result of 112.41. It doesn't matter why Alteryx returns what it returns -- it only matters that Alteryx is returning the wrong value.
If Microsoft can get Excel to return the correct result, why can't Alteryx?