Formula in Rounding Ending in 5

[KamenRider]

Hi Alteryx Community,

I have problem in formulating a formula considering the rule of rounding ending in 5 which says that if the digit immediately before the 5 is even, round down (keep the even digit). If the digit before the 5 is odd, round up to the next even number. This is known as round to even or banker's rounding. I am getting a NULL result under Change_Percent2.

example: 

1.45 would be rounded to 1.4 (the digit before 5 is even, so round down).
1.55 would be rounded to 1.6 (the digit before 5 is odd, so round up).

Here's my formula: 

Change_Percent (String type)

((ToNumber([Total_CurReject]) - ToNumber([Total_LastWReject])) / ToNumber([Total_LastWReject])) * 100

Change_Percent2 (String type)

IF (MOD(ABS(ToNumber([Change_Percent])), 1) = 0.5) THEN
IF (MOD(FLOOR(ABS(ToNumber([Change_Percent]))), 2) = 0) THEN
FLOOR(ABS(ToNumber([Change_Percent])))
ELSE
CEIL(ABS(ToNumber([Change_Percent])))
ENDIF
ELSE
ROUND(ABS(ToNumber([Change_Percent])), 0)
ENDIF

 

Hoping some to help me solve this problem.

Thanks,

Kamen

[alexnajm]

The MOD function will get you the remainder as a whole number - there is no MOD result that can be 0.5 in my view. Once I change the 0.5 to 0 for example, I get a result

I would check your logic again and adjust the formula accordingly - good luck!

[KamenRider]

Hi @alexnajm 

What would be the correct formula to get the result required?

Kamen

[alexnajm]

@KamenRider what have you tried to fix it? Sounds like you could find the 5 in the second decimal position and create a formula to decide what happens in the event when the first decimal is odd or even

[KamenRider]

Hi Alteryx Community / @alexnajm 

I am trying to fix it but some how I can't find the problem. Maybe this formula hopefully will work.

IF Abs(ToNumber([Change_Percent]) - Floor(ToNumber([Change_Percent]))) >= 0.4999 AND Abs(ToNumber([Change_Percent]) - Floor(ToNumber([Change_Percent]))) <= 0.5001 THEN
IIF(Mod(Floor(Abs(ToNumber([Change_Percent]))), 2) = 0,
Floor(ToNumber([Change_Percent])),
Ceil(ToNumber([Change_Percent]))
)
ELSE
Round(ToNumber([Change_Percent]), 0)
ENDIF

OR

IF Abs([Change_Percent] - Floor([Change_Percent])) >= 0.4999 AND Abs([Change_Percent] - Floor([Change_Percent])) <= 0.5001 THEN
IIF(Mod(Floor(Abs([Change_Percent])), 2) = 0,
Floor([Change_Percent]),
Ceil([Change_Percent])
)
ELSE
Round([Change_Percent], 0)
ENDIF

Any suggestions or solutions are very much appreciated.

Kamen

[KamenRider]

Hi @alexnajm 

Thanks for the suggestion! I actually tried to implement that 'round half to even' and odd logic with my Change_Percent2 formula, but it's currently showing [Null]. For now, I'm using Change Percentage 2 which just rounds to one decimal place. Do you have any ideas why Change_Percent2 might be returning null, or a more robust way to handle that specific rounding?

A sample formula of yours might help me get of this issue.

Looking forward for your response.

Kamen

[alexnajm]

This formula worked for me

[KamenRider]

Hi @alexnajm 

Thanks for the response however can we round it to whole number turning it to 38. The rules for odd and even for 0.5 shall still be followed.

Thanks,

Kamen

[alexnajm]

I think you should be able to do this from here with my solution @KamenRider 😊 

Please accept my response as a solution since it solved the initial query, and post any further queries as a separate discussion question!

[KamenRider]

Hi @alexnajm 

What went wrong? the result is still "NULL". Please help.

 

Kamen