How to ignore #DIV/0! in Sumproduct formula
 

Below is my formula...there are #DIV/0! in the data set...(I can't remove).
I want to exclude them in the formula. Basically...how to I exclude the
#DIV/0! in the following formula? Thanks in advance...

=(SUMPRODUCT((Main!$H$2:$H$95100)*(Main!$I$2:$I$9 5-Main!$H$2:$H$95)/(Main!$I$2:$I$95)0.5))

How to ignore #DIV/0! in Sumproduct formula
 

try adding not(iserror(Range)) to your sumproduct formula

I have not been able to figure out what you are trying to calculate with the
0.5 in the sumproduct equation
the divide by zero error may come when the I column is less than 0.5

"deeds" wrote:

Below is my formula...there are #DIV/0! in the data set...(I can't remove).
I want to exclude them in the formula. Basically...how to I exclude the
#DIV/0! in the following formula? Thanks in advance...

=(SUMPRODUCT((Main!$H$2:$H$95100)*(Main!$I$2:$I$9 5-Main!$H$2:$H$95)/(Main!$I$2:$I$95)0.5))

How to ignore #DIV/0! in Sumproduct formula
 

Thanks....I tried the not(iserror(Range)...but all it does is count the
entire range...without the criteria. the formula is trying to count the
occurances of when column H is greater than 100 and when the result of the
I-H/I is than 50%.

"bj" wrote:

try adding not(iserror(Range)) to your sumproduct formula

I have not been able to figure out what you are trying to calculate with the
0.5 in the sumproduct equation
the divide by zero error may come when the I column is less than 0.5

"deeds" wrote:

Below is my formula...there are #DIV/0! in the data set...(I can't remove).
I want to exclude them in the formula. Basically...how to I exclude the
#DIV/0! in the following formula? Thanks in advance...

=(SUMPRODUCT((Main!$H$2:$H$95100)*(Main!$I$2:$I$9 5-Main!$H$2:$H$95)/(Main!$I$2:$I$95)0.5))

How to ignore #DIV/0! in Sumproduct formula
 

Why can't you fix the formulas that return the error?


--
Regards,

Peo Sjoblom


"deeds" wrote in message
...
Thanks....I tried the not(iserror(Range)...but all it does is count the
entire range...without the criteria. the formula is trying to count the
occurances of when column H is greater than 100 and when the result of the
I-H/I is than 50%.

"bj" wrote:

try adding not(iserror(Range)) to your sumproduct formula

I have not been able to figure out what you are trying to calculate with
the
0.5 in the sumproduct equation
the divide by zero error may come when the I column is less than 0.5

"deeds" wrote:

Below is my formula...there are #DIV/0! in the data set...(I can't
remove).
I want to exclude them in the formula. Basically...how to I exclude
the
#DIV/0! in the following formula? Thanks in advance...

=(SUMPRODUCT((Main!$H$2:$H$95100)*(Main!$I$2:$I$9 5-Main!$H$2:$H$95)/(Main!$I$2:$I$95)0.5))

How to ignore #DIV/0! in Sumproduct formula
 

try
=(SUMPRODUCT((I2:I95<0)*((Main!$H$2:$H$95100)*(M ain!$I$2:$I$95-Main!$H$2:$H$95)/(Main!$I$2:$I$95)0.5))
unless there is another spot in the various ranges with /0 error
which is what I thought you had

"deeds" wrote:

Thanks....I tried the not(iserror(Range)...but all it does is count the
entire range...without the criteria. the formula is trying to count the
occurances of when column H is greater than 100 and when the result of the
I-H/I is than 50%.

"bj" wrote:

try adding not(iserror(Range)) to your sumproduct formula

I have not been able to figure out what you are trying to calculate with the
0.5 in the sumproduct equation
the divide by zero error may come when the I column is less than 0.5

"deeds" wrote:

Below is my formula...there are #DIV/0! in the data set...(I can't remove).
I want to exclude them in the formula. Basically...how to I exclude the
#DIV/0! in the following formula? Thanks in advance...

=(SUMPRODUCT((Main!$H$2:$H$95100)*(Main!$I$2:$I$9 5-Main!$H$2:$H$95)/(Main!$I$2:$I$95)0.5))

How to ignore #DIV/0! in Sumproduct formula
 

If we assume that the div errors are caused by the second part of your
formula
this might work

=SUM(IF(ISERROR((Main!I2:I95-Main!H2:H95)/(Main!I2:I95)0.5),0,(Main!I2:I95-Main!H2:H95)/(Main!I2:I95)0.5)*(Main!H2:H95100))


entered with ctrl + shift & enter

this assume that there are no errors in H2:H95

if there can be div errors there you could try

=SUM(IF(ISERROR((Main!I2:I95-Main!H2:H95)/(Main!I2:I95)0.5),0,(Main!I2:I95-Main!H2:H95)/(Main!I2:I95)0.5)*((IF(ISERROR(Main!H2:H95100),0 ,Main!H2:H95100))))

also array entered


--
Regards,

Peo Sjoblom





--
Regards,

Peo Sjoblom



"deeds" wrote in message
...
Thanks....I tried the not(iserror(Range)...but all it does is count the
entire range...without the criteria. the formula is trying to count the
occurances of when column H is greater than 100 and when the result of the
I-H/I is than 50%.

"bj" wrote:

try adding not(iserror(Range)) to your sumproduct formula

I have not been able to figure out what you are trying to calculate with
the
0.5 in the sumproduct equation
the divide by zero error may come when the I column is less than 0.5

"deeds" wrote:

Below is my formula...there are #DIV/0! in the data set...(I can't
remove).
I want to exclude them in the formula. Basically...how to I exclude
the
#DIV/0! in the following formula? Thanks in advance...

=(SUMPRODUCT((Main!$H$2:$H$95100)*(Main!$I$2:$I$9 5-Main!$H$2:$H$95)/(Main!$I$2:$I$95)0.5))

How to ignore #DIV/0! in Sumproduct formula
 

Thanks!...Worked great.

"Peo Sjoblom" wrote:

If we assume that the div errors are caused by the second part of your
formula
this might work

=SUM(IF(ISERROR((Main!I2:I95-Main!H2:H95)/(Main!I2:I95)0.5),0,(Main!I2:I95-Main!H2:H95)/(Main!I2:I95)0.5)*(Main!H2:H95100))


entered with ctrl + shift & enter

this assume that there are no errors in H2:H95

if there can be div errors there you could try

=SUM(IF(ISERROR((Main!I2:I95-Main!H2:H95)/(Main!I2:I95)0.5),0,(Main!I2:I95-Main!H2:H95)/(Main!I2:I95)0.5)*((IF(ISERROR(Main!H2:H95100),0 ,Main!H2:H95100))))

also array entered


--
Regards,

Peo Sjoblom





--
Regards,

Peo Sjoblom



"deeds" wrote in message
...
Thanks....I tried the not(iserror(Range)...but all it does is count the
entire range...without the criteria. the formula is trying to count the
occurances of when column H is greater than 100 and when the result of the
I-H/I is than 50%.

"bj" wrote:

try adding not(iserror(Range)) to your sumproduct formula

I have not been able to figure out what you are trying to calculate with
the
0.5 in the sumproduct equation
the divide by zero error may come when the I column is less than 0.5

"deeds" wrote:

Below is my formula...there are #DIV/0! in the data set...(I can't
remove).
I want to exclude them in the formula. Basically...how to I exclude
the
#DIV/0! in the following formula? Thanks in advance...

=(SUMPRODUCT((Main!$H$2:$H$95100)*(Main!$I$2:$I$9 5-Main!$H$2:$H$95)/(Main!$I$2:$I$95)0.5))

How to ignore #DIV/0! in Sumproduct formula
 

"Peo Sjoblom" wrote...
If we assume that the div errors are caused by the second part of
your formula this might work

=SUM(IF(ISERROR((Main!I2:I95-Main!H2:H95)/(Main!I2:I95)0.5),0,
(Main!I2:I95-Main!H2:H95)/(Main!I2:I95)0.5)*(Main!H2:H95100))
....

OP said there were errors in the data set. You don't need the 0.5
check in the error check.

this assume that there are no errors in H2:H95

How so? Errors in H2:H95 will propagate to the ISERROR call here.

if there can be div errors there you could try

=SUM(IF(ISERROR((Main!I2:I95-Main!H2:H95)/(Main!I2:I95)0.5),0,
(Main!I2:I95-Main!H2:H95)/(Main!I2:I95)0.5)
*((IF(ISERROR(Main!H2:H95100),0,Main!H2:H95100) )))
....

And both will capture any other errors in the data set. If the data
set contained any #REF!, #NULL! or #NAME? errors, there's seldom any
good reason to ignore them. That is, these 3 errors usually indicate
something seriously wrong, so generally best to see them rather than
mask them.

So another alternative,

=SUM(IF(ISNUMBER(1/(ERROR.TYPE(Main!H2:H95/Main!I2:I95)=2)),0,
(Main!H2:H95/Main!I2:I95<0.5)*(Main!H2:H95100)))

which only traps #DIV/0! errors and replaces (I-H)/I0.5 with the
algebraically identical but more efficient H/I<0.5. Of course, if the
#DIV/0! errors were due to zeros in Main!I2:I95, the obvious
alternative would be

=SUMPRODUCT(--(2*Main!H2:H95<Main!I2:I95),--(Main!H2:H95100))