Hey I am trying to correlate a bunch of different columns from one sheet into another sheet. The sheet with the columns is called "Data for correlation." In my new sheet, I have cell A1 with the formula:
=CORREL('Data for correlation'!A:A,'Data for correlation'!B:B)
This will give me the correlation of "Data for correlation" Column A vs. "Data for correlation" Column B in the cell A1. I am wondering how to fill this formula down the column A of the new sheet with each subsequent cell correlating the next column in "Data for correlation" to column A in "Data for correlation."
Ex. I want cell A2 to read
=CORREL('Data for correlation'!A:A,'Data for correlation'!C:C)
Cell A3 to read
=CORREL('Data for correlation'!A:A,'Data for correlation'!D:D)
And so on...
Is there a way to do this? Thanks for the help.
Yes, you can use INDEX
A1:
=CORREL('Data for correlation'!A:A,INDEX('Data for Correlation'!$1:$1048576,0,1+ROWS(A$1:A1)))
copied down
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks