Part of the reason that things are harder in Excel is that you have to pay attention to, and understand, the logic that underlies the formulas in Excel. That is especially true for the stem-and-leaf formula. Once you follow what the formula is doing, you will be able to construct the stem-and-leaf plot. The formula counts how often numbers appear in a distribution and outputs the leafs for each occurrence of that number.

 

The formula looks at the stem, often multiplies and adds something to the stem, and counts how many times that number is in the distribution.

 

For example, if your stem is 2 and you are looking for 20, the formula will multiply the stem by 10 and add 0. This is illustrated by the following formula in the video. The "0" tells Excel to output a 0 for every time it finds a 20 in the distribution that is in cells A2 to A156. You know the formula is telling Excel to count the number of 20s it finds because cell H7 is where the stem of 2 is and the formula tells Excel to multiply 2 by 10 and add 0.

​

=REPT("0",COUNTIF($A2:$A156, H7*10+0))

​

The same logic applies for the entire plot. You will then look for how often 21 occurs in the plot, by multiplying the stem by 10 and adding 1. See formula below where 21 is added.

​

=REPT("0",COUNTIF($A2:$A156, H7*10+0))&REPT("1", COUNTIF($A2:$A156, H7*10+1))

​

Then do that all the way to 29. Then you move to the next row and do the same thing for the stem of 3, and count how many 30s, 31s, 32s, etc. there are. After the first row, you can copy your formula down to the rest of the rows and save a lot of time (see video).

 

It is best to work with two digit numbers. If your distribution consists of 3 digit numbers, divide your data by 10. If it has four digit numbers, divide by 100 (see video). Also, if your data has decimal points (fractions of whole numbers) round your data (see video).

​

Ironically, the answer to the stem-and-leaf in R is ... stem(classdata$R_IV1), assuming the name of your data is classdata and the variable you are working with is called R_IV1. All you have to do in R is type those few characters. I strongly encourage students to use R and frequently point out that some of the tasks in this class are much harder in Excel. I should mention, however, that getting started in R usually takes some effort, but once you figure out how it works, it is much faster and easier than Excel. In addition, there are many other advantages to learning R than I can discuss in a short message.