| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/O! Error
I would like to be abe to have the #DIV/O! error show "N/A" when this
formula does not find any values to calculate. Due to another program I am using along with excel, I cannot use ISERROR or ISERR. I know that there are more effecient ways of writing the formula but I am restricted to what is shown. =(SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A $23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4))/5 I tried using =IF(SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF ($A$23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4)/5=0,"N/A",(SUMIF($A$23:$A$361,A4,$L $23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$M$ 23:$M $361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$N$ 23:$N$361)/ COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O$ 23:$O$361)/COUNTIF($A $23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$23:$P$361)/COUNTIF($A$23:$A $361,A4))/5) But I still get the #DIV/0! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#DIV/O! Error
Your divisor is the same in all instances so all you need to do is test and
make sure there is a result 0 from your COUNTIF: =IF(COUNTIF($A$23:$A$361,A4)=0,"N/A",SUMIF(......)/5) -- Biff Microsoft Excel MVP "Chris" wrote in message ... I would like to be abe to have the #DIV/O! error show "N/A" when this formula does not find any values to calculate. Due to another program I am using along with excel, I cannot use ISERROR or ISERR. I know that there are more effecient ways of writing the formula but I am restricted to what is shown. =(SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A $23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4))/5 I tried using =IF(SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF ($A$23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4)/5=0,"N/A",(SUMIF($A$23:$A$361,A4,$L $23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$M$ 23:$M $361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$N$ 23:$N$361)/ COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O$ 23:$O$361)/COUNTIF($A $23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$23:$P$361)/COUNTIF($A$23:$A $361,A4))/5) But I still get the #DIV/0! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#DIV/O! Error
On Mar 16, 6:45*pm, "T. Valko" wrote:
Your divisor is the same in all instances so all you need to do is test and make sure there is a result 0 from your COUNTIF: =IF(COUNTIF($A$23:$A$361,A4)=0,"N/A",SUMIF(......)/5) -- Biff Microsoft Excel MVP "Chris" wrote in message ... I would like to be abe to have the #DIV/O! error show "N/A" when this formula does not find any values to calculate. Due to another program I am using along with excel, I cannot use ISERROR or ISERR. I know that there are more effecient ways of writing the formula but I am restricted to what is shown. =(SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A $23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4))/5 I tried using =IF(SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF ($A$23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4)/5=0,"N/A",(SUMIF($A$23:$A$361,A4,$L $23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$M$ 23:$M $361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$N$ 23:$N$361)/ COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O$ 23:$O$361)/COUNTIF($A $23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$23:$P$361)/COUNTIF($A$23:$A $361,A4))/5) But I still get the #DIV/0!- Hide quoted text - - Show quoted text - Thanks - this works great! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#DIV/O! Error
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Chris" wrote in message ... On Mar 16, 6:45 pm, "T. Valko" wrote: Your divisor is the same in all instances so all you need to do is test and make sure there is a result 0 from your COUNTIF: =IF(COUNTIF($A$23:$A$361,A4)=0,"N/A",SUMIF(......)/5) -- Biff Microsoft Excel MVP "Chris" wrote in message ... I would like to be abe to have the #DIV/O! error show "N/A" when this formula does not find any values to calculate. Due to another program I am using along with excel, I cannot use ISERROR or ISERR. I know that there are more effecient ways of writing the formula but I am restricted to what is shown. =(SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A $23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4))/5 I tried using =IF(SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF ($A$23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4)/5=0,"N/A",(SUMIF($A$23:$A$361,A4,$L $23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$M$ 23:$M $361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$N$ 23:$N$361)/ COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O$ 23:$O$361)/COUNTIF($A $23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$23:$P$361)/COUNTIF($A$23:$A $361,A4))/5) But I still get the #DIV/0!- Hide quoted text - - Show quoted text - Thanks - this works great! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#DIV/O! Error
Hi Chris,
I suspect that you have more than 1 divisor that = Zero. Try using an out of the way range of your worksheet and insert all of your divisor formulas something like the following. (Example uses Z1:Z5 but can use any range). Z1 = COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$M$ 23:$M$361) Z2 = COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$N$ 23:$N$361) Z3 = COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O$ 23:$O$361) Z4 = COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P$361) Z5 = COUNTIF($A$23:$A$361,A4)) Then in your formula you can test for any of the divisors equal to zero with the countif function. If no divisors equal zero then your formula else "N/A". Untested but I think it should then be something like this:- =IF(COUNTIF($Z$1:$Z$5,0) = 0, (SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A $23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4))/5, "N/A") I'll be interested to see if it works. -- Regards, OssieMac "Chris" wrote: I would like to be abe to have the #DIV/O! error show "N/A" when this formula does not find any values to calculate. Due to another program I am using along with excel, I cannot use ISERROR or ISERR. I know that there are more effecient ways of writing the formula but I am restricted to what is shown. =(SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A $23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4))/5 I tried using =IF(SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF ($A$23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4)/5=0,"N/A",(SUMIF($A$23:$A$361,A4,$L $23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$M$ 23:$M $361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$N$ 23:$N$361)/ COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O$ 23:$O$361)/COUNTIF($A $23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$23:$P$361)/COUNTIF($A$23:$A $361,A4))/5) But I still get the #DIV/0! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#DIV/O! Error
Hi again Chris,
Disregard my answer. Obviously Biff was answering around the same time that I was and when his answer arrived I realized that I had not studied the formula sufficiently well enough. The part after the + sign is not part of the divisor. -- Regards, OssieMac "OssieMac" wrote: Hi Chris, I suspect that you have more than 1 divisor that = Zero. Try using an out of the way range of your worksheet and insert all of your divisor formulas something like the following. (Example uses Z1:Z5 but can use any range). Z1 = COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$M$ 23:$M$361) Z2 = COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$N$ 23:$N$361) Z3 = COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O$ 23:$O$361) Z4 = COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P$361) Z5 = COUNTIF($A$23:$A$361,A4)) Then in your formula you can test for any of the divisors equal to zero with the countif function. If no divisors equal zero then your formula else "N/A". Untested but I think it should then be something like this:- =IF(COUNTIF($Z$1:$Z$5,0) = 0, (SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A $23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4))/5, "N/A") I'll be interested to see if it works. -- Regards, OssieMac "Chris" wrote: I would like to be abe to have the #DIV/O! error show "N/A" when this formula does not find any values to calculate. Due to another program I am using along with excel, I cannot use ISERROR or ISERR. I know that there are more effecient ways of writing the formula but I am restricted to what is shown. =(SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A $23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4))/5 I tried using =IF(SUMIF($A$23:$A$361,A4,$L$23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF ($A$23:$A$361,A4,$M$23:$M$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A $361,A4,$N$23:$N$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O $23:$O$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$ 23:$P $361)/COUNTIF($A$23:$A$361,A4)/5=0,"N/A",(SUMIF($A$23:$A$361,A4,$L $23:$L$361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$M$ 23:$M $361)/COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$N$ 23:$N$361)/ COUNTIF($A$23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$O$ 23:$O$361)/COUNTIF($A $23:$A$361,A4)+SUMIF($A$23:$A$361,A4,$P$23:$P$361)/COUNTIF($A$23:$A $361,A4))/5) But I still get the #DIV/0! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error of slope taking into account error of the data points | Excel Worksheet Functions | |||
| 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) | |||
Hybrid Mode
