Calculate Moran's I and Geary’s C ratio in Excel

[yorickyorick]

Hey

How do I calculate the Moran's I and the Geary's C ratio in excel. I don't find a good tutorial on Youtube. Can someone give me 2 simple examples?

Thanks

Yorick

 

[alansidman]

If you provide the manual formulae (algerbraic) for those of us not familiar, someone might be able to help you.

 

[yorickyorick]

Hey

Thank you for answering. Can you please reformule your answer because i don't understand what you want to say?

Thanks

 

[joeu2004]

If you provide the manual formulae (algerbraic) for those of us not familiar, someone might be able to help you.

Google Moran's I and Geary's C separately. Good results:
https://en.wikipedia.org/wiki/Moran's_I
https://en.wikipedia.org/wiki/Geary's_C

 

[yorickyorick]

Hey

Thank you for your answer. This two pages don't explain me how I can calculate it in excel. I have tried to calculate it in excel but there is something wrong with my formula.

 

[joeu2004]

Google Moran's I and Geary's C separately. Good results:
https://en.wikipedia.org/wiki/Moran's_I
https://en.wikipedia.org/wiki/Geary's_C

Thank you for your answer. This two pages don't explain me how I can calculate it in excel.

I posted that information for alansidman's benefit and for other potential responders who might want the mathematical definitions. I assumed you know them already.

I believe I can help you with the Excel implementation. Download "moran and geary.xls" (click here) [1] for reference.

The mathematical formulas are easiest to implement as VBA user-defined functions. See the code below. Alternatively, right-click on a worksheet tab, click on View Code, and double-click on Module1 on the left side of the VBA window.

The function calls are demonstrated in A6 and A7 in the "data" worksheet.

(All cell references are in the "data" worksheet, unless they are qualified with a different worksheet name.)

Alternatively, I also provide an implementation with Excel formulas only. See the formulas in A3 and A4, and follow their dependencies.

For demonstration purposes, I created data in X1:X10 and "weights" in w!A1:J10, a 10-by-10 matrix.

Honestly, I do not know what values are reasonable for the real purpose of those mathematical formulas (spatial autocorrelation). I just filled the data and weights ranges with random prime numbers.

In Excel, it is difficult (for me) to formulate the double-sigma, a mathematical presentation of nested summations.

To simplify, I created helper formulas in columns Y (for Moran's I) and Z (for Geary's C). Those formulas represent Sigma(j=1 to 10) for each x.

For Moran's I, the formula in Y1 is array-entered (press ctrl+shift+Enter instead of just Enter), then copied into Y2:Y10. The formula in Y1 is:

=(X1-$A$1) * SUMPRODUCT(TRANSPOSE(w!A1:J1), $X$1:$X$10 - $A$1)

A1 has the formula =AVERAGE(X1:X10).

For Geary's C, the formula in Z1 is array-entered, then copied into Z2:Z10. The formula in Z1 is:

=SUMPRODUCT(TRANSPOSE(w!A1:J1), ($X$1:$X$10 - X1)^2)

Moran's I is calculated in A3 by the following normally-entered formula (just press Enter, as usual):

=SUM(Y1:Y10) / ( SUM(w!A1:J10) * VARP(X1:X10) )

SUM(Y1:Y10) represents Sigma(i=1 to 10).

Note an important simplification of the Moran's I Excel formula compared to the wiki mathematical formula. Ostensibly, the Excel formula is:

=n*SUM(Y1:Y10) / ( SUM(w!A1:J10) * SUMPRODUCT((X1:X10 - A1)^2) )

But note that VARP(X1:X10) is effectively SUMPRODUCT((X1:X10 - A1)^2) / n. So we might write:

=n*SUM(Y1:Y10) / ( SUM(w!A1:J10) * n*VARP(X1:X10))

Note that the n cancels out, resulting in the formula in A3.

Geary's C is calculated in A4 by the following normally-entered formula (just press Enter, as usual):

=SUM(Z1:Z10) / ( 2*SUM(w!A1:J10) * VAR(X1:X10) )

SUM(Z1:Z10) represents Sigma(i=1 to 10).

And we make a similar formula simplification, relying on the fact that VAR(X1:X10) is effectively SUMPRODUCT((X1:X10 - A1)^2) / (n-1).

As you can see, the Excel formulas in A3 and A4 return essentially the same values as the VBA UDFs that called in A6 and A7.

Actually, A3 and A6 differ by an invisible infinitesimal amount, about 6.94E-18. Such infinitesimal differences are common, due to anomalies of 64-binary floating-point arithmetic.

The VBA UDFs....

Function moransI(xRng As Range, wRng As Range) As Double
    ' x must be column of length n
    ' w must be n-by-x matrix
    Dim x As Variant, w As Variant
    Dim n As Long, i As Long, j As Long
    Dim sumW As Double, xBar As Double, xVar As Double, ss As Double
    x = xRng
    w = wRng
    n = UBound(x, 1)
    sumW = WorksheetFunction.Sum(w)
    xBar = WorksheetFunction.Average(x)
    xVar = WorksheetFunction.VarP(x)
    For i = 1 To n: For j = 1 To n
        ss = ss + w(i, j) * (x(i, 1) - xBar) * (x(j, 1) - xBar)
    Next j, i
    ' simplification:
    ' note that VARP(x) = SUMPRODUCT((x(i,1)-xBar)^2)/n
    ' so:
    ' n*ss / (2*sumW*SUMPRODUCT((x(i,1)-xBar)^2))
    ' = n*ss / (2*sumW*n*xVar)
    ' which equals:
    moransI = ss / (sumW * xVar)
End Function


Function gearysC(xRng As Range, wRng As Range) As Double
    ' x must be column of length n
    ' w must be n-by-x matrix
    Dim x As Variant, w As Variant
    Dim n As Long, i As Long, j As Long
    Dim sumW As Double, xBar As Double, xVar As Double, ss As Double
    x = xRng
    w = wRng
    n = UBound(x, 1)
    sumW = WorksheetFunction.Sum(w)
    xBar = WorksheetFunction.Average(x)
    xVar = WorksheetFunction.Var(x)
    For i = 1 To n: For j = 1 To n
        ss = ss + w(i, j) * (x(i, 1) - x(j, 1)) ^ 2
    Next j, i
    ' simplification:
    ' note that VAR(x) = SUMPRODUCT((x(i,1)-xBar)^2)/(n-1)
    ' so:
    ' (n-1)*ss / (2*sumW*SUMPRODUCT((x(i,1)-xBar)^2))
    ' = (n-1)*ss / (2*sumW*(n-1)*xVar)
    ' which equals:
    gearysC = ss / (2 * sumW * xVar)
End Function

-----
[1] https://app.box.com/s/s7exh9kacwcb1vfxmd2wbr4w9s9emj7d