Possible Rounding Bug?

[mitstevens]

Hello!

I encountered unexpected behavior with Round() in the formula tool. Starting with a Text input with the following values:

1. 48.885

2. 0.885

3. 40.885

With the formula Round([Value],0.01), I would expect the following output

1. 48.89

2. 0.89

3. 40.89

However, the actual output is

1. 48.89

2. 0.89

3. 40.88

Can anyone help me understand what is going on here? Currently I have a formula workaround such that if the third decimal place is ever a 5, change it to a 6 to ensure rounding up.

[AGilbert]

try the ceil() function! 

[mitstevens]

Ceil works great, thank you! Much better than the roundabout approach of regexing out the third decimal place to check if its a 5 and then adding 0.001 and then rounding... 

Still I am confused on why Round did not work. Given that they all have the same decimal and are being rounded to the same place, they should all round the same way right? I don't know why what is before the decimal place affects the rounding after. Any insights on that would be greatly appreciated

[flying008]

Hi, @mitstevens 

While I can't say for sure that it's a bug, the fact is that the results of functions do have unexplained differences based on the same decimal places, so we need to know and avoid this phenomenon before there is an official response, like use a udf to fix it.

[apathetichell]

this shouldn't be happening at that level of precision. Can you share your Alteryx versoin #, if AMP is on, what your text input looks like - and your select tool? ie can you share your workflow?

[AGilbert]

@apathetichell can confirm same rounding and agree that decimal imprecision shouldn't be that early. In fact, my text input was only three digits w/ dt double. 

@mitstevens can you share your workflow? 

AMP off, Version 2023.2.1.133

[alexnajm]

Following for curiosity's sake

[apathetichell]

I'm on 21.4 - and not seeing this behavior... at least not with my sample data.

[flying008]

Hi, @mitstevens @apathetichell 

This is a real difference that happened on the v2021 version, fortunately, at the moment we found it ourselves and can correct the issue.

P1	P2	Bug	Fix	Test
40.885	1	41	41	-1
40.885	0			-1
40.885	0.1	40.9	40.9	-1
40.885	0.01	40.88	40.89	0
40.885	0.001	40.885	40.885	-1
40.885	0.0001	40.885	40.885	-1
40.8885	0.01	40.89	40.89	-1
-40.885	0.01	-40.88	-40.89	0
40.85	0.1	40.9	40.9	-1
40.85	0.01	40.85	40.85	-1

[mitstevens]

Thank you everyone trying to help!

My current version is 2022.1.1.42604, AMP on. I tried disabling AMP with no effect. I am unable to upload any workflows due to company policies, nor am I able to download any community workflows. However, attached are pictures of my text input and select tool.

Flying008 thank you. The formula you appear to have used for the fix is Round5Fix(X,[Mult]), I don't think I have this function. This a custom formula function/UDF? I don't know much about these so I am unsure if I can use that since my workflows are also required to be compatible with our Alteryx server.

I may have to stick with my convoluted string searching and regex workaround (also attached). Basically a formula to find where the decimal place is and get everything to the right of the decimal, then regex out the third digit and check if is 5, then add 0.001 if so before the rounding.

AGilbert's Ceil solution sounded good, but also realized that will round up for values below 5 too, so I'd still need some check if the value is >=5