Hide #DIV/0! when there is a formula in the cell.

[Ozhatch]

I've tried conditional formatting, and I'm unable to get this to work.

The cell contains this formula: =IF(H32="","", H32/E32). Obviously, when there is no value in E32, the sheet displays #DIV/0.

Please can somebody show me the formula for hiding the error message? I imagine it would begin with IFERROR; what after that?

Thanks in advance.

 

[jtakw]

Hi,

How about:

=IF(OR(H32="",E32=""),"", H32/E32)

 

[wwbwb]

Try:
=if(E32="","",if(H32="","", H32/E32))

 

[Ozhatch]

Thank you both. Neither solution works; the error message is still visible.

However, based on your suggestions, I edited the formula to =IFERROR(IF(H32="","", H32/E32),"") Works perfectly!

 

[Peter_SSs]

Neither solution works; the error message is still visible.

In that case I'd suggest that E32 does not contain "no value". In a vacant cell if you put the formula =LEN(E32) what does it return?

If the error is still a #DIV/0! error then perhaps E32 contains a zero or a #DIV0 ! error itself and the cell has some custom or conditional formatting that is hiding the zero/error value?

If the error has changed to #VALUE ! then perhaps E32 contains a formula that is returning, say, a space character so the cell appears empty but is actually not?

 

[Ozhatch]

In that case I'd suggest that E32 does not contain "no value".

Thanks Peter; I hadn't thought of that. E32 does have a formula in it: =SUM(E4:E31).

The solution I cobbled together seems to do the job. And the learning is invaluable.

Much appreciated.

 

[Michael M]

So, in that case wouldn't

=IF(OR(H32=0,E32=0),"", H32/E32)

work ??

 

[Peter_SSs]

Thanks Peter; I hadn't thought of that. E32 does have a formula in it: =SUM(E4:E31).

The solution I cobbled together seems to do the job.

It would & I don't really know anything about your worksheet or data, but in general terms your solution would also hide other errors that it might actually be better to know about.
For example, if E4:E31 contained another error (eg #N/A or #VALUE !) your solution would hide that & the user may just think that E32 contains zero.

This modified suggestion by jtakw for instance would deal with the zero in E32 but still make the user aware of other errors that may be in the data

=IF(OR(H32="",E32=0),"", H32/E32)

wwbwb's suggestion could have a similar modification and it may be that the H32 check should be for 0 instead of "" too?

 

[Ozhatch]

Thanks again, Peter. And thanks, too, to Michael M.

In my spreadsheet the values in E4:E31 are user-entries, i.e. dollar amounts. The cell under discussion calculates a discount percentage. Therefore there's little or no chance that a #N/A or #VALUE ! could appear.

I have no problem with seeing #DIV/0! in that cell; a couple of the less-tech-savvy users seem to think it's confusing. (Each user has her own spreadsheet to record sales.) That's why I've turned to you gurus for a solution.

Once again, thanks very much for all your input, and for Michael M's line: "The more I learn, the less I seem to know."


In "The Four Stages of Learning" I'm trying to elevate to "Conscious Incompetence."