Using ISBLANK or IFERROR

Hello,

I need to add an exception in here for when the cells are blank. The problem is I am getting an "invalid operation" error whenever I come up with a formula.

=SUM([NEW COMMERCIAL LIST PRICE]@row, -([NEW COMMERCIAL LIST PRICE]@row * [CURRENT GSA (%) DISCOUNT (excluding IFF)]@row))

I have been trying to add an ISBLANK or IFERROR onto this with no luck.

It would be great if my formula were able to recognize the blank, and multiply by 0 so that values just stay the same.

I am stumped. Any help would be appreciated.

Find more posts tagged with

Accepted answers

All comments

Jeff Reisman

@Taylor Cerul

...multiply by 0 so that values just stay the same

Do you mean 1? Multiplying by 0 will result in 0. Hard to know what you're expecting without a better idea of what you're computing.

You can wrap the whole thing in IFERROR, and specify what value to put in place of the error message. The syntax is =IFERROR(formula, replacement value)

=IFERROR(SUM([NEW COMMERCIAL LIST PRICE]@row, -([NEW COMMERCIAL LIST PRICE]@row * [CURRENT GSA (%) DISCOUNT (excluding IFF)]@row)), 1)

If dealing with blank cells - in your formula, if [NEW COMMERCIAL LIST PRICE]@row is blank, the result is just the negative of [NEW COMMERCIAL LIST PRICE]@row * [CURRENT GSA (%) DISCOUNT (excluding IFF)]@row. If either or both of the other values are blank, the result is just the value in [NEW COMMERCIAL LIST PRICE]@row.

If dealing with potential errors in those cells you are referencing, you can't IFERROR those out in another formula. You would need to IFERROR the formulas in those cells to produce a valid value that could then be computed by your formula above..

Taylor Cerul

@Jeff Reisman Thank you very much for the response. Yes I meant 1 (my brain is fried lol).

Basically, I am using a formula to calculate the prices in the "new commercial list price" column. If there is no match from the linked sheet, i have the cell coming back as blank. If it is blank, I would like the "Current commercial list price" to be transferred to the "New commercial list price" column to reflect that there should be no change entered into our system.. Im not sure if thats possible? Thanks

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320304#Comment_320304

To accomplish that, you might need to add a hidden "Helper" column.

Use your Helper column to bring in your New Commercial List Price value using the same formula you're using now. Then in your existing New Commercial List Price column, use an IF statement to decide which value to use. The syntax is =IF(logical expression, true condition, false condition).

=IF(ISBLANK(HelperColumn@row), [Current Commercial List Price]@row, HelperColumn@row)

In English, If the Helper Column is blank, put the Current price in this column, otherwise, give me the value from the Helper Column.

(Technically, you could do this without a helper column, but if you have the room in your sheet for the column, it's just an easier and less complicated formula. If you want to do it without, let me know.)

Jeff Reisman

Without a helper column:

In the New Commercial List Price column,

=IF(ISBLANK(formula to bring in your values from the other sheet), [Current Commercial List Price]@row, (formula to bring in your values from the other sheet))

Taylor Cerul

@Jeff Reisman @Jeff R that worked! I used the =IF(ISBLANK(HelperColumn@row), [Current Commercial List Price]@row, HelperColumn@row) with my hidden column. Thank you so so very much!!!!!!!!!!!!!!! You rock!!!! :)

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320318#Comment_320318

Happy I could help!

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.