Fairly intricate array formula question.

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

=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


"THOMAS CONLON" <[email protected]> wrote in message news:dt6yg.5746$fL3.4878@trnddc07...
>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
>

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))