Evaluate a range of cells for true or false condition (Div/0)
 

I want to check a range of cells for an all zero condition to preclude a
divide by zero error. If any of the cells (in the range) contain non-zero
data then proceed with the remaining calculation. If all the cells (in the
range) are zeros, then stop and optionally present either a meaning full
message (my composition) or just leave blank ("").

=IF(logical_test,SUMIF(B1:B20,"E",A1:A20)/COUNTIF(B1:B20,"E"),"")
--
Bill Billmire

Evaluate a range of cells for true or false condition (Div/0)
 

You might try something like the following. Be warned, though, it will
#VALUE out if you have non-numeric text in a cell.
=IF(SUM(ABS(A1:A20))=0, "All zeros","at least one non-zero")

This adds up the absolute values of A1:A20 and if that SUM is 0 then all
elements in A1:A20 are 0. If any cell is non-zero, the SUM will be non-zero.
This uses absolute values to sum up. This will prevent negative and positive
values cancelling out one another. This is an array formula so you must
press CTRL SHIFT ENTER rather than just ENTER when you first enter the
formula and whenever you edit it later. If you do this properly, Excel will
display the formula in the formula bar enclosed in curly braces { }. See
www.cpearson.com/Excel/ArrayFormulas.aspx for more info about array
formulas.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Bill Billmire" wrote in message
...
I want to check a range of cells for an all zero condition to preclude a
divide by zero error. If any of the cells (in the range) contain non-zero
data then proceed with the remaining calculation. If all the cells (in
the
range) are zeros, then stop and optionally present either a meaning full
message (my composition) or just leave blank ("").

=IF(logical_test,SUMIF(B1:B20,"E",A1:A20)/COUNTIF(B1:B20,"E"),"")
--
Bill Billmire

Evaluate a range of cells for true or false condition (Div/0)
 

Thanks Chip for the quick response...

Unfortunately the range of data that I want to check for a zero condition is
alpha.
For example column "A" is a range of numbers, column "B" is a code letter
"A-Z" that will represent a category for the corresponding number. I need to
test column "B" for zero entrys to preclude a #DIV/0 error for the specific
"code letter".

Current Formula I'm working with (see below) - except the hard coded "20"
limits the functions ability to scale when the data grows without needing to
edit the formula as the data grows.

=IF(COUNTBLANK(B1:B20)=20,"Empty",SUMIF(B1:B20,"E" ,A1:A20)/COUNTIF(B1:B20,"E"))

Bill Billmire -

"Chip Pearson" wrote:

You might try something like the following. Be warned, though, it will
#VALUE out if you have non-numeric text in a cell.
=IF(SUM(ABS(A1:A20))=0, "All zeros","at least one non-zero")

This adds up the absolute values of A1:A20 and if that SUM is 0 then all
elements in A1:A20 are 0. If any cell is non-zero, the SUM will be non-zero.
This uses absolute values to sum up. This will prevent negative and positive
values cancelling out one another. This is an array formula so you must
press CTRL SHIFT ENTER rather than just ENTER when you first enter the
formula and whenever you edit it later. If you do this properly, Excel will
display the formula in the formula bar enclosed in curly braces { }. See
www.cpearson.com/Excel/ArrayFormulas.aspx for more info about array
formulas.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Bill Billmire" wrote in message
...
I want to check a range of cells for an all zero condition to preclude a
divide by zero error. If any of the cells (in the range) contain non-zero
data then proceed with the remaining calculation. If all the cells (in
the
range) are zeros, then stop and optionally present either a meaning full
message (my composition) or just leave blank ("").

=IF(logical_test,SUMIF(B1:B20,"E",A1:A20)/COUNTIF(B1:B20,"E"),"")
--
Bill Billmire

Evaluate a range of cells for true or false condition (Div/0)
 

OK - I think I got it...

This logic counts the number of blank cells in the range and compares that
to the total number of cells in the range. So if 20=20, [true] = "Empty",
else [false] and calculate the sumif()/countif(). Tested and no more
#DIV/0... And its scalable...

=IF(COUNTBLANK(B1:B20)=ROWS(B1:B20),"Empty",SUMIF( B1:B20,"E",A1:A20)/COUNTIF(B1:B20,"E"))

"Bill Billmire" wrote:

Thanks Chip for the quick response...

Unfortunately the range of data that I want to check for a zero condition is
alpha.
For example column "A" is a range of numbers, column "B" is a code letter
"A-Z" that will represent a category for the corresponding number. I need to
test column "B" for zero entrys to preclude a #DIV/0 error for the specific
"code letter".

Current Formula I'm working with (see below) - except the hard coded "20"
limits the functions ability to scale when the data grows without needing to
edit the formula as the data grows.

=IF(COUNTBLANK(B1:B20)=20,"Empty",SUMIF(B1:B20,"E" ,A1:A20)/COUNTIF(B1:B20,"E"))

Bill Billmire -

"Chip Pearson" wrote:

You might try something like the following. Be warned, though, it will
#VALUE out if you have non-numeric text in a cell.
=IF(SUM(ABS(A1:A20))=0, "All zeros","at least one non-zero")

This adds up the absolute values of A1:A20 and if that SUM is 0 then all
elements in A1:A20 are 0. If any cell is non-zero, the SUM will be non-zero.
This uses absolute values to sum up. This will prevent negative and positive
values cancelling out one another. This is an array formula so you must
press CTRL SHIFT ENTER rather than just ENTER when you first enter the
formula and whenever you edit it later. If you do this properly, Excel will
display the formula in the formula bar enclosed in curly braces { }. See
www.cpearson.com/Excel/ArrayFormulas.aspx for more info about array
formulas.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Bill Billmire" wrote in message
...
I want to check a range of cells for an all zero condition to preclude a
divide by zero error. If any of the cells (in the range) contain non-zero
data then proceed with the remaining calculation. If all the cells (in
the
range) are zeros, then stop and optionally present either a meaning full
message (my composition) or just leave blank ("").

=IF(logical_test,SUMIF(B1:B20,"E",A1:A20)/COUNTIF(B1:B20,"E"),"")
--
Bill Billmire