MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.