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 1field 2ResultDesired Round OutputAlteryx Wrong Output4662,4530155,415155,42155,414662,3530155,4117155,41155,411145,253038,17538,1838,171953,453065,11565,1265,111953,43065,1133365,1165,11 I'm using this simple formula: round((round([field 1],0.01) /[field 2]), 0.01) Any ideas? Kind Regards, Tanai

Hi @Tanai_Goncalves, Suggesting an approach that is unconventional when comes to rounding, but since the other suggestions don't work for you, probably you can try this brute force method. 😅 The idea is to use Regex to identify the digit at the 3rd decimal place. If it is 5 then add 0.01 to 'Alteryx wrong output'. * Create 2 [Result] = [Field 1]/[Field 2] fields * Set it as String * Set it as Fixed Decimal with decimal = 2 * * Use Regex on [Result - String] to identify the digit at the 3rd decimal place* * If the output in step 2 = 5 then add 0.01 to [Result - Fixed Decimal]* Hope this helps. Best Regards, Lelia

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

Tips and Tricks

Accepted answers

ncrlelia

Hi @Tanai_Goncalves,

Suggesting an approach that is unconventional when comes to rounding, but since the other suggestions don't work for you, probably you can try this brute force method. 😅

The idea is to use Regex to identify the digit at the 3rd decimal place. If it is 5 then add 0.01 to 'Alteryx wrong output'.

Create 2 [Result] = [Field 1]/[Field 2] fields
1. Set it as String
2. Set it as Fixed Decimal with decimal = 2
Use Regex on [Result - String] to identify the digit at the 3rd decimal place
If the output in step 2 = 5 then add 0.01 to [Result - Fixed Decimal]

Hope this helps.

Best Regards,

Lelia

Workflow.yxmd

All comments

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)

binu_acs

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

Roundup.yxmd

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.

round.xlsx

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

danilang

Hi @Tanai_Goncalves

You'll have to show us exactly what isn't working. With your sample data I get the following output when adding a small amount before rounding

The values in the AlteryxOutputSmallAddition column I added match with the values from your [Desired Round Output] column using the following formula

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

Note that you have to do the addition every time you use the round() function.

If you have any counter examples where the rounding is not correct, please post them here.

Dan

Round+.yxmd

cmcclellan

My answer should work, can you give me an example where it doesn't please ?

vsoni

Hi Tenai

I would use the advice from this thread: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Round-130-3155-to-3-decimal-places/td-p/566369

use a formula tool with the following: Round([Value]+0.001, 0.001)

ncrlelia

Hi @Tanai_Goncalves,

Suggesting an approach that is unconventional when comes to rounding, but since the other suggestions don't work for you, probably you can try this brute force method. 😅

The idea is to use Regex to identify the digit at the 3rd decimal place. If it is 5 then add 0.01 to 'Alteryx wrong output'.

Create 2 [Result] = [Field 1]/[Field 2] fields
1. Set it as String
2. Set it as Fixed Decimal with decimal = 2
Use Regex on [Result - String] to identify the digit at the 3rd decimal place
If the output in step 2 = 5 then add 0.01 to [Result - Fixed Decimal]