Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Rounding in Alteyrx

bribui
5 - Atom

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?

 

bribui_1-1665765827679.png

 

bribui_0-1665765812183.png

 

2 REPLIES 2
Luke_C
17 - Castor

Hi @bribui 

 

This article will explain why this is happening. 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Floating-point-numbers-are-surprisi...

 

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

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

 

Luke_C_0-1665772334760.png

 

 

danilang
19 - Altair
19 - Altair

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. 

 

danilang_0-1665834883154.png

 

 

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

Labels