correl function

Hi, hope you can help.
I have a spreadsheet with a primary input column A and secondary inputs B,C,D etc

I am using the correl function to work out the correlation between the primary input A against the secondary inputs B,C,D etc trying to identify the highest correlatiion.

My problem

Input A has values of either 1 or -1 and the secondary inputs B,C,D etc has inputs 1, -1 and 0. I need the function to ignor the 0 values and only work out the correlation when the secondary inputs in columns b,c,d etc have either a 1 or -1

A,B,C,D,E,F
1,-1,1,-1,0,1
1,1,1,1,0,0
-1,1,1,0,0,0

thank you

Hi

Is this the type of thing you are chasing?


=IF(COUNTIF(B1:B3,0)>0,"",CORREL($A$1:$A$3,B1:B3))


rylo

No , i may have not explained this very well, i want the calculation to be made but just considering the rows that have a 1 or -1 in the secondary input columns ie b,c,d,e,f etc so for eg when working out the corr between say the primary input A and a secondary input say column b if a 0 is in any row of column b ignor this in the correlation equation ie

a,b
1,0
-1,1
1,1
1,1
1,1

in the above the 1st row would be ignored as there is a 0 in column b but i still want the correlation equation to take place but in this case using rows 2-5

hope this explains it better
thank you again

Hi

How would you want to handle a situation where there was a 0 partway down the column?

a,b
1,1
1,1
1,0
1,1
-1,1

Build a comparable array for the non 0 entries, or based on the first 2 rows only???


rylo

hi thank you for your help, i would need to build an array as all non 0 rows must be included in the calculation

Perhaps the following as an array (sft ctrl and enter when finished typin gthe formula)

=CORREL((IF(D3:D9<>0,C3:C9,"")),(IF(D3:D9<>0,D3:D9,"")))

A word of caution I have had different results in the past with regression functions between excel 97 and 2003 depending how they treat blank values. So check that it works on machines your using. I only have 97 in this office

Regards

Dav

Thank you for you suggestion, it produces a funny result the value in the cell shows #value ? but when you click on the formula button on the tool bar it shows the equation and a coefficient value.

Also i have used named ref in my origional formula
=CORREL(INDIRECT(B$256),INDIRECT($A262)) where B$256 is MM with a name and data range can this be incorporated into your solution
thank you

without knowing which way round your ranges are

=CORREL((IF(INDIRECT(b256)<>0,INDIRECT(b256),"")),(IF(INDIRECT(b256)<>0,INDIRECT(A236),"")))

but I say again this needs to be entered as an array holding down shift and control together then pressing enter whilst they are still depressed when you have finished typing the formula

does that work

Regards

Dav