I hate the DIV/0! error! Can anyone help me make this go away?

[Dan the Man]

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

 

[Sandy Mann]

Try:

=IF(SUM(B5,B7,B9,B10)=0,"",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))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sheeloo]

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

 

[Sheeloo]

Typo...

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

values are ZERO

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

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

 

[Teethless mama]

=IF(SUM(B5,B7,B9,B10),<your_formula>,"")

 

[Dan the Man]

Thanks Sandy and Sheeloo. Both suggestions worked.......Sandy my office
manager will love you for all the help you've given me. This spreadsheet I'm
developing is going to save her hours and hours of
time..............................

Best,

Dan

Typo...

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

values are ZERO

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

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