Stem-and-Leaf Plots Present a Distribution of Scores in Excel

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:

image0.jpg

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.

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.

image1.jpg

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:

  1. For an intermediate display row, select the subset of scores from the data column.

    For the first row, select 90–98 from Column A.

  2. Right-click the selection and choose Copy from the pop-up menu.

  3. Select the first cell for the row where the selected scores go.

    Select cell D4 to begin the first row.

  4. Right-click the selected cell and choose Paste Special from the pop-up menu to open the Paste Special dialog box.

  5. 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.

  1. Select the first cell for the leaf row.

    Select D12, the highlighted cell in the figure.

  2. From the Math & Trig menu, select MOD to open the Function Arguments dialog box for MOD.

    image2.jpg
  3. 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.

  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:

    =MOD(D4,$C$4)
  5. 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.

blog comments powered by Disqus
Advertisement

Inside Dummies.com