IF function to blank without getting #value in sum function

[Brad Stevenson]

I am trying to format an IF function to relay information from one cell to
the other. If I use the value of 0 (zero) in the "true if false" portion of
my function, then my SUM function for the row works fine... but I want the
cell to be blank if there is no info to relay. However, when I use the ""
for blank, then I get the #value figure in my SUM function.

The SUM function is from a worksheet which was formatted by someone else,
and I cannot change it. Is there a way to change the IF function to perform
a cell blank if false, but not to put the #value in my SUM function?

Thks.

 

[Max]

Possibly, with your IFs set to return zeros instead of ""s, a simple way to
get a clean look would be just to suppress extraneous zeros from showing on
the sheet, via:
Click Tools > Options > View tab > Uncheck "Zero values" > OK

 

[Brad Stevenson]

OK I feel dumb now.

Thanks Max!

Brad

Possibly, with your IFs set to return zeros instead of ""s, a simple way
to
get a clean look would be just to suppress extraneous zeros from showing
on
the sheet, via:
Click Tools > Options > View tab > Uncheck "Zero values" > OK

 

[Biff]

Hi!

The SUM() function will ignore text so if you are using a formula that
returns formula blanks ("") it should work.

Is your sum function actually just something like this:

=A1+A2+A3+A4 ???

A formula like that above will return an error if there are text entries in
the range(which is what formula blanks are - TEXT)

If, as you say, you can't change it then a possible work around is to have
your other formula return zero and use conditional formatting to hide the
zero. Just set the font color to be the same as the fill color if the cell
value equals zero.

Biff

 

[Max]

You're welcome, Brad !

OK I feel dumb now

LOL! I felt the same way, too, until I learnt about this and the other
10,000 or so things from the kind folks here in the excel newsgroups <g>

 

[Defoes Right Boot]

Another possibility is to use the ISERROR function and nest your original IF
function within another like this :

=IF(ISERROR(original IF function),"",original IF function)

This basically says "If the original IF function returns an error, return a
blank cell, otherwise return the value from the original IF function."

HTH

Phil