Hi,
Good day !
I would like to 'round down' numbers to 2 decimal places.
Example as below :
Field1 | Correct Output |
30689.71 | 30689.71 |
4300.286 | 4300.28 |
6504.661 | 6504.66 |
6074.728 | 6074.72 |
467.1111 | 467.11 |
2222.249 | 2222.24 |
2277.22 | 2277.22 |
2273.7 | 2273.7 |
2277.22 | 2277.22 |
However, i could not find 'round down' formula in Alteryx, so I use 'floor' formula.
Formula for 'Round Down' column that i used, and the result as below :
You can see there some different expected output on Round Down column ( Record 7 - 9 ).
Please advise what is the reason, or is there any better formula to get 2 decimal places without rounding it.
Thanks !
Solved! Go to Solution.
Hi @fikrizahari
Step 1: Input
Step 2: Change to String format
Step 3:
(\d+\.\d{0,2})
Step 4: Change back to Numeric datatype.
Many thanks
Shanker V
Hi @ShankerV,
Thank you for the solution.
It working so well.
However, is the expression : (\d+\.\d{0,2}) can be input in Formula tools ?
This due to, there a lot of other formula that link to this question.
I tried this :
REGEX_Replace([Field1], "(\d+\.\d{0,2})","$1")
but it isnt working :
Thanks !
Hi @fikrizahari
Even I tried the same in Regex tool using the Replace function.
Output if the Copy unmatched text to output is enabled.
Difference in output if it is unchecked.
So you need to inherit this in your formula tool when using the Regex replace as a function.
Many thanks
Shanker V
I understand the diff is caused by the rounding error of Double data type.
(because we want to display in decimal number but the computer stores it in binary number.)
So, another way to avoid this error might be shifting the boundary a little bit as below.
FLOOR([Field1]*100 + 0.1)/100
Result:
@Yoshiro_Fujimori
Thank you for clarification and solution.
It works well with just a little shifting.
Hi@ShankerV & @RobertOdera
The solution look great and give the same output as well.
Thank you so much !
Hi @Yoshiro_Fujimori and everyone,
I have another question, how do i Round Up to next 0.05.
Field1 | Require Output |
1.2 | 1.2 |
1.21 | 1.25 |
1.22 | 1.25 |
1.23 | 1.25 |
1.24 | 1.25 |
1.25 | 1.25 |
1.26 | 1.3 |
1.27 | 1.3 |
1.28 | 1.3 |
1.29 | 1.3 |
1.3 | 1.3 |
Currently I'm using Round([Field1],0.05) but it goes to the nearest 0.05. ( which is wrong )
Thanks !
Hi @fikrizahari
One way of doing this.
IF [Field1]-floor([Field1]) in (0,0.05)
THEN [Field1]
ELSE
ROUND([Field1]+.025,0.05)
ENDIF
Many thanks
Shanker V