View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Teethless mama Teethless mama is offline
external usenet poster
 
Posts: 3,718
Default I hate the DIV/0! error! Can anyone help me make this go away?

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


"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