Iterating a matrix operation

**invictus** · 06-15-2012, 11:13 AM

Hi All,

I have an inverted matrix and want to calculate partial correlations using the formula Pij = - Pij/sqrt(Pii*Pjj) where Pij is the elment in the i-th row and j-column of the inverted correlation matrix.

Can anyone tell me how to implement this formula into excel?

Thanks in advance

Attachment 162241

**shg** · 06-15-2012, 11:29 AM

      A- B- C- D --E-- ---F--- ---G--- ---H---
  1              i \ j       1       2       3
  2   10 20 30       1 -1.0000 -0.8944 -1.0000
  3   40 50 60       2 -1.7889 -1.0000 -0.8944
  4   70 80 90       3 -2.3333 -1.1926 -1.0000

The formula in F2 and copied right and down is

=-A2/SQRT(INDEX($A$2:$C$4, $E2, $E2) * INDEX($A$2:$C$4, F$1, F$1))

**invictus** · 06-15-2012, 07:12 PM

is there a way to do this without inserting E indices? I'm guessing I am getting no output due to that.

**shg** · 06-15-2012, 07:28 PM

      a- b- c- d ---e--- ---f--- ---g---
  1   10 20 30   -1.0000 -0.8944 -1.0000
  2   40 50 60   -1.7889 -1.0000 -0.8944
  3   70 80 90   -2.3333 -1.1926 -1.0000

e1:

=-a1 / sqrt(index($a$1:$c$3, columns($e1:e1), columns($e1:e1)) * index($a$1:$c$3, rows(e$1:e1), rows(e$1:e1)))