Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error: "Excel encountered an error and had to remove some formatti | Excel Discussion (Misc queries) | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
I have Error 1919 Error Configuring ODBC dataSource Database | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |