how to disregard the DIV in a range

[Guest]

2 questions

how can i get a sum formula to return a reslut when there are DIV errors in
the range?

and what does the "--" mean in this formula?
=SUMPRODUCT(--($C$11:$C$36="n"),($D$11:$D$36*P$11$36))

Help is greatly appreciated

c

 

[JE McGimpsey]

Take a look at

http://www.mcgimpsey.com/excel/doubleneg.html

 

[JE McGimpsey]

First, good practice dictates that you should eliminate the DIV errors,
first. For instance, if your existing formulas are

=A1/B1

use

=IF(B1=0,"Invalid Data",A1/B1)

SUM() will then ignore the text.

However, you *could* work around it with (array-entered:
CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(IF(ISERR(A1:A100),"",A1:A100))

 

[Guest]

J
Does this formula only work with a range? when i apply to two individual
cells it doesn't seem to work. i've entered it both as an array and normally
and it won't calculate divs.
=SUM(IF(ISERR(E43+E45),"",E43+E45))
cell e 43 has this in it: =IF(AVG!E43<1,1,ROUND(AVG!E43,0))
and cell e 45 has this in it:=IF(AVG!E45<1,1,ROUND(AVG!E45,0))

if i give a range of cells and enter as a formula it is fine
{=SUM(IF(ISERR(E18:E28),"",E18:E28))}.
the defined range of cells has the same formula from e43 and e45.

i have a feeling its a simple solution and one i should be embarassed about
not knowing!

thanks

 

[JE McGimpsey]

Yes, the array formula I gave will work only on a contiguous range.

I probably would use


E43: =MAX(ROUND(AVG!E43, 0), 1)
E45: =MAX(ROUND(AVG!E45, 0), 1)

Neither of these should ever give you a DIV/0 error by themselves, so

=E43+E45

should work as long as neither cell passes through a DIV/0 error (e.g.,
from Avg!E43 or Avg!E45).

If they are passing an error through, I'd correct the Avg!E43 and
Avg!E45 cells to avoid the error.

However, you could use

=IF(OR(ISERR(E43),ISERR(E45)),"",E43+E45)

 

[JulieD]

Hi

the way i deal with DIV errors in a range to sum is to use a IF statement in
the cell generating the DIV error to suppress it along the lines of
=IF(ISERROR(original formula),0,original formula)

for a full discussion on SUMPRODUCT including the double unary (--) check
out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
this site also has multiple examples of different ways to write a SUMPRODUCT
function with good explainations on how they work which might be worth a
read, as i'm not too sure about the format of the sumproduct function you
posted.

Hope this helps
Cheers
JulieD


for details on how sumproduct function