alteryx Community

# Alteryx Designer Discussions

SOLVED

## decimal roundup when 0.005

8 - Asteroid

Hello,

I'm rounding a formula.

But I need to roundup when it get on the exact half of the third decimal.

155,415 needs to become 155,42 and not 155,41.

Example:

 field 1 field 2 Result Desired Round Output Alteryx Wrong Output 4662,45 30 155,415 155,42 155,41 4662,35 30 155,4117 155,41 155,41 1145,25 30 38,175 38,18 38,17 1953,45 30 65,115 65,12 65,11 1953,4 30 65,11333 65,11 65,11

I'm using this simple formula:

round((round([field 1],0.01) /[field 2]), 0.01)

Any ideas?

Kind Regards,

Tanai

16 REPLIES 16
13 - Pulsar

Does this do what you're after ?

CEIL([field 1]*100)/100

8 - Asteroid

I believe this is a floating point issue.

Can you add a very small amount to your number to make it round up?

e.g  round([Result]+0.00000001,0.01)

9 - Comet

Change the Alteryx output field to int64 data type

21 - Polaris

Any value lesser or equal to 5 in the rounding place will get converted to the previous decimal. Anything greater than 5 ex:6 will be converted to next decimal. Refer the highlighted

Hope this helps : )

21 - Polaris

You can arrive at the desired output by using below formula.

Workflow:

Hope this helps : )

11 - Bolide

@Tanai_Goncalves - perhaps you should submit this as a bug. i could understand if it's a floating point issue, but i'm not sure it is. they may just need to fix their round() function to make the exact midpoint round UP to the desired level instead of down.

this is not intuitive and i would not expect people to need to add an arbitrary tiny number to force the round() to work as expected. i'm kind of concerned now about any previous times i used the round function??

21 - Polaris

Hi @mst3k

I have same concern excel seems to round anything lesser than 5 to previous greater than equal to 5 to next. This is definitely a risky issue. Especially when you dont know about this issue and have used the round functionality this is for both round and as well as fixed decimal.

18 - Pollux

Hello All

As @mst3k hinted at, this is actually a floating point issue.  If you force the values to fixed(19.15) you get this

The Alteryx Round() function(the Round column above) uses the underlying floating values and not the displayed values in its calculation leading to the inconsistencies that you see here.  Since excel was developed for financial applications, where rounding follows very specific conventions,  it actually has code that operates on the string representation of the number to ensure consistent results.

As @dbmurray suggested the best way to get around this is to add a very small amount to the number before applying the round function.   The double format used in the Alteryx Engine stores number to about 15 decimal digits of precision so adding 0.000000000001 should guarantee consistent results for rounding(Round+).

Dan

8 - Asteroid

Hello.

Unfortunately none of the answers worked.

I have really no idea what to do. I need this precision.

Kind regards,

Tanai

Labels