Hi - I am trying to calculate conditional correlations. Using the attached spreadsheet as an example the goal is to calculate the correlation of Column B and C at ranges of Column B. When Column B is <0 ; is >=0,<.5 ; etc. The formula =CORREL(IF(B$2:B$508<0,$B$2:$B$508),IF(B$2:B$508<0,C$2:C$508)) works for > than but if I try to add an AND statement for the between values it does not work. Any Suggestions would be great. Thank you
AND function cannot be used in ARRAY function.
Instead of
=CORREL(IF(AND(B$2:B$508>=0,B$2:B$508<0.5),$B$17:$B$523),IF(AND(B$2:B$5508>=0,B$2:B$508<0.5),C$2:C$508))
Use ARRAY entered
=CORREL(IF((B$2:B$508>=0)*(B$2:B$508<0.5),$B$17:$B$523),IF((B$2:B$508>=0)*(B$2:B$508<0.5),C$2:C$508))
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks