substracting empty cells gives #invalid operation

Antje B.

column 1 & 2: data, column 4: data

column 3: =IF(SUM(column1@row:column2@row) = 0, "", (SUM(column1@row:column2@row)))

so if column 1 plus column 2 equal 0 the cell stays blank. this part of the function works well

in column 5 i want to substract column 3 from column 4. if column 3 is blank (aka hidden 0) i get an error message #invalid operation

=IF(column4@row - column3@row = 0, "", column4@row - column3@row)

Can I not substract cells with hidden zeros? or is there a mistake in my formula? if column 3 is not blank the formula works fine.

any help is appreciated.

thank you

Antje

Mark Cronk

Hi @Antje B.,

If the sum is 0 you've made column 3 "" which smartsheet reads as text. When you try to do math with text you get an error. In column 5 try using:

=IF(column3@row="", column4@row, column4@row - column3@row).

Theformula says that if column 3 is blank then the result is column 4, which is column 4 - 0. Otherwise it returns column 4 - column 3.

Work?

Mark

Mark Cronk

Happy to help.

Mark

Mark Cronk

Hi @Antje B.,

If the sum is 0 you've made column 3 "" which smartsheet reads as text. When you try to do math with text you get an error. In column 5 try using:

=IF(column3@row="", column4@row, column4@row - column3@row).

Theformula says that if column 3 is blank then the result is column 4, which is column 4 - 0. Otherwise it returns column 4 - column 3.

Work?

Mark

Antje B.

https://community.smartsheet.com/discussion/comment/270396#Comment_270396

Hi Mark,

genius! worked like a charm! thanks Mark!

Mark Cronk

Happy to help.

Mark