how to format decimal in list box?

[Ms.Sigma]

I need to display correlation matrix in the list box. All the correlation coefficients have infinite decimals. How to display the precision of 4 for the decimal?

Another question is: how to display 0.55 rather than .55?

 

[Ms.Sigma]

help!

 

[Ms.Sigma]

anyone knows?

 

[Norie]

Where is the data coming from?

 

[Ms.Sigma]

There are several columns in one worksheet. I wrote macros to calculate correlation matrix of these columns and make the result to be displayed in the list box of user form.

 

[Ms.Sigma]

 

[RoryA]

You haven't said how you are populating the listbox - in code, using .List, .AddItem or .Rowsource?

 

[shg]

http://www.mrexcel.com/forum/showthread.php?t=533208

 

[Ms.Sigma]

Here is the macros for list box:

Private Sub ShowCorrelation_Click()

Dim series As Range
Dim correlation As Variant
Set series = Range(RefEdit1.Text)
correlation = CorrelationMatrix(series)

With ListBox1
.Clear
.Font.Size = 10
.List() = correlation

End With

End Sub

My macros for CorrelationMatrix is:

Function CorrelationMatrix(rng As Variant) As Variant
' Returns correlation matrix of a range
Dim i As Integer, j As Integer, K As Integer, ncols As Integer, nrows As Integer
Dim r1vector() As Variant
Dim r2vector() As Variant
Dim Cmatrix() As Variant
ncols = rng.Columns.Count
ReDim Cmatrix(ncols, ncols)
nrows = rng.Rows.Count
ReDim r1vector(nrows)
ReDim r2vector(nrows)
For i = 1 To ncols
For K = 1 To nrows
r1vector(K) = rng(K, i)
Next K
Cmatrix(i, i) = 1
For j = i + 1 To ncols
For K = 1 To nrows
r2vector(K) = rng(K, j)
Next K

Cmatrix(i, j) = Application.WorksheetFunction.Correl(r1vector, r2vector)
Cmatrix(j, i) = Cmatrix(i, j)
Next j
Next i

CorrelationMatrix = Cmatrix

End Function

You haven't said how you are populating the listbox - in code, using .List, .AddItem or .Rowsource?

 

[RoryA]

Change this:

Cmatrix(i, j) = Application.WorksheetFunction.Correl(r1vector, r2vector)

to:

Cmatrix(i, j) = Format$(Application.WorksheetFunction.Correl(r1vector, r2vector),"0.0000")