#div/0 error help
 

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:M10000.001,'Filled All'!M1:M1000)))))

Any help is appreciated, Thanks!

-Chris

#div/0 error help
 

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" wrote in message
...
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:M10000.001,'Filled All'!M1:M1000)))))

Any help is appreciated, Thanks!

-Chris

#div/0 error help
 

On Feb 14, 3:09*pm, "Chip Pearson" wrote:
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)

"Chris" wrote in message

...



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:M10000.001,'Filled All'!M1:M1000)))))

Any help is appreciated, Thanks!

-Chris- Hide quoted text -

- Show quoted text -

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

#div/0 error help
 

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

=IF(SUM((rng1="JS")*(rng2="IT")*(rng3="A")*(rng40 .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)


--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
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:M10000.001,'Filled All'!M1:M1000)))))

Any help is appreciated, Thanks!

-Chris

#div/0 error help
 

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)

"Chris" wrote in message
...
On Feb 14, 3:09 pm, "Chip Pearson" wrote:
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)

"Chris" wrote in message

...



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:M10000.001,'Filled All'!M1:M1000)))))

Any help is appreciated, Thanks!

-Chris- Hide quoted text -

- Show quoted text -

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

#div/0 error help
 

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


"Chris" wrote in message ...
On Feb 14, 3:09 pm, "Chip Pearson" wrote:
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)

"Chris" wrote in message

...



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:M10000.001,'Filled All'!M1:M1000)))))

Any help is appreciated, Thanks!

-Chris- Hide quoted text -

- Show quoted text -

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

#div/0 error help
 

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

On Thu, 14 Feb 2008 15:22:30 -0800 (PST), Chris wrote:

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