-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

7
  • This doesn't work in Excel 2010 or Gnumeric (I know to substitute commas with semicolons with it). 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" 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. Apr 3 '18 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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