2 Decimal Places without Rounding

[fikrizahari]

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 !

[ShankerV]

Hi @fikrizahari 

One way of doing this.

Many thanks

Shanker V

[ShankerV]

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

[fikrizahari]

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 !

[ShankerV]

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

[Yoshiro_Fujimori · Accepted solution]

@fikrizahari , @ShankerV 

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:

[RobertOdera]

Hi, @fikrizahari 

Here you go.

Please mark it as an acceptable solution if it works for you.

[fikrizahari]

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

[fikrizahari]

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 !

[ShankerV]

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