'Return 0 when a value of a cell is negative
I have a google spreadsheet that uses this function:
=SUM(E:E) - SUM(C:C)
It adds up all the values of column E and column C and them subtracts the difference. I would like to be able to return a 0 if the difference is negative.
Solution 1:[1]
IF((SUM(E:E) - SUM(C:C))< 0,0,SUM(E:E) - SUM(C:C))
Solution 2:[2]
Possible without changing the existing formula but with formatting such as:
#.00;"0";0
This differs from a formula approach such as =MAX(SUM(E:E)-SUM(C:C),0) because adding a number to the output of that formula will give as a result the added number, where SUM(C:C) is greater than SUM(E:E). Adding the same addend to =SUM(E:E)-SUM(C:C) where SUM(C:C) is greater than SUM(E:E) will not give the addend as the result, except when the added is 0.
Solution 3:[3]
I took another route - I just copied the entire column to another column to the far right, and then referenced that column for my formulas in the column I want to edit.
When you're finished, "hide" the copied column (click on the column menu and there is a "hide" option), but DO NOT delete it, because then your formulas will not work.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Femaref |
| Solution 2 | pnuts |
| Solution 3 | glenn |