J
Jason
If only it was as easy as the subject line makes it sound.
Here's the core of my problem:
I have three columns of data. I want to find (Col1/Col2)*Col3 for
each row and then sum the result. I can do this with the array
formula {=sum((Array1/Array2)*Array3)}. This works fine as long as
there are no errors (#N/A etc) in any of the arrays and as long as
Array2 has no zeros (else you get a divide by zero error).
Trouble is that my arrays will sometimes contain such values. I can
avoid the problem of errors if I do the (Col1/Col2)*Col3 for each row
in the array individually and them sum the results that are not
errors, but I cannot find a way to integrate this into the array
formula. Rather than checking for errors in each array I figure
it’s best to evaluate (Col1/Col2)*Col3 and then check for
errors, this way it incorporates the divide by zero scenario.
I have the feeling that I’m dancing around the solution using
IF, SUMIF, ISERROR() etc, but just can’t get the syntax
right…
Any thoughts are appreciated, thanks,
Jason.
example:
C1 C2 C3 C4
Array1 Array 2 Array3 =(1/2)*3
700 35 1 20
#N/A 65 1 #N/A
500 50 1 10
400 80 1 5
800 30 0 0
900 0 0 #DIV/0!
200 10 1 20
1000 120 0 0
500 25 1 20
200 100 1 2
___
Sum of Column 4 = 77 <- this is the number I'm after
===
Here's the core of my problem:
I have three columns of data. I want to find (Col1/Col2)*Col3 for
each row and then sum the result. I can do this with the array
formula {=sum((Array1/Array2)*Array3)}. This works fine as long as
there are no errors (#N/A etc) in any of the arrays and as long as
Array2 has no zeros (else you get a divide by zero error).
Trouble is that my arrays will sometimes contain such values. I can
avoid the problem of errors if I do the (Col1/Col2)*Col3 for each row
in the array individually and them sum the results that are not
errors, but I cannot find a way to integrate this into the array
formula. Rather than checking for errors in each array I figure
it’s best to evaluate (Col1/Col2)*Col3 and then check for
errors, this way it incorporates the divide by zero scenario.
I have the feeling that I’m dancing around the solution using
IF, SUMIF, ISERROR() etc, but just can’t get the syntax
right…
Any thoughts are appreciated, thanks,
Jason.
example:
C1 C2 C3 C4
Array1 Array 2 Array3 =(1/2)*3
700 35 1 20
#N/A 65 1 #N/A
500 50 1 10
400 80 1 5
800 30 0 0
900 0 0 #DIV/0!
200 10 1 20
1000 120 0 0
500 25 1 20
200 100 1 2
___
Sum of Column 4 = 77 <- this is the number I'm after
===
