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,
Hi @MOlinski
This part "Parse Error at char (40): Invalid type in operator (expression #1)" is referencing the operator '>'. You are getting the error because the field in question is a string. Use a ToNumber wrapper and you should be good to go.
IF ToNumber([YTD Accr Calc after Taken to Date]) > 0 THEN [YTD Accr Calc after Taken to Date] ELSE 0 ENDIF
Cheers!
Phil
I prefer:
max(tonumber([your data]),0)
cheers,
mark
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
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!!
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