2 Decimal Places without Rounding

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 !

Tips and Tricks

Preparation

Accepted answers

Yoshiro_Fujimori

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

RoundDown.yxmd

All comments

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

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

RoundDown.yxmd

RobertOdera

Hi, @fikrizahari

Here you go.

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

For_fikrizahari.yxmd

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

Yoshiro_Fujimori

@fikrizahari ,

As you see the explanation of the function

If you input "0.05" as "mult" parameter, the function will find the "nearest multiple of 0.05".

So the nearest multiple of 1.21 would be 1.2, not 1.25.

In this case, you would need to shift the border by 0.025 (half of 0.05).

And also you need to shift a little bit to avoid the base conversion error.

With the formla below,

Round([Field1] + 0.0249, 0.05)

The output would be

reeteshchoudhury

Hi, There is a very simple way to achieve this without any complex formula: Just use the floor function with the following parameter:

floor([Data],0.01)

Data Result
1555.848 1555.84
3441.202 3441.2
6115.357 6115.35
2411.07 2411.07
60128.39 60128.39

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703