Rounding Error (to 2DP)

[SAS82]

Hi,

I have a dataset in which our calculated value needs to be rounded to 2DP.

Example - The first Formula, to calculate the Initial CWA, calculates to 73.725. In the second Formula [Round([Initial CWA], 0.01)] this rounded down to 73.72, not up to 73.73 as expected. The data type in the second Formula is set to Double and I cannot change it.

  

This only appears to be an issue where the initial calculation (to 3DP) ends in 5 - all the other values round up/down as expected. 

I'm perhaps missing something obvious, but has anyone else come across this issue and know of a solution? 

Thank you,

Sarah

[Jonathan-Sherman · Accepted solution]

Hi @SAS82,

This is often caused by computers not being able to store numbers as the exact value (read more here if you're interested https://softwareengineering.stackexchange.com/questions/101163/what-causes-floating-point-rounding-e...)

One way round it would be to multiply by 1000 in your case, and round to the closest unit of 10, subsequently dividing again by 1000 to give the rounding value you're looking for.

ROUND(([InitialValue]*1000),10)/1000

If this solves your issue please mark the answer as correct, if not let me know!

Regards,

Jonathan

[PhilipMannering]

I'm guessing the first number has also been rounded?

Say the number 73.724999999.

That number to 3dp is 73.725

That number to 2dp is 73.73

So Alteryx is working as expected. Now you might tell me that the number should be 73.725 exactly. But computers work in binary, so every floating point number is an approximation, hence the "0.49999999" phenomenon (see https://www.geeksforgeeks.org/floating-point-error-in-python/) which could be throwing off your rounding.

Incidentally, Python does the same thing,

[SAS82]

Hi Philip,

Yes, that does look to be the case! Is there a different data type I should be using for the first number? 

Thanks,

Sarah

[SAS82]

That'll work - thank you! 🙂

[Jonathan-Sherman]

Doing a little bit more digging on this and it does seem if we store the input value as a fixed decimal (i've used a size of 19.3 - the .3 indicates decimal precision) and use the round() function off that we get the correct values.

I've attached the workflow if you need!

Regards,

Jonathan

[PhilipMannering]

@Jonathan-Sherman That only works for that example. The issue still persists for many other examples. And it is, as we have both pointed out, because of a fundamental limitation on the precision of decimal numbers,