Steam Leaf Plot in Excel

[Guest]

Did anybody know, how to create a steam leaf plot with Excel?

Thanks,
Tilo

 

[Guest]

To my knowledge there is no built-in ability in Excel to do a stem-leaf plot.

 

[Mike Middleton]

Ben or Tilo -

Did anybody know, how to create a steam leaf plot with Excel? <

What happens if you go to www.google.com, click on Groups, type "stem leaf
plot with Excel" (without the quotes), and click Search Groups?

You'll get fewer results if you search for "steam leaf plot with Excel."

- Mike
www.mikemiddleton.com

 

[Guest]

Ben,
Try this as a starter:

Sub StemAndLeafPlot()

Dim inRng As Range, cell As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim MaxVal As Integer, MinVal As Integer, lIndex As Integer, uIndex As Integer
Dim i As Integer, n As Integer, j As Integer

Set ws1 = Worksheets("sheet1")
With ws1
Set inRng = .Range("a1:a" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With

MaxVal = Application.Max(inRng)
MinVal = Application.Min(inRng)

lIndex = Int(MinVal / 10) * 10
uIndex = (Int(MaxVal / 10)) * 10

Set ws2 = Worksheets("sheet2")
ws2.Cells.ClearContents

i = 0
For n = lIndex To uIndex Step 10 ' Write Stems
i = i + 1
ws2.Cells(i, 1) = n
Next n

For Each cell In inRng ' Write Leaves
i = Int((cell.Value - lIndex) / 10) + 1
n = cell.Value Mod 10
j = Application.CountA(ws2.Rows(i)) + 1
ws2.Cells(i, j) = n
Next cell

End Sub

HTH