substracting empty cells gives #invalid operation
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
Best Answers

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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Happy to help.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers

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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Hi Mark,
genius! worked like a charm! thanks Mark!

Happy to help.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
Check out the Formula Handbook template!