How can i return a value of 0 when the calculation in the cell is zero or less than zero?
iMac, iOS 9.0.2
what ever calculation is in the cell should be "put" inside a max function like this.
is the calc is designated <calc>
then you should change from:
=<calc>
to
=max(<calc>, 0)
e.g. if the cell contains:
=sum(A2:B45)
you should use:
=max(sum(A2:B45), 0)
WOW That worked well in a hurry. ( although i didn't actually have to type <> or = )
NOW, the returned number in the cell is either 0 if the calculation in the cell is negative, or stays the same if it is a positive number. Cool.
Thanks Wayne !!
Now , the next thing i need is return a 0 when dividing by zero. Can this be done? I think so.
Actually i would need it to be little different --> to return the number normally found by the division formula except if the the formula attempts to divide by 0, in which case i don't want to get an error and would likely want to return a zero in place of that.
there are a couple ways to mask a divide by zero error:
1) use the iferror function like this:
=iferror(max(<calc>, 0), "")
this will leave the cell blank on error
=iferror(max(<calc>, 0), "ERROR")
this will print "ERROR" on error
OR
2) =if(<DENOM> = 0, "", <NUM>/<DENOM>)
wow, Wayne that was a cool fix.
After i got the original box to say "N/A" instead of "ERROR", i got the other series of boxes related to it say "N/A" also ( when the original calculation wasn't relevant)
For not being a programmer, this was an exact fix to this problem. and was relatively easy. Thanks Wayne !!
New Function to return 0 for negative calculation value
