Rounding in Alteyrx

Question

We have a data set of interest rates that go to 5 decimal points, and we need to round it to 4 decimal points. Were currently using Round([Field1,.0001) as our formula, but when a value ends in a 5 it is sometimes rounding up, sometimes rounding down. In excel a value ending in 5 would always round up. How do we round so its uniform?

danilang · Answer

Hi @bribui

Another way to accomplish this is to add a very small number to your value before rounding.  A good guideline is to add 4-5 decimals smaller that the order that your rounding to.  i.e rounding to .1, add 0.000001, rounding to .0001 add 0.000000001.

The reason that excel can handle this, is that excel is built for financial applications and so has specialized(and relatively slow) routines that work with the text representation of the number.  Alteryx was built for data analysis purposes and deals with all numbers as floating point representations.

Dan

rounding.yxmd

Luke_C · Answer

Hi @bribui

This article will explain why this is happening.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Floating-point-numbers-are-surprisingly-strange/ta-p/29049

Others might have other solutions, but try something like this:

Round([Field1]*100000,10)/100000