Skipping Rows in Formula

[Sheepdoug]

I'm trying to run a Pearson Correlation [=pearson(array1,array2)] between two columns (e.g. A and B, rows 1-20) but wanted to skip a few rows in both columns (e.g. 11-15). When I try to write the equation, skipping the rows by holding ctrl+highlighting, the equation writes itself as:

=pearson(A1:A10,A16:A20,B1:B10,B16:B20)

Which is an invalid function, since there should be only 2 arrays separated by a comma.

Obviously, I could simply delete rows 11-15, and re-organize the worksheet - but does anyone know how to adjust the formula to correlate A1:A20, B1:B20 - while skipping a few rows (in both columns)?

Thanks!

 

[shg]

=PEARSON(IF((ROW(A1:A20) <= 10) + (ROW(A1:A20) >= 16), A1:A20, FALSE), B1:B20)

Confirmed with Ctrl+Shift+Enter.

 

[Jonmo1]

Nice,

Or if there is some value in an adjescent column to indicate which rows should be included or excluded.
Like only consider values in A1:B20 IF C1:C20 = "x" <- or whatever

=PEARSON(IF(C1:C20="x", A1:A20, FALSE), B1:B20)

 

[Sheepdoug]

Perfect - thank you both!

 

[shg]

You're welcome.

I like Jonmo's suggestion better, because it moves the conditional to the sheet instead of embedded in the formula.

The ", False" could be removed from both of our formulas.