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
Solved! Go to Solution.
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
My answer should work, can you give me an example where it doesn't please ?
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)
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.
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 🙂
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'.
Hope this helps.
Best Regards,
Lelia
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