Display empty cell when the formula returns error

[Rainmanne]

I usually prefer to hide error using "" in IF construction, something like

=if(iserror(),"",())

However, if you have another formula, which depends on this cell, it turns #VALUE !, when the error is hidden. So "" does not make the cell empty. I wonder if there is any other solution to display the cell empty, which will play nicely with formulas, where it is used?

 

[JazzSP8]

Hi

=IFERROR([Formula],"")

Never mind - Read the question before I had coffee :/

 

[Peter_SSs]

However, if you have another formula, which depends on this cell, ...

1. Which cell was the original formula in (this cell) ?
2. What is the 'other' formula?

 

[Rainmanne]

1. Which cell was the original formula in (this cell) ?
2. What is the 'other' formula?

It usually depends, because I often use the IF construction to make cells empty for other reasons.
For example:

=IF(ISBLANK(B34),"",IF(E34="paid","",B34))

There are budget amounts in column B and outstanding amounts in column C (from which the formula above is taken) and a switch in column E. So if it's turned paid, then C34 should be empty. Interestingly, with SUM formula it works fine. So summing up all entries in column C works fine even if some of them turned blank using the above formula. However, if you refer to the blank cell directly (like =C34 or =G4+C34), it returns #VALUE !. Realising this, the workaround for me now is to refer to the cell through the SUM formula, like =SUM(C34), so it works. But I wonder if there is another way to display blank cells through formula and use them in other formulas without an error?

 

[Peter_SSs]

A cell containing a formula is, by definition, not empty and cannot be so.
You have discovered one option to use SUM which ignores the text value ""
Another option would be to change the formula to =IF(ISBLANK(B34),0,IF(E34="paid",0,B34)) then the zero values would not be a problem for other calculations. (Further, you could custom format or conditionally format the cell to hide the zero values if you want.)

 

[Rainmanne]

A cell containing a formula is, by definition, not empty and cannot be so.
You have discovered one option to use SUM which ignores the text value ""
Another option would be to change the formula to =IF(ISBLANK(B34),0,IF(E34="paid",0,B34)) then the zero values would not be a problem for other calculations. (Further, you could custom format or conditionally format the cell to hide the zero values if you want.)

Thanks a lot!

 

[Peter_SSs]

Thanks a lot!

You're welcome