Rounding Error (to 2DP)

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

Developer Tools

Accepted answers

Jonathan-Sherman

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-errors)

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

All comments

Jonathan-Sherman

Hi @SAS82,

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,

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703