| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I hate the DIV/0! error! Can anyone help me make this go away?
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I hate the DIV/0! error! Can anyone help me make this go away?
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I hate the DIV/0! error! Can anyone help me make this go away?
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I hate the DIV/0! error! Can anyone help me make this go away?
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 "Sheeloo" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return to my previous version of Excel. HATE RIBBON!!! | Setting up and Configuration of Excel | |||
| How much I hate the ribbon | Excel Discussion (Misc queries) | |||
| I hate paths! | Excel Discussion (Misc queries) | |||
| i hate your formatting popus | Excel Discussion (Misc queries) | |||
| Another Reason to Hate Insurance Companies (as if you needed one) | Excel Worksheet Functions | |||
![Dan the Man[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
