Formula in Rounding Ending in 5

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

Change Percent.JPG

Thanks,

Kamen

All comments

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!

Screenshot 2025-07-16 084223.png

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

Kamen

@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

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

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

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

This formula worked for me

Screenshot 2025-07-16 111053.png

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

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!

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

still null.JPG

Kamen

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

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

Change Percent.JPG

Thanks,

Kamen