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:

x1	x2	x3	x4
1.0000	1.0000	2.0000	4.0000
4.0000	3.0000	4.0000	2.0000
4.0000	2.0000	4.0000	2.0000
4.0000	5.0000	5.0000	2.0000

<tbody>
</tbody>

- output (the correlation matrix -- '=CorrelationMatrix(Range)):

0.0000	0.6831	0.9272	-1.0000
0.0000	0.0000	0.8919	-0.6831
0.0000	0.0000	0.0000	-0.9272
0.0000	0.0000	0.0000	0.0000

<tbody>
</tbody>

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.