Turn formula cell to Blank if range in formula is empty

Hi All.
D4 =SUM(D3+B4)-C4
formula copy down to column D
I could see the first computed result drag down.
how do i make the result turn to blanks if the row to the formula don't have data?
I've used IF function and "", the same result
also how do i turn blank spaces (within the formula) into dashes (-)
Thanks.

Firstly you don't need the sum function. D3+B4-C4 is sufficient.

To return blanks when there is no data the easiest way is to wrap an if formula. Depending on how your data is structured I'm assuming you will want to check the present line so maybe try this
=if(B4="","",D3+B4-C4)

Finally if you want to replace spaces with dashes a substitute formula will do this but I can't see how you'd have spaces in this formula as it looks like it is numerical not strings.

Pretty much everyone who logs on here wants formula help.

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

Hi Crooza,
I have tried your suggestion. i got what i wanted, but when i input C, D cell continue to be empty and doesn't compute.
i tried double IF argument, '=IF(B4="","",IF(C4="","")),D3+B4-C4, i got error #value error message
what do you suggest?
thanks for your help

Upload your file so I can see it. I'm flying blind without it.

In the meantime the syntax of this

IF(B4="","",IF(C4="","")),D3+B4-C4 is incorrect

Maybe this will be better

IF(B4="","",IF(C4="","",D3+B4-C4))

Which could be simplified with an and statement too but first let's see if that fixes your problem. If not upload your file like I said above and we'll see if we can fix it.

The reason you get an error on the original formula is because "" is seen as text so can't be used in math calculations, resulting in an error

Try this instead:

=IF(B4=0,0,D3+B4-C4)

If you want to check both B4 and C4 before calculating D4, you can use an AND statement in the IF formula, like this:

=IF(AND(B4=0,C4=0),0,D3+B4-C4)

You can replace the AND with OR if you want to check if either is true.

Hi shirleyxls.
thanks for the solution, but i still got 0 values if i copy the formula down.
are there ways to turn 0 into blanks?
Thanks

So did this work?

=IF(B4="","",IF(C4="","",D3+B4-C4))

You can simplify further by

IF(and(B4="",C4=""),"",D3+B4-C4)

So did this work?

=IF(B4="","",IF(C4="","",D3+B4-C4))

This will look for a blank in either cell. I think you want to find blanks in both cells so try this

IF(and(B4="",C4=""),"",D3+B4-C4)

There are no 0s in the attached file where the formula has been copied down.

To show zeros as blanks, go to:

File > Options > Advanced

Scroll down until you see Display Options for this worksheet, and uncheck the box for Show a zero in cells that have a zero value.

Note that ALL zero values on this sheet will displayed as blanks.

Originally Posted by getafixkwik

Crooza.
IF(and(B4="",C4=""),"",D3+B4-C4) Works!
Thank you!

Great. Glad it worked