Formula to return a calculated value, only if the value in another column is not zero

Lisa Matthews

Hello,

I am looking for help to create a formula to calculate only if the value in another column is NOT ZERO. This is a description of what I am trying to do:

·        If [Projected Estimated Cost] is NOT ZERO, then calculate [Current Cost]@row MINUS [Projected Estimated Cost]@row.

·        If the value of [Projected Estimated Cost] is zero, then [Projected Savings] should appear as zero

·        Return value should appear in the [Projected Savings] row.

Thank you, in advance, for your help.

--Lisa M.

David Tutwiler

Something like this should work:

=IF([Projected Estimated Cost]@row = 0, 0, [Current Cost]@row - [Projected Estimated Cost]@row)

Lisa Matthews

Hi @David Tutwiler ,

Thank you for responding to my question. I inserted the suggested formula and received the following error message:

Any suggestions for troubleshooting?

Thank you,

Lisa

David Tutwiler

That is odd. I recreated your columns and it does not do that for me. Can you confirm that all of your @row mentions in the formula have a color to them (not black text) to verify that Smartsheet can see that it is a column value? I put a picture below of what it looks like on my test sheet:

Lisa Matthews

Hi @David Tutwiler ,

I enter the formula as written (colored column names appear) and see a #NONPARSEABLE error. The column "Projected Estimated Cost" has a formula in it to calculate the estimated cost, does that make a difference in how the formula in the projected savings column is written?

Thank you,

Lisa

David Tutwiler

It shouldn't. I would check the column formatting, just to make sure that everything is set to Text/Number, but other than that it all looks fine and matches what I have working on my test sheet.

Lisa Matthews

Hello @David Tutwiler

Thank you! I checked the column type and changed to "text/number" and it worked! You are awesome!

:)

--Lisa

David Tutwiler

Great news! Glad you got it working.