Histogram

[mohan]

I would like to create a histogram, without using the recording macro function to use excel add-in toolpak.

does anyone have any ideas? many thanks

[Bob Phillips]

This array UDF migt do what you want



Function myHistoGram(rngInput As Range, rngBin As Range)
Dim m1Rows As Long
Dim m2Rows As Long, m2Cols As Long
Dim m1 As Variant, m2 As Variant
Dim i As Long, j As Long, k As Long
Dim tmp As Double
Dim rng As Range
Dim resultRows As Long, resultCols As Long

Set rng = Application.Caller
resultRows = rng.Rows.Count
resultCols = rng.Columns.Count
m1 = rngInput
m2 = rngBin
m1Rows = UBound(m1, 1) - LBound(m1, 1) + 1
m2Rows = UBound(m2, 1) - LBound(m2, 1) + 1
m2Cols = UBound(m2, 2) - LBound(m2, 2) + 1
If m2Cols > 1 Or m1Rows <> m2Rows Or m1Rows + 1 <> resultRows Then

myHistoGram = CVErr(xlErrRef)
Exit Function
End If
ReDim result_matrix(1 To resultRows + 1, 1 To 2) As Variant
result_matrix(1, 1) = "Bin"
result_matrix(1, 2) = "Frequency"
result_matrix(2, 1) = rngBin(1, 1)
For i = 2 To m1Rows

result_matrix(i + 1, 1) = m2(i, 1)
If result_matrix(i + 1, 1) < result_matrix(i, 1) Then

tmp = result_matrix(i + 1, 1)
result_matrix(i + 1, 1) = result_matrix(i, 1)
result_matrix(i, 1) = tmp
i = 1
End If
Next i
For i = 1 To m1Rows

result_matrix(i + 1, 2) = Application.CountIf(rngInput, result_matrix(i + 1, 1))
Next i
myHistoGram = result_matrix
End Function