ExcelBanter - #DIV/O! Error

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - #DIV/O! Error (https://www.excelbanter.com/excel-worksheet-functions/224499-div-o-error.html)

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!

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!

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!

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!

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!

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!