I am using this function for Cell E6:
=SUM((B6+D6)-(B5+C6))
It works great but if B6 is blank it returns an erroneous negative number for E6 since it assumes B6=0.
How can I modify the formula to force a blank E6 if B6 is bank?
THANKS!
You can use an IF statement:
IF(logical_test,value_if_true,value_if_false)
In your case, this would be:
=IF(B6<>"",SUM((B6+D6)-(B5+C6)),"")
This works as follows:
IF(B6<>"", : If B6 is not equal to nothing, ie. it equals something
SUM((B6+D6)-(B5+C6)), : Set the value as per the equation
"") : Otherwise set the value to nothing.
Of course, this only checks that there is a value in B6. Depending on that value (and that of the other cells) you could still get a negative result. The way to avoid this would be to use the following:
=IF((SUM((B6+D6)-(B5+C6))>0),SUM((B6+D6)-(B5+C6)),"")
A bit long winded, but basically it checks the result of the sum is greater than 0. If it is it displays it, if not it leaves the cell blank.
Hope this helps,
Matt
195 views
Usually answered in minutes!
Thanks Matt!
It works if the E6 result is positive but not if it is negative. However, you set me on the right path. I modified it to:
=IF(SUM(B6>0),SUM((B6+D6)-(B5+C6)),"")
AND IT WORKS GREAT.
Thanks So Much!!
×