Removing a #Div/0!

[Mokolpop]

Hello,

Im getting a #div/0! message from the below formula, the input range depending on the scenario, will in most instances always have part of the Input data missing, (This isnt an error its just how the data set will happen)

In the below formula is their something I can add to it that if a Div error message would be returned I can just add a zero ?

Kind Regards

Ian


=SUM(IF('Support Phase 1 Inputs'!$C$5:$C$128=$N100,IF('Support Phase 1 Inputs'!$D$5:$D$128=$O100,IF('Support Phase 1 Inputs'!$G$4:$Q$4=W$99,'Support Phase 1 Inputs'!N$5:N$128))))/COUNT(IF('Support Phase 1 Inputs'!$C$5:$C$128=$N100,IF('Support Phase 1 Inputs'!$D$5:$D$128=$O100,IF('Support Phase 1 Inputs'!$G$4:$Q$4=W$99,'Support Phase 1 Inputs'!N$5:N$128))))

 

[DauntingGecko]

=iserror()

 

[Mike Blackman]

Hi,

You can use IFERROR to supress the error...

=IFERROR(SUM(IF('Support Phase 1 Inputs'!$C$5:$C$128=$N100,IF('Support Phase 1 Inputs'!$D$5:$D$128=$O100,IF('Support Phase 1 Inputs'!$G$4:$Q$4=W$99,'Support Phase 1 Inputs'!N$5:N$128))))/COUNT(IF('Support Phase 1 Inputs'!$C$5:$C$128=$N100,IF('Support Phase 1 Inputs'!$D$5:$D$128=$O100,IF('Support Phase 1 Inputs'!$G$4:$Q$4=W$99,'Support Phase 1 Inputs'!N$5:N$128)))) ,"")

 

[Mokolpop]

Hi,

Ive just tried it, so the formula becomes

=ISERROR(SUM(IF('Support Phase 1 Inputs'!$C$5:$C$128=$N100,IF('Support Phase 1 Inputs'!$D$5:$D$128=$O100,IF('Support Phase 1 Inputs'!$G$4:$Q$4=W$99,'Support Phase 1 Inputs'!N$5:N$128))))/COUNT(IF('Support Phase 1 Inputs'!$C$5:$C$128=$N100,IF('Support Phase 1 Inputs'!$D$5:$D$128=$O100,IF('Support Phase 1 Inputs'!$G$4:$Q$4=W$99,'Support Phase 1 Inputs'!N$5:N$128)))))

But now instead of the right value it returns a false answer.

Sorry for being daft ?

Regards

Ian

 

[Mokolpop]

Hi Mike,

So my formula becomes

=IFERROR(SUM(IF('Support Phase 1 Inputs'!$C$5:$C$128=$N100,IF('Support Phase 1 Inputs'!$D$5:$D$128=$O100,IF('Support Phase 1 Inputs'!$G$4:$Q$4=W$99,'Support Phase 1 Inputs'!N$5:N$128))))/COUNT(IF('Support Phase 1 Inputs'!$C$5:$C$128=$N100,IF('Support Phase 1 Inputs'!$D$5:$D$128=$O100,IF('Support Phase 1 Inputs'!$G$4:$Q$4=W$99,'Support Phase 1 Inputs'!N$5:N$128)))),"")

But now instead of the value it returns #name?

Sorry for being a pain

ian

 

[Mike Blackman]

For Gecko's suggestion it needs to be...

=IF(ISERROR( Your Formula ),"",Your Formula)

If your using 2007 or onwards then go with mine.

 

[DauntingGecko]

ISERROR dude. Not IFERROR.

 

[ayazgreat]

What Version of office are you using ?

 

[Mike Blackman]

Excel 2007 onwards IFERROR exists...

=IFERROR( Your Formula, Desired Value if Error)

I'm assuming your on 2003?

 

[Mokolpop]

Excel 2003 dude, (Im at work)