-1

Gnumeric has a nice tool for calculating correlations, but is limited in terms of specifying your output. It looks like this, Figure 8-7 :

Correlations | pressure | distance | height
pressure     |        1 |          |
distance     | 0.9841073|        1 |
height       | 0.7633996| 0.7239509|      1

I would like to generate pairs like this:

pressure pressure  1
pressure distance  -
pressure height    -
distance pressure  0.9841073
distance distance  1
distance height    -
height pressure    0.7633996
height distance    0.7239509
height height      1

I'm fine with 1's and blanks, but it would be nice if I could avoid them. It doesn't have to be within a spreadsheet.

1

This is a pretty straightforward Index/Match:

=INDEX($B$2:$D$4,MATCH($A6,$A$2:$A$4,0),MATCH($B6,$B$1:$D$1,0))

Assuming your data is like the below:

enter image description here

  • This doesn't work in Excel 2010 or Gnumeric (I know to substitute commas with semicolons with it). – anon8723879278 Apr 3 '18 at 11:44
  • @anon8723879278 - This should work in Excel 2010...what happens when you try to use it? – BruceWayne Apr 3 '18 at 14:34
  • "Circular reference warning" – anon8723879278 Apr 3 '18 at 14:43
  • @anon8723879278 - How is your worksheet setup? That warning means you're referencing the cell the formula is actually in. In my example, I put the formula in C6. You will likely need to adjust yours. This formula will work in Excel 2010, it's just you need to tweak the ranges to match your data. – BruceWayne Apr 3 '18 at 14:55
  • It works when I input the variable names in preceding cells. Part of my question was about automatically generating these "pressure pressure, pressure distance" pairs. I apologise for not making that clear enough. – anon8723879278 Apr 3 '18 at 15:13

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.