CODEWITHSUNDEEP
excel
Daniel Wyatt
Daniel Wyatt

Reputation: 1151

Calculate the Correlation of two arrays where each array is disjointed

I have data similar to the following in Excel:

             A           B
 1           1,287.90    221,300 
 2           0           0
 3           1,828.93    64,080 
 4           417.81      0
 5           397.37      76,529 
 6           1,371.69    0
 7           1,316.81    50,761 
 8           1,828.93    28,645

I want to calculate the correlation coefficient of these two arrays where there are non-zeros. I want to do this without copying the data to a separate sheet with the zeros removed. I have tried this for example:

CORREL({A1,A3,A5, A7:A8}, {B1,B3,B5, B7:B8}) but this does not work. Any ideas

Upvotes: 1

Views: 315

Answers (1)

Pspl
Pspl

Reputation: 1474

Correct me if I'm wrong, but I think you don't have to do nothing:

correlation

As you can see the correlation result is the same. Did I misunderstand your question?

Update: From your comment I suppose the dashed cells on your question may represent zeros and you want to remove those values from the result. If that's the case you could place the formula:

{=CORREL(IF(A1:A8*B1:B8<>0;A1:A8);B1:B8)}

Don't forget to enter the formula by Ctrl + Shift + Enter to place the brackets.

Upvotes: 3

Related Questions