Using ISBLANK or IFERROR

@Taylor Cerul

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

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