Another blank cell question
Posted by Kevin Smith on January 09, 2002 1:22 PM
I use the following formula to compute a group of numbers
=SUM(R7-(U7+V7+W7))+(U7*2)+(V7*3)+(W7*4)
Occasionally the cells that it uses are blank (which is intentional) and I get the result #value. This screws up another formula. Instead of #value I want the cell to be blank if their is no value.
Alos thanks for all your help. This is a great place for people like myself with imited excel knowledge.
Kevin
Posted by Mark W. on January 09, 2002 1:33 PM
If by blank you mean empty... a blank cell won't
cause the #VALUE! error. Is it possible that
the cells contain a null text string ("") instead?
BTW, the SUM function is unnecessary since it
only has 1 argument. =SUM(R7-(U7+V7+W7)) is the
same as =R7-(U7+V7+W7).
Posted by Kevin Smith on January 09, 2002 1:48 PM
Yes it is actually a null value
Example the cell r7 in the equation has the formula =IF(ISBLANK(Apr2!AC9),"",Apr2!AC9)
When Apr2!AC9 has no value is when I get the #value response
Posted by Mark W. on January 09, 2002 1:54 PM
AC9 is null (empty, blank, whatever), but R7
contains the null text string, ""; therefore,
=ISBLANK(R7) is FALSE and =R7-0 is #VALUE!.
Try this...
=N(R7)-(U7+V7+W7)+(U7*2)+(V7*3)+(W7*4)
Posted by Kevin Smith on January 09, 2002 2:03 PM
Thaks Mark but I still get the same #value response when using
=N(R7)-(U7+V7+W7)+(U7*2)+(V7*3)+(W7*4)
Posted by Mark W. on January 09, 2002 2:26 PM
Then the same null text string, "", is in
U7, V7, and/or W7. Try this...
=N(R7)-SUM(U7,V7,W7)+N(U7)*2+N(V7)*3+N(W7)*4
By now it should be obvious that...
=IF(condition,"",range)
...can be a questionable practice. Some use
this approach to eliminate an "unsightly" zeroes
caused by referencing a blank cell when the
number format...
0;-0;
...would accomplish the same thing without the
undesirable side effects that you're experiencing.
