remove div/0 error values from excel cells

[gar_ka]

Hi guys,

i am stuck with a problem , how to remove div/0 value from excel cells.
i got this when i used formula where denominator is zero . is there any function to do or we should write any macro for that.

 

[JV0710]

Hi

One way to do this is to use the if statement in your formula . . .

example: in cell C1 . . . . =if(iserror(a1/b1),"",a1/b1)

 

[Sal Paradise]

If you want to retain other errors, then you can change above code to =IF(ERROR_TYPE(2),"","a1/b1")

 

[JV0710]

Thanks Sal Paradise . . . .

I definitely can use that . . . .

 

[Sal Paradise]

Your way works just as well if you don't care for errors at all (which I rarely do). So I'd probably use your way

 

[Stormseed]

If you want to retain other errors, then you can change above code to =IF(ERROR_TYPE(2),"","a1/b1")

Hi Sal

When I try this stuff on my worksheet it does give me a #NAME? error...am I missing something here?

 

[Sal Paradise]

Sorry, my apologies.

=IF(A1/B1=ERROR_TYPE(2),"",A1/B1)

I really have to proof read...

 

[Stormseed]

Sorry, my apologies.

=IF(A1/B1=ERROR_TYPE(2),"",A1/B1)

I really have to proof read...

no problem mate...great that u r sharing your knowledge !

Neverthless - it still does not seem to work...is it that it will not work with the DIV/0 error? or do we need to enable to some kind of add-in or something? it does not change a bit

 

[Sal Paradise]

You know, I'll be entirely damned. I cannot figure out why it isn't working properly.

I can get it to not display the #DIV/0! errors with this code:

Book6
	A	B	C	D
1	1	9	0.111111	#N/A
2	2	8	0.25	#N/A
3	3	7	0.428571	#N/A
4	4	6	0.666667	#N/A
5	5	5	1	#N/A
6	6	4	1.5	#N/A
7	7	3	2.333333	#N/A
8	8	2	4	#N/A
9	9	1	9	#N/A
10	10	0	#DIV/0!
Sheet1

But unfortunately, it won't properly do the math either. That's just weird. Maybe I should just keep my yap shut from now on.

 

[Jon von der Heyden]

Book1
	A	B	C	D	E
1	2	0		#DIV/0!	0
2	#N/A	15		#N/A	#N/A
3	12	15		0.8	0.8
4	3	4		0.75	0.75
5	2	3		0.666667	0.666666667
6	0	7		0	0
7	1	10		0.1	0.1
8	12	5		2.4	2.4
9	3	2		1.5	1.5
10	0	0		#DIV/0!	0
Sheet1

Sal,
It was a good suggestion and you're so close!

Formula: =IF(ISERROR(ERROR.TYPE(D1)),D1,IF(ERROR.TYPE(D1)=2,0,D1))

See here for explanation: http://support.microsoft.com/kb/213891