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

[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

[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-...

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

[Tanai_Goncalves]

Hey @cmcclellan thanks for reply.

The problem is that only ~700 rows where impacted by this issue.

I have another +hundred thousand rows that will be impacted by this decimal adding.

[cmcclellan]

I wouldn't be adding decimals either 🙂   As per my post, I would be using CEIL to make it work the way you intend, but we all need to see some real data in order to help further 🙂 

[ncrlelia · Accepted solution]

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'.

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

Hope this helps.

Best Regards,

Lelia

[Tanai_Goncalves]

Hey @ncrlelia 

Thanks for reply.

I suggested a workaround really close to that. 

And that is the only way, I think.

But the idea of manipulate number with strings formulas wasn't well received.

Thanks for all suggestions.

Kind Regards,

Tanai