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]

You can update the regex to consider negatives, and it works for me: "-?d+\.[2468]\d*"

However for negative numbers, the rounding behavior will be different - "rounding up" in these cases will make -1.55 into -1.5 since that's going higher - you can adjust your formula accordingly to deal with negatives differently if you want different behavior

[KamenRider · Accepted solution]

Hi @alexnajm 

Thank you very much for your assistance. I received help from other sources and believe this solution aligns with the requirements for matching in the Excel file. I'm sharing this solution with you.

IF ToNumber([Change_Percent]) = NULL() THEN NULL()
ELSE

IF ABS(ToNumber([Change_Percent])) - FLOOR(ABS(ToNumber([Change_Percent]))) = 0.5 THEN
IF ToNumber([Change_Percent]) < 0 THEN
// If it's a negative .5 number
// Check if the absolute integer part is even or odd
IF MOD(FLOOR(ABS(ToNumber([Change_Percent]))), 2) = 0 THEN // e.g., -38.5 (abs 38 is even)
FLOOR(ToNumber([Change_Percent])) // Round -38.5 towards zero to -38

ELSE // If integer part is odd (e.g., -37.5)
CEIL(ToNumber([Change_Percent]) - 1) // Round -37.5 away from zero to -38
ENDIF
ELSE // Positive numbers ending in .5 (as before)
IF MOD(FLOOR(ToNumber([Change_Percent])), 2) = 0 THEN // e.g., 2.5 (integer 2 is even)
FLOOR(ToNumber([Change_Percent])) // Rounds to 2
ELSE // If integer part is odd (e.g., 3.5)
CEIL(ToNumber([Change_Percent])) // Rounds to 4
ENDIF
ENDIF
ELSE
ROUND(ToNumber([Change_Percent]), 1)
ENDIF
ENDIF

 

Thanks,

Kamen

[alexnajm]

I still contend that my response solves the original query of rounding up/down based on odd/even, so if my response could also be accepted that would be appreciated. Thank you in advance!