Correlation between two columns, ignoring errors

[lisannnestolte]

Hi all,

I want to check the correlation between two columns (column B and I), taking out all the values that give an error. Furthermore if a certain nth value in column B (I) gives an error, the corresponding nth value in column I (B) should also be ignored. I did it this way but it gaves a #VALUE error.


This is the formula I used:
=CORREL(IF(ISERROR(B2:B300);"";IF(ISERROR(I2:I300);"";B2:B300));IF(ISERROR(I2:I300);"";IF(ISERROR(B2:B300);"";I2:I300)))

so basically it should be CORREL(B2:B300; I2:I300) but then ignoring all the error values and corresponding values in the other column.

Any help would be appreciated

Thanks in advance.

 

[sheetspread]

One way:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Series1	Series2				Series1a	Series2a
2	#DIV/0!	#DIV/0!		0.017918		47	20		0.017918
3	47	20				47	67
4	20	#DIV/0!				39	100
5	47	67				77	91
6	39	100				2	58
7	77	91				11	32
8	2	58				48	52
9	96	#DIV/0!				72	22
10	11	32				49	23
11	48	52				18	37
12	#DIV/0!	#DIV/0!				2	7
13	72	22				6	80
14	49	23				22	19
15	18	37				97	27
16	#DIV/0!	66				13	58
17	2	7
18	6	80
19	22	19
20	97	27
21	#DIV/0!	86
22	13	58
23	46	#DIV/0!
24	86	#DIV/0!
Sheet1

Cell Formulas
Range	Formula
I2	=CORREL(F2:F16,G2:G16)
D2	{=CORREL(IF(ISNUMBER($A$2:$A$24+$B$2:$B$24),$A$2:$A$24),IF(ISNUMBER($A$2:$A$24+$B$2:$B$24),$B$2:$B$24))}
Press CTRL+SHIFT+ENTER to enter array formulas.