iserror problem

[stephen.ditchfield]

Hello,
could you please show me how I can get rid of #DIV/0
with iserror
my formula is

=IF(L12=0,"",(G12/K12)/L12)

much appreciated
Ditchy

 

[Claus Busch]

Hi,

Am Thu, 10 Oct 2013 22:59:57 -0700 (PDT) schrieb
(e-mail address removed):

could you please show me how I can get rid of #DIV/0
with iserror
my formula is

=IF(L12=0,"",(G12/K12)/L12)

try:
=IF(OR(L12=0,K12=0),"",G12/K12/L12)
or:
=IFERROR(IF(L12=0,"",G12/K12/L12),"")


Regards
Claus B.

 

[Charlotte E.]

=IF(ISERROR((G12/K12)/L12,"",(G12/K12)/L12)

 

[joeu2004]

could you please show me how I can get rid of #DIV/0
with iserror
my formula is
=IF(L12=0,"",(G12/K12)/L12)

If you just want to avoid the #DIV/0 error:

=IF(K12*L12=0,"",G12/K12/L12)

Or if you do not require Excel 2003 compatibility:

=IFERROR(G12/K12/L12,"")

But if you require Excel 2003 compatibility or your assignment requires the
use of ISERROR:

=IF(ISERROR(G12/K12/L12),"",G12/K12/L12)

The exercise demonstrates the issue with ISERROR and why IFERROR was born:
ISERROR calculates the expression twice if there is no error.

Not a big deal for your expression. But the cause of many performance
issues when the expression involves long look-ups, for example.

 

[stephen.ditchfield]

Hi,



Am Thu, 10 Oct 2013 22:59:57 -0700 (PDT) schrieb










try:

=IF(OR(L12=0,K12=0),"",G12/K12/L12)

or:

=IFERROR(IF(L12=0,"",G12/K12/L12),"")





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thank You Claus
much appreciated, all fixed

 

[stephen.ditchfield]

=IF(ISERROR((G12/K12)/L12,"",(G12/K12)/L12)


Thank You Charlotte

much appreciated,
all fixed

 

[stephen.ditchfield]

Thank You joeu2004

much appreciated,
all fixed

 

[Claus Busch]

Hi,

Am Fri, 11 Oct 2013 01:43:52 -0700 (PDT) schrieb
(e-mail address removed):

much appreciated, all fixed

have a look for Joe's suggestion. That is the better way to do it.


Regards
Claus B.

 

[stephen.ditchfield]

Hi,

Thanks Claus,

I have another small problem with this array coming up with this #DIV/0

{=AVERAGE(IF(L9:L23<>0,L9:L23))}

would you have any suggestions how it can be fixed?

thank you
Ditchy

 

[joeu2004]

I have another small problem with this array coming up
with this #DIV/0
{=AVERAGE(IF(L9:L23<>0,L9:L23))}

Did you ever whether you are using Excel 2003/earlier or Excel 2007/later?
Or if you require Excel 2003/earlier compatibility, nevertheless?

It might save us a lot of time and space if you would tell us that. Sorry
if I overlooked it.

If you do not require Excel 2003/eariler compatibility, the simplest
solution is the following normally-entered formula (just press Enter):

=IFERROR(AVERAGEIF(L9:L23,"<>0"),"")

If you require Excel 2003/earlier compatiblity, array-enter the following
(press ctrl+shift+Enter instead of just Enter):

=IF(COUNTIF(L9:L23,"<>0")=0,"",AVERAGE(IF(L9:L23<>0,L9:L23)))

 

[stephen.ditchfield]

Hi Joeu2004

thank you for solving my array problem,
without people like you that help out novices like me, we could be fumbling around for days trying to solve these issues.
You an everyone else that helps on these sites does make Excel easier and more rewarding to learn.

Much appreciated
Ditchy
Ballarat
Australia