Fairly intricate array formula question.


T

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
 
Ad

Advertisements

B

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
 
Ad

Advertisements

H

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Intricate dupe-macro question 1
Fairly Complicated Calendar question 1
Array Formulas 1
Array formula 1
Array formulas 1
Array Formula 5
Fairly easy one.. 4
Wildcards in array formulas? 8

Top