MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Hiding None Valid Contents

Posted by Bob Jarvis on January 27, 2001 5:08 AM

I have a spreadsheet that calculates financial ratios.
Sometimes the information is not present or zero and I get an error message (like "Div By Zero"). Is there a simple way I can hide these? (rather then using a lot of "if"'s in every formala) That is, show only a blank cell when the contents would normaly be a message?

Posted by Aladin Akyurek on January 27, 2001 5:22 AM

You might use:

=IF(ISERROR(your formula as arg here),"", your formula here)


Posted by Mark W. on January 27, 2001 3:51 PM

Bob, there's no way to supress or hide error
messages without the use of the IF() statement.
But, let me suggest that if you're only concerned
about the #DIV/0! error then it's not necessary
to perform your complete calculation twice -- once
to see if it will return a #DIV/0! error.

Instead, just test the denominator of your
expression. For example, if you want to divide
cell A1 by B1 then consider using:


Posted by Aladin Akyurek on January 28, 2001 1:46 AM

Bob: Mark's suggestion is right, even if your denominator is a complex formula. If you can, recompute the existing ratio's according to his suggestion: that is: =IF(denominator, nominator/demonitor).
If you can't recompute them for whatever reason and you do additional computations over the ratio's that you already have, you can use the suggestion I made in my first post below (I had this situation in mind, of course).


Posted by Bob Jarvis on January 28, 2001 6:22 AM

I think it often helps when we know what we "can not do" and hence save a lot of time. Actually, this little "IF" is quite elegant and I would like to say thank you. I must spend a bit more time perusing this board there are some bright suggestions here.