How to hide "Divide by Zero" #DIV/0! error

[zombiemaster]

Hi, everyone...I'm here with another hopefully easy question!

Management has given me a pre-existing report and wants me to improve it. One of the improvements they are asking for is with a yearly formula that takes a number from a month last year and compares it to a number from the same month this year and give a percentage as the result. What they DON'T LIKE is when last year's number is ZERO, the formula is giving the #DIV/0! error, and they want it to just be a blank-looking cell instead of that "ugly" error.

The formula I'm working with is very basic:

=(H9/G9)-1

Is there a way to "hide" a formula result from view if it's giving that error? I tried conditional formatting with no luck (I hate Conditional Formatting since they changed it from Office2007, which was SO much easier INHO). I also tried something like this, thinking it would work but it didn't:

=IF(H9>0.9,(H9/G9)-1," ")

I know there's a simple solution here, so someone here will get my undying gratitude for something easy.

Thanks in advance!
~ZM~

 

[zombiemaster]

philwojo and Jonmo1 - I tried both of your suggestions, but it's still not calculating correctly if there's a zero in column G and a real number in column H. Both of those formulas come back as 0.0%.

~ZM~

 

[philwojo]

That is what it should do, you can't divide by zero.

So if you have a zero in column-G you said you wanted to show 0.0%, so that is what both our formulas did for you.

If you don't use our formulas it will go back to the divide by zero error.

 

[zombiemaster]

Ok, thanks for that info and that was totally my bad. I wish I had been awake enough to realize that was exactly what it was doing! Feeling like an idiot here...

So is there a way for it to show as an empty or blank cell if it's the result is 0.0%? I don't want the managers to get confused with a zero % in there...

~ZM~

 

[Jonmo1]

Sure, change the result of the formula from 0 to ""

=IFERROR(H9/G9-1,"")

 

[philwojo]

Jonmo1's one will work or you could update mine to change the zero to the "" as well, both formulas will do the same thing.

 

[zombiemaster]

That did the trick. Thanks to you both beyond words, and I'm sorry to have confused the situation so much!

~ZM~

 

[philwojo]

Glad to help, and glad it worked out.