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.

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.