Typo...

Last line should be read as:
[ARRAY FORMULA] --OR(B5, B7,B9, & B10) will return TRUE only if all four
values are ZERO


"Sheeloo" wrote:

Put an IF around your formula and use the logical condition --OR(B5, B7,B9, &
B10) and put your formula for the false result

[ARRAY FORMULA] --OR(B5, B7,B9, & B10) will return TRUE only if all four
values are non-zero

"Dan the Man" wrote:

Hello Group!

In cells B5, B7,B9, & B10 I have raw score outcomes which provide me with
the TOTAL number of individuals served in four of our programs.

In cell M16 I have a formula which provides me with a raw score outcome
(related to overall improvement) on a particular dimension.

I want to divide the total outcome (reflected in M16), by the total number
of individuals served (B5, B7, B9 & B10) in order to obtain a percentage
related to improvement across a particular category.

I have been able to accomplish my desired outcome with the following formula:

=SUMPRODUCT(--('Raw Data'!$X$4:$X$5000=DATE(2008,1,1)),--('Raw
Data'!$X$4:$X$5000<=DATE(2008,3,31)),--('Raw
Data'!$E$4:$E$5000<"CIC"),--('Raw Data'!$J$4:$J$5000<'Raw
Data'!$Z$4:$Z$5000))/SUM(B5,B7,B9,B10)

However, if cells B5, B7, B9 & B10 have a 0 in them (when certain programs
have no individuals in them), than the outcome of the above formula produces
the infamous DIV/0! error. I'm trying to find an additional piece of formula
which tell Excel to leave cell M16 blank (and not do the division) if there
are scores of "0" (no individuals) in cells B5, B7. B9, or B10.

Below is what I tried, but it didn't work, and I just got a formula error
message:

=SUMPRODUCT(--('Raw Data'!$X$4:$X$5000=DATE(2008,4,1)),--('Raw
Data'!$X$4:$X$5000<=DATE(2008,6,30)),--('Raw
Data'!$E$4:$E$5000<"CIC"),--('Raw Data'!$J$4:$J$5000<'Raw
Data'!$Z$4:$Z$5000),)IF(B5,B7,B9,B10"0",/(b5, 57, b9,b10))

Any suggestions? Thanks much,

Dan