Modified Correlation Matrix output

[Juggler_IN]

I have a UDF for correlation matrix (enclosed); can it be modified to replace the non-result 0's (zeros) with "n/a"? For example,

When I use the function I get a 1-sided correlation matrix for --

- data:
[TABLE="width: 256"]
<tbody>[TR]
[TD]x1
[/TD]
[TD]x2
[/TD]
[TD]x3
[/TD]
[TD]x4
[/TD]
[/TR]
[TR]
[TD="align: right"]1.0000
[/TD]
[TD="align: right"]1.0000
[/TD]
[TD="align: right"]2.0000
[/TD]
[TD="align: right"]4.0000
[/TD]
[/TR]
[TR]
[TD="align: right"]4.0000
[/TD]
[TD="align: right"]3.0000
[/TD]
[TD="align: right"]4.0000
[/TD]
[TD="align: right"]2.0000
[/TD]
[/TR]
[TR]
[TD="align: right"]4.0000
[/TD]
[TD="align: right"]2.0000
[/TD]
[TD="align: right"]4.0000
[/TD]
[TD="align: right"]2.0000
[/TD]
[/TR]
[TR]
[TD="align: right"]4.0000
[/TD]
[TD="align: right"]5.0000
[/TD]
[TD="align: right"]5.0000
[/TD]
[TD="align: right"]2.0000
[/TD]
[/TR]
</tbody>[/TABLE]

- output (the correlation matrix -- '=CorrelationMatrix(Range)):
[TABLE="width: 256"]
<tbody>[TR]
[TD="align: right"]0.0000
[/TD]
[TD="align: right"]0.6831
[/TD]
[TD="align: right"]0.9272
[/TD]
[TD="align: right"]-1.0000
[/TD]
[/TR]
[TR]
[TD="align: right"]0.0000
[/TD]
[TD="align: right"]0.0000
[/TD]
[TD="align: right"]0.8919
[/TD]
[TD="align: right"]-0.6831
[/TD]
[/TR]
[TR]
[TD="align: right"]0.0000
[/TD]
[TD="align: right"]0.0000
[/TD]
[TD="align: right"]0.0000
[/TD]
[TD="align: right"]-0.9272
[/TD]
[/TR]
[TR]
[TD="align: right"]0.0000
[/TD]
[TD="align: right"]0.0000
[/TD]
[TD="align: right"]0.0000
[/TD]
[TD="align: right"]0.0000
[/TD]
[/TR]
</tbody>[/TABLE]

What i need is n/a in place of 0's.

Function CorrelationMatrix(rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim NumColumns As Integer
NumColumns = rng.Columns.Count - 1
Dim Matrix() As Double
ReDim Matrix(NumColumns, NumColumns)
For i = 0 To NumColumns
For j = i To NumColumns
If j = i Then Matrix(i, j) = False Else Matrix(i, j) = WorksheetFunction.Correl(rng.Columns(i + 1), rng.Columns(j + 1))
Next j
Next i
CorrelationMatrix = Matrix
End Function

 

[Eric W]

Function CorrelationMatrix(rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim NumColumns As Integer
NumColumns = rng.Columns.Count - 1
Dim Matrix() As Variant
ReDim Matrix(NumColumns, NumColumns)
For i = 0 To NumColumns
For j = i To NumColumns
If j = i Then Matrix(i, j) = False Else Matrix(i, j) = WorksheetFunction.Correl(rng.Columns(i + 1), rng.Columns(j + 1))
Next j
Next i

For i = 0 To NumColumns
   For j = 0 To NumColumns
       If Matrix(i, j) = 0 Then Matrix(i, j) = "n/a"
   Next j
Next i
    
CorrelationMatrix = Matrix


End Function

 

[Juggler_IN]

Thanks Eric. This meets the purpose.