I have this correlation matrix that I wanted to complete. So far, I've been entering all the formulas in manually. I was wondering how I can use autofill to speed things up?
I've attached the file below.
I have this correlation matrix that I wanted to complete. So far, I've been entering all the formulas in manually. I was wondering how I can use autofill to speed things up?
I've attached the file below.
It's not a question of autofill. You need a formula to calculate the range you need, based on either the row or column title of the matrix.
The OFFSET function is the one to use to generate a range. It has 5 arguments: (a) place to start; (b) number of rows to move to top-left of range; (c) number of columns to move for same; (d) height of range; (e) width of range.
If I use Sheet 1 A63 as (a), then (b) is zero; (c) must be calculated; (d) is 252, the number of data points you have; (e) is 1.
To calculate (c), we can use MATCH on Sheet 1 row 17.
Do that for both parts of your PEARSON function, and you get a copyable formula, but make sure you get the $s in the right place! I think I’ve got them about right on the attached spreadsheet.
NB It will only work if the row and column titles of the matrix exactly match the text in sheet1 row 17. E.g. BOUNCE must be SAFETY_BOUNCE.
In similar vein to @ooth attached uses non-volatile INDEX construct to generate.
Given your headings list does not follow the pattern of your source sheet you need to conduct matches and as such the values on both sheets must match - this is repetition of the point made in the prior post.
Given the column of the matches are constant for each row/column it makes sense to calculate these matches once only thereby avoiding needless repetitive calculations.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you DonkeyOte!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks