Page 1 of 1
Formula question ("if sum is negative, then 0")
Posted: Fri Oct 28, 2016 9:26 pm
by lilkt29
Sorry, newbie here. This may have been answered somewhere else, but if so, I haven't been able to find it. Here's my scenario:
A1 = value
B1 = value
C1 = sum(A1-B1)
If the sum shown in C1 is negative, is there a way for it to just show as "0" instead?
Thanks for your help!
Re: Formula question ("if sum is negative, then 0")
Posted: Fri Oct 28, 2016 10:09 pm
by Lupp
1. "=SUM(A1-B1)" is only a complicated way to express "=A1-B1"
2. You have to specify if you actually want
2.a the cell containing the formula to
show zero but to actually contain the true result of the subtraction OR
2.b the cell to actually get assigned the value 0 under the given condition and also to show this value.
2.a is not done by a formula but by a 'Numbers' format code like
2.b can be done in different ways.
Code: Select all
=IF(A1-B1<0;0;A1-B1) or =(A1-B1)*If(CURREBT()<0;0;1)
are possible solutions.
Re: Formula question ("if sum is negative, then 0")
Posted: Fri Oct 28, 2016 10:13 pm
by Villeroy
=A1-B1 returns one value, the difference between A1 and B1.
=SUM(A1-B1) returns the sum of the one value. The SUM function is obsolete since it sums up a single value.
=MAX(A1:D99) returns the highest value in range A1:D99
=MAX(A1;B2;C3;D4:X99;1000) returns the highest value of 3 disjunct cells, range D4:X99 and number 1000. If none of the cells is above 1000, the 1000 will be returned.
=MAX(A1-B1;0) returns the highest value of 2 values, either the difference between A1 and B1 or 0. If the first argument (the difference) is smaller than 0 then the MAX function returns the second argument 0.
[Tutorial] Ten concepts that every Calc user should know