Fairly intricate array formula question.

[THOMAS CONLON]

I have the following array formula, works correctly for me in cases where
both input values are nonblank, but i don't get the result i need if one or
the other is blank. Here's the formula:
{=SUM((data!A11:A61-AVERAGE(data!A11:A61))*(data!B11:B61-AVERAGE(data!B11:B61)))}
[Actually if you are interested, it is the crossproduct term, the numerator
of the formula given the help file for the Excel SLOPE function].

What i want the formula to do is skip any row in which a value in range
A11:A61 OR B11:B61 is blank, and continue to return the crossproduct sum for
the rest of the rows where both values are nonblank.

Any array formula experts out there know a way to do that using array
formulas?

Thanks, tom

 

[Bernie Deitrick]

=SUM(IF((A11:A61<>"")*(B11:B61<>"")=1,(data!A11:A61-AVERAGE(data!A11:A61))*(data!B11:B61-AVERAGE(data!B11:B61))))

or

=SUM(IF((A11:A61<>"")*(B11:B61<>"")=1,(data!A11:A61-AVERAGE(IF((A11:A61<>"")*(B11:B61<>""),A11:A61)))*(data!B11:B61-AVERAGE(IF((A11:A61<>"")*(B11:B61<>""),B11:B61)))))

Not sure how you wanted to handle the averages....


HTH,
Bernie
MS Excel MVP

 

[Harlan Grove]

THOMAS CONLON wrote...

I have the following array formula, works correctly for me in cases where
both input values are nonblank, but i don't get the result i need if one or
the other is blank. Here's the formula:

=SUM((data!A11:A61-AVERAGE(data!A11:A61))
*(data!B11:B61-AVERAGE(data!B11:B61))) ....
What i want the formula to do is skip any row in which a value in range
A11:A61 OR B11:B61 is blank, and continue to return the crossproduct sum for
the rest of the rows where both values are nonblank.

Any array formula experts out there know a way to do that using array
formulas?

If you mean something like

=SUM(IF(ISNUMBER(data!A11:A61)*ISNUMBER(data!B11:B61),data!A11:A61
-AVERAGE(IF(ISNUMBER(data!A11:A61)*ISNUMBER(data!B11:B61),data!A11:A61)))
*IF(ISNUMBER(data!A11:A61)*ISNUMBER(data!B11:B61),data!B11:B61
-AVERAGE(IF(ISNUMBER(data!A11:A61)*ISNUMBER(data!B11:B61),data!B11:B61))))

use the following instead.

=COVAR(data!A11:A61,data!B11:B61)
*SUMPRODUCT(ISNUMBER(data!A11:A61)*ISNUMBER(data!B11:B61))