Statistical Function

[Jonathan]

I have two sets of data that I would like to put together into the function.,
First set would be (for example) A2:A6 and A25:A30 and use a stats function
(such as correlate) to B2:B6 and B25:B30. I want them joined together but
Excel won't let me. Help please.

 

[Jerry W. Lewis]

Use a nested IF to select the data
=CORREL(IF((ROW(A2:A30)>6)*(ROW(A2:A30)<25),"",A2:A30),B2:B30)

Some functions, such as AVERAGE() support a syntax like
=AVERAGE((A2:A6,A25:A30),(B2:B6,B25:B30))
which is considered only 2 arguments (of compound arrays) rather than 4
arguments. Unfortunately this syntax does not seem to work with
CORREL(), PEARSON(), RSQ(), or even SUMPRODUCT() and {SUM(()*())}.

Jerry

 

[Jerry W. Lewis]

Use a nested IF to select the data
=CORREL(IF((ROW(A2:A30)>6)*(ROW(A2:A30)<25),"",A2:A30),B2:B30)

I failed to mention that this must be array entered (Ctrl-Shift-Enter).

Jerry