Skipping Rows in Formula

Sheepdoug

New Member
Joined
Apr 18, 2016
Messages
2
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,678
=PEARSON(IF((ROW(A1:A20) <= 10) + (ROW(A1:A20) >= 16), A1:A20, FALSE), B1:B20)

Confirmed with Ctrl+Shift+Enter.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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)
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,678
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.
 

Forum statistics

Threads
1,078,134
Messages
5,338,432
Members
399,233
Latest member
mmgezer

Some videos you may like

This Week's Hot Topics

Top