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))))

 

[Mike Blackman]

So am I and i'm on 2007

This will work for you....

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

 

[Mokolpop]

Hello,

So im on 2003 and have tried the iserror

=IF(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)))),"",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))))

However the computer wont even execute the formula it just high lights the ""

Completely puzzled ?

REgards

Ian

 

[Mike Blackman]

You've potentially hit the 7 nested functions limit......

 

[Mokolpop]

Thanks everyone, ive come up with a hybred solution

Ive spilt the count part of the formula into another column, and added in the iserror into my formula

Thanks for your help and time



Ian

 

[Jonmo1]

Instead of ISERROR, try testing the countif for 0

=IF(count(if..)=0,"",sum(if..)/count(if..))

 

[Jonmo1]

If a 0 is acceptable instead of blank...
Give this a shot

=LOOKUP(9.99999999999999E307,CHOOSE({1,2},0,AVERAGE(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))))))

CTRL + SHIFT + ENTER