Using ISBLANK or IFERROR

Options

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.

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @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..

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Taylor Cerul
    Options

    @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
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/21/22
    Options

    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.)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Taylor Cerul
    Options

    @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
    Jeff Reisman ✭✭✭✭✭✭
    Options

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!