Subtraction Formula When One Cell Is Blank

Good afternoon. How do I format my formula as to not allow the subtraction to take place if one of the cells are blank?

=[Starting Board #]@row - [Ending Board # (Top of Stack)]@row

The math is simple unless the Ending Board # cell is blank. I wish to maintain accurate inventory at all times, so if the "Ending Board #" cell is left blank until the last board is used, Smartsheet runs the formula tallying an inaccurate amount (as seen in the screenshot above).

Once the range of material has been used and entered, all my data points are accurate.

What's the formula look like to not run the formula with a blank cell and to still run it with both cells populated?

Find more posts tagged with

Accepted answers

Melissa Boehl

This is a formula entry error. It has nothing to do with any conditional formatting or other formulas. Your formula shows [Ending Board Number (top of stack)]@row when your sheet names that column [Ending Board # (Top of Stack)]@row. Try to copy/paste this into the column then make it a column formula.

=IF(ISBLANK([Ending Board # (Top of Stack)]@row), "", ([Starting Board #]@row - [Ending Board # (Top of Stack)]@row))

All comments

Melissa Boehl

I would recommend using an if statement. See the attached images for example of the formula in a sheet and the formula used as a column formula.

JLogan

@Mellissa Boehl

It doesn't work, I've disabled all conditional formatting and removed all other formulas from the sheet...what am I doing wrong?!?!?!

Melissa Boehl

=IF(ISBLANK([Ending Board # (Top of Stack)]@row), "", ([Starting Board #]@row - [Ending Board # (Top of Stack)]@row))

JLogan

@Melissa Boehl

I figured it out. When I manually typed the formula out it didn't work, but after I typed in the prefix (=IF(ISBLANK) and then selected the cells I want the formula to reference, it worked.

Thank you for providing the formula!

Melissa Boehl

@JLogan I am glad I was able to help! I should have mentioned to select the cell when inputting the formula. That is always the best way to go. Have a great day!!

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.