Formula to return a calculated value, only if the value in another column is not zero
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.
Answers

Something like this should work:
=IF([Projected Estimated Cost]@row = 0, 0, [Current Cost]@row  [Projected Estimated Cost]@row)

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

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:

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

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.

Hello @David Tutwiler
Thank you! I checked the column type and changed to "text/number" and it worked! You are awesome!
:)
Lisa

Great news! Glad you got it working.
Help Article Resources
Categories
Check out the Formula Handbook template!