Turning Negative Values to Zero

Question

Hello,

I am trying to turn all of my negative values to zero in the column, please see my equation below:

IF [YTD Accr Calc after Taken to Date] > 0 THEN [YTD Accr Calc after Taken to Date] ELSE 0 ENDIF

I keep getting the error "Parse Error at char (40): Invalid type in operator (expression #1).

I want to say that some figures in this column are negative, some are positive, and some don't have a figure (I would like to keep the blank cells, blank.

Thank you,

jrgo · Answer

This assumes that you're trying to update the existing field [YTD Accr Calc after Taken to Date] and that the field is set to one of the STRING field types:

IF ISEMPTY(TRIM([YTD Accr Calc after Taken to Date]))
THEN [YTD Accr Calc after Taken to Date]
ELSIF TONUMBER([YTD Accr Calc after Taken to Date] > 0
THEN [YTD Accr Calc after Taken to Date]
ELSE '0' ENDIF

Kamran1991 · Answer

You need to convert the column in Number then apply this formula

IF [YTD Accr Calc after Taken to Date]< 0 THEN 0 ELSE [YTD Accr Calc after Taken to Date] ENDIF

Thanks!!

Maskell_Rascal · Answer

Hey @MOlinski

I just realized that you also wanted to maintain empty cells, so slight modification to your formula with incorporating @MarqueeCrew's solution will give you the desired results.

IF IsEmpty([YTD Accr Calc after Taken to Date]) THEN [YTD Accr Calc after Taken to Date] ELSE Max(ToNumber([YTD Accr Calc after Taken to Date]),0) ENDIF