Excel Magic Trick 1136: Correlation From 4 Columns: CHOOSE Array Formula “Mash” Columns Together

See how to calculate
1) (08:00 min) Basics of Correlation
2) (02:17 min) CORREL and PEARSON functions for data from two columns
3) (03:45 min) Basics of CHOOSE function
4) (04:37 min) CHOOSE and an array constant to mash data from two columns together into a rectangular array of values. This is an array formula because it is spitting out an array of values.
5) (05:07 min) CORRELL and 2 CHOOSE functions to take x and y data from 4 columns and mash them together so the CORREL can calculate Correlation.
6) (08:36 min) Playlist of Statistics video:
Correlation videos in this playlist of Statistics Videos:
Videos #87-91
Calculate Correlation when data is in multiple columns (4 columns in this example).

[excelisfun]

Excel Magic Trick 1136: Correlation From 4 Columns: CHOOSE Array Formula “Mash” Columns Together
See how to calculate 
1) (08:00 min) Basics of Correlation
2) (02:17 min) CORREL and PEARSON functions for data from two columns
3) (03:45 min) Basics of CHOOSE function
4) (04:37 min) CHOOSE and an array constant to mash data from two columns together into a rectangular array of values. This is an array formula because it is spitting out an array of values.
5) (05:07 min) CORRELL and 2 CHOOSE functions to take x and y data from 4 columns and mash them together so the CORREL can calculate Correlation.
6) (08:36 min) Playlist of Statistics video:

Full List of  Statistics Videos:
Regression and Correlation: Videos #87-91

[lewispearson]

Spot on! Will be working my way through that playlist. I'm doing a bunch of data analysis stuff for my PhD at the moment and these videos are helping me out a tonne!

[ExploringExcelAmey]

Love to check your statistics section.. Can you teach sequencing or assignment in statistics..

[MikeDolanFliss]

Super tricky!!  I would have never thought to use choose to do that.  And no CSE to boot!

[BillSzysz]

Mike you are the Great Master. You rocks :-)))

[joeswright]

Thanks for the great trick. I would never have thought about using an array constant in the choose function. This leads me to consider this technique for the area part of index....?

[justinacciavatti]

You are the best, Mike.  Question:  Would love to see a vid that shows how to mash columns that are not all the same number of rows in length.  I find that this does not seem to work if the columns do not all have the same number of