How To Do Stem And Leaf Display On Excel For Mac
Stem-and-leaf displays organize data so that an entire distribution of scores is quickly and easily comprehensible. The display breaks each score into two components: a leaf, which is usually the last digit of the score, and a stem, which is everything else. The objective is to create a layout that looks like this:
In the display (also called a stemplot), the row labeled 9 means that all scores in that row are between 90 and 99. The leaves — 0, 3, 8, and 8 — in that row stand for scores of 90, 93, 98, and 98.
Complete these five steps for each row and you have a stem-and-leaf display. To make everything look nicer for pasting to PowerPoint, merge the cell holding the.
The following figure shows an Excel worksheet with scores (in A1:A31), an intermediate display, and a stem-and-leaf display like the preceding figure. The intermediate display is the foundation for the stem-and-leaf display.
The scores are arranged in increasing order, which is how they appear in A1:A31. The intermediate display has numbers representing the stems in a column. That’s 90, 80, 70, 60, 50, and 40 in C4:C9.
The scores from Column A then are placed into the appropriate row in the intermediate display. Here are the steps for putting the data into the row:
For an intermediate display row, select the subset of scores from the data column.
For the first row, select 90–98 from Column A.
Right-click the selection and choose Copy from the pop-up menu.
Select the first cell for the row where the selected scores go.
Select cell D4 to begin the first row.
Right-click the selected cell and choose Paste Special from the pop-up menu to open the Paste Special dialog box.
In the Paste Special dialog box, choose Transpose.
This puts the selected column data into the row, with the scores in increasing order.
Going through those steps for each row completes the intermediate display.
To complete the stem-and-leaf display begin by putting the stems into a column: 9, 8, 7, 6, 5, and 4 into C12:C17. Then use the MOD function to add the leaves. The MOD function takes two arguments — a number and a divisor — and returns the remainder. Here are the steps for using MOD and finishing the display.
Select the first cell for the leaf row.
Select D12, the highlighted cell in the figure.
From the Math & Trig menu, select MOD to open the Function Arguments dialog box for MOD.
In the Function Arguments dialog box, enter the values for the arguments.
For Number, you want the corresponding entry in the intermediate table, so that’s D4.
For Divisor, you want the intermediate display number that corresponds to the stem. That’s cell C4. You also want C4 to be the divisor when you autofill the remaining cells in the row, so press the F4 key to turn C4 into $C$4.
Click OK to close the dialog box and place the calculated value into the selected cell.
The Formula bar displays the formula for D12:
Autofill the row with the same number of scores as the row in the intermediate table.
Complete these five steps for each row and you have a stem-and-leaf display.
To make everything look nicer for pasting to PowerPoint, merge the cell holding the Leaves label with a few of the cells to its right. After pasting to PowerPoint, you get a clearer picture if you stretch the copy and enlarge the font.
Consider the stem-and-leaf display next time you have to present a distribution of scores.
A stem and leaf plot (also called a stemplot) is a type of diagram used to show statistical data. It’s a way of retaining the individual data points in a diagram that often disappear with other graphical methods such as pie charts and histograms. For example, if you plot the numbers 10,11,12,13, and 14 in a pie chart, the numbers are placed into a category and you cannot see the individual numbers on the chart. With a stem and leaf, you get to see all of the original numbers.
Rename a blank Excel worksheet “Data” by clicking the bottom tab for the worksheet and typing the new name.
Rename another blank Excel worksheet in the workbook “Stem” by clicking the bottom tab for that worksheet and typing the new name.
Enter your list of numbers in column A of the “Data” worksheet.
Press “Alt” and “F11” at the same time to open the visual basic editor.
Double click “This Workbook” under Microsoft Excel Objects in the left navigation pane to open a blank code window.
Paste the following VBA code into the blank window: Sub StemAndLeaf() dataColumn = 1
'Clean everything out of the Stem worksheet. Worksheets('Stem').Cells.Clear Audio damage ronin.
'Look at the Data worksheet. Worksheets('Data').Activate
'Find the maximum value. rowPointer = 2 Do Until Cells(rowPointer, 1).Value = ' rowPointer = rowPointer + 1 Loop Maximum = Cells(rowPointer - 1, dataColumn).Value
'Set the divisor to strip off leaves. divisor = 1 Do Until Maximum / divisor <= 10 divisor = divisor * 10 Loop
'If the first digit of the largest value is less than 5, then 'use a smaller divisor. 'Otherwise you could end up with four or fewer rows in the plot. If Fix(Maximum / divisor) < 5 Then divisor = divisor * 10
'Calculate the top stem’s value. topStem = Fix(Maximum / divisor)
'Set up the Stem worksheet. Worksheets('Stem').Activate Cells(1, 1).Value = 'Count' Cells(1, 2).Value = 'Stem' Cells(1, 3).Value = 'Leaves' For rowPointer = 2 To topStem + 2 Cells(rowPointer, 2).Value = rowPointer - 2 Cells(rowPointer, 3).Value = ' ' Next rowPointer
'Calculate the counts. 'The following code is slower than it needs to be, 'but a faster code would be harder to read and understand. Movavi mac cleaner easily pro. Worksheets('Data').Activate rowPointer = 2 Do Until Cells(rowPointer, dataColumn).Value = ' measurement = Cells(rowPointer, dataColumn).Value Stem = Fix(measurement / divisor) Worksheets('Stem').Cells(Stem + 2, 1).Value = Worksheets('Stem').Cells(Stem + 2, 1).Value + 1 rowPointer = rowPointer + 1 Loop
'Calculate the shrink factor. Worksheets('Stem').Activate maximumCount = 0 For rowPointer = 2 To topStem + 2 If Cells(rowPointer, 1).Value > maximumCount Then maximumCount = Cells(rowPointer, 1).Value End If Next rowPointer
shrinkFactor = Fix(maximumCount / 50) If shrinkFactor < 1 Then shrinkFactor = 1 Cells(1, 4).Value = 'Each digit represents' + Str(shrinkFactor) + ' cases.'
'Return to the data, and fill the leaves in light of the values in the data. Worksheets('Data').Activate rowPointer = 2 Do Until Cells(rowPointer, dataColumn).Value = ' measurement = Cells(rowPointer, dataColumn).Value Stem = Fix(measurement / divisor) leaf = measurement - Stem * divisor leaf = Fix(leaf * 10 / divisor)
Worksheets('Stem').Cells(Stem + 2, 3).Value = Worksheets('Stem').Cells(Stem + 2, 3).Value + Trim(Str(leaf)) rowPointer = rowPointer + shrinkFactor Loop
'Get to the Stem worksheet. Worksheets('Stem').Activate End Sub
Press “F5” to run the code. Your Stem and Leaf plot will appear in the “Stem” worksheet.
Tips
If you change your data and need to generate a new stem and leaf plot, simply run the StemAndLeaf macro again.