#div/0 error help

[Chris]

I was hoping someone could help me out in making the #div/0 error
removed and replaced by a 0.
Here is my formulal:

=AVERAGE(IF('Filled All'!I1:I1000="JS",IF('Filled All'!
N1:N1000="IT",IF('Filled All'!B1:B1000="A",IF('Filled All'!
M1:M1000>0.001,'Filled All'!M1:M1000)))))

Any help is appreciated, Thanks!

-Chris

 

[Chip Pearson]

If you are using Excel 2003 or earlier, you can use a formula like

=IF(ISERROR(your_formula),0,your_formula)

In Excel 2007, you can use

=IFERROR(your_formula,0)

In both cases, substitute your formula (without the leading '=') in the
formulas above.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Chris]

If you are using Excel 2003 or earlier, you can use a formula like

=IF(ISERROR(your_formula),0,your_formula)

In Excel 2007, you can use

=IFERROR(your_formula,0)

In both cases, substitute your formula (without the leading '=') in the
formulas above.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
    Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)









- Show quoted text -

Does this mean I have to write my long formula twice?

 

[T. Valko]

You could add something like this to the beginning of your formula:

=IF(SUM((rng1="JS")*(rng2="IT")*(rng3="A")*(rng4>0.0001))=0,0,AVERAGE(IF.........)

But that'll make the formula pretty long.

You could just use your formula as is in some other cell then test that cell
like this:

=IF(ISERROR(A1),0,A1)

 

[Chip Pearson]

Does this mean I have to write my long formula twice?

Yes, but you can always copy/paste rather than type.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

If you are using Excel 2003 or earlier, you can use a formula like

=IF(ISERROR(your_formula),0,your_formula)

In Excel 2007, you can use

=IFERROR(your_formula,0)

In both cases, substitute your formula (without the leading '=') in the
formulas above.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)









- Show quoted text -

Does this mean I have to write my long formula twice?

 

[Niek Otten]

If you're <= Excel 2003, YES

of Course you can put the formula in A1, and in another cell, put

=IF(ISERROR(A1),0,A1)

That's shorter

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

If you are using Excel 2003 or earlier, you can use a formula like

=IF(ISERROR(your_formula),0,your_formula)

In Excel 2007, you can use

=IFERROR(your_formula,0)

In both cases, substitute your formula (without the leading '=') in the
formulas above.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)









- Show quoted text -

Does this mean I have to write my long formula twice?

 

[Gord Dibben]

You could use this macro to add the ISERROR to all existing formulas.

Sub ErrorTrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISERROR*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISERROR(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP