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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!