0

A friend has a worksheet where some rows are missing data (approximately 20 arrays). She wants to do the CORREL function on these data sets but omitting the missing data sets.

For example,

enter image description here

Is it possible to concatenate the arrays on the spreadsheet for use in a function?

1

If Array (CSE) argument encounters blank cells, formula ignores them, but considers cells with zeros.

Blanks cells produces different results then, if zeros are in there.

You keep blanks or if you intend to have more accurate result, then replaces blanks with zeroes.

Situation 1:

enter image description here

  • Formula in Cell A142:

=CORREL(A131:A139,B131:B139)

  • An array (CSE) formula in Cell A144:

    {=CORREL(IF(A131:A139+B131:B139=0,"",A131:A139),IF(A131:A139+B131:B139=0,"",B131:B139))}

  • Array (CSE) formula in Cell A146 tests blanks has different result, since consider blanks as zero:

    {=CORREL((IF(ISERROR(A131:A139),"",A131:A139)),(IF(ISERROR(B131:B139),"",B131:B139)))}

Finish formula with Ctrl+Shift+Enter.


Situation 2:

Blank cell replaced with Zeros.

enter image description here

N.B.

  • Now you can find formula in Cell A142 & in Cell A146 has similar outcome.

Adjust cell references in the formula as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.