#div/0!

[Silo]

Hello Everyone

I like some help with this formula...

If there's a zero in the table array 'Acc & Data'!$A:$I,9 or WAA!$A:$F,6 this returns a #DIV/0!

I try editing this with ISNA, but I get an error saying I entered too many arguments


=IF(D30>=$D$5,"New Hire",IF(A30=0,"",VLOOKUP(A30,'Acc & Data'!$A:$I,9,0)/VLOOKUP(A30,WAA!$A:$F,6,0)))

Any thoughts?

 

[CWatts]

I'm assuming you dont want it to display the errors:

Assuming A5 is 10 and C2 is 0:

If you have Excel 2007+ you can use =IFERROR(A5/C2,"") which returns a blank.

If you have excel 2003 or older you can use =IF(ISERROR(A5/C2),"",A5/C2) which will test if it's an error, and if it is displays a blank, otherwise does the formula.

Just replace A5/C2 with your formula. Is that what you were looking for?

 

[Silo]

Got it, will try Thanks

 

[Aladin Akyurek]

Hello Everyone

I like some help with this formula...

If there's a zero in the table array 'Acc & Data'!$A:$I,9 or WAA!$A:$F,6 this returns a #DIV/0!

I try editing this with ISNA, but I get an error saying I entered too many arguments


=IF(D30>=$D$5,"New Hire",IF(A30=0,"",VLOOKUP(A30,'Acc & Data'!$A:$I,9,0)/VLOOKUP(A30,WAA!$A:$F,6,0)))

Any thoughts?

If you want 0 instead of #DIV/0!...

On Excel 2007 or later...

=IFERROR(IF(D30>=$D$5,"New Hire",IF(A30=0,"",
   VLOOKUP(A30,'Acc & Data'!$A:$I,9,0)/VLOOKUP(A30,WAA!$A:$F,6,0))),0)

Otherwise...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
    IF(D30>=$D$5,"New Hire",IF(A30=0,"",
      VLOOKUP(A30,'Acc & Data'!$A:$I,9,0)/
        VLOOKUP(A30,WAA!$A:$F,6,0)))))

 

[Silo]

Excellent It worked! Thanks