mohan
03-05-2008, 09:41 AM
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
03-05-2008, 10:27 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.