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~

 

[philwojo]

Just add in the ISERROR function or check to see if G9 is zero first.

 

[sheetspread]

With conditional formatting, you'd change the font to background color. Otherwise an iferror formula can return "".

 

[zombiemaster]

Thanks philwojo - I'm not familiar with ISERROR but whatever it is, it's working!

Sheetspread - that's what I thought to do as well, is change the font to the same color as the fill, but for some reason it didn't work (again, I liked 2007 much better).

Thanks to you both for the quick responses, I truly appreciate it and I'm sure to get a HUGE RAISE because of those errors being gone! (note the sarcasm there, of course)

Thank you!!

~ZM~

 

[offthelip]

I have this macro which I keep in personal.xlb that I run to wrap the "iferror" fiunction around whichever formular is causing the problem.

Sub addiferror()cform = ActiveCell.Formula
cform2 = Mid(cform, 2)
cform3 = "=iferror(" & cform2 & ",0)"


 ActiveCell.Formula = cform3
End Sub

 

[philwojo]

So what did you end up with for a formula, out of curiosity, that is working for you.

 

[sheetspread]

that's what I thought to do as well, is change the font to the same color as the fill, but for some reason it didn't work

Excel 2010
	A
1	#DIV/0!
2	3
3	4
4	#DIV/0!
5	4
6	4
7	5
8	#DIV/0!
Sheet5

Cell Formulas
Range	Formula
A1	=1/0
A4	=1/0
A8	=0/0

=ISERROR($A1:$A8) as the rule will white out errors

 

[zombiemaster]

Spoke too soon, and I'm still struggling it seems. Maybe it would help if I showed you what I'm seeing, but I dont' know how to insert a screenshot here.

Here's a rough indication:

2017.....2018.....% Change
.....3..........0..........-100%
.....0..........2..........#DIV/0!

When I use =iserror(H9/G9)-1 in the third column, the #DIV/0! changes to 0.0% which is what I was hoping for. So I carried that formula through to the rest of my sheet but now when I have non-zero figures in column G, it's not calculating correctly:

2017.....2018.....% Change
.....4..........9..........-100%

That 100% should be 125% but it's not. What am I missing?

Thanks as always,
~ZM~

 

[philwojo]

try this:

=if(g9=0,0,((h9/g9)-1))

 

[Jonmo1]

Try adding IFERROR to your original formula

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