decimal roundup when 0.005

[Tanai_Goncalves]

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

[cmcclellan]

Does this do what you're after ?

CEIL([field 1]*100)/100

[dbmurray]

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)

[binuacs]

Change the Alteryx output field to int64 data type

[atcodedog05]

Hi @Tanai_Goncalves 

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

[atcodedog05]

Hi @Tanai_Goncalves 

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

Workflow:

Hope this helps : )

[mst3k]

@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??

[atcodedog05]

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.

[danilang]

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 

[Tanai_Goncalves]

Hello.

Thanks for replys.

Unfortunately none of the answers worked.

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

Kind regards,

Tanai