FREQUENCY DISTRIBUTION
A recent visit to a nursing home showed how many times nursing residents fell in the last 24 months. The data is 0,0,1,2,3,6,1,2,4,1,3,4,11,10,8,8,0,0,0,1. First, we would like to use Excel to make a table with frequency distribution, relative frequencies and cumulative relative frequencies
In column A type your data, but observe that your data has five 0’s, four 1’s, two 2’s, two 3’s, two 4’s, one 6, two 8’s, one 10 and one 11. Column B will be the frequency column. Type Falls in cell A1 and type Frequency in cell B1. Then type 0 in cell A2 and 5 in cell B2. Type 1 in cell A3 and 4 in cell B3. Type 2 in cell A4 and 2 in cell B4. Type 3 in cell A5 and 2 in cell B5. Type 4 in cell A6 and 2 in cell B6. Type 6 in cell A7 and 1 in cell B7. Type 8 in cell A8 and 2 in cell B8. Type 10 in cell A9 and 1 in cell B9. Type 11 in cell A10 and 1 in cell B10.
Now we will do relative Frequency. Column C will be the frequency column. Type Relative Frequency in cell C1. To calculate the relative frequencies we will need the sample size. We will put sample size in cell B12 and indicate it in cell A12. Type Sample size in cell A12. To evaluate the sample size we have to take the sum of all frequencies. In cell B12 type =SUM(b1:b10) and press Enter. We have 20 nursing home residents in our questionnaire. Lets now do relative frequencies. In cell C2 type =B2/B12. Do that for other relative frequencies. That is, In cell C3 type =B3/B12, in cell C4 type =B4/B12 and so on until in cell C10 you type =B10/B12.
Now we will do cumulative relative Frequency. Column D will be the cumulative relative frequency column. Type Cumulative Relative Frequency in cell D1. To evaluate the Cumulative Relative Frequency we have to take the sum of all relative frequencies up to that point. In cell D2 type =C2. In cell D3 type =C3+D2. Do that for other cumulative relative frequencies. That is, in cell D4 type =C4+D3 in cell D5 type =C5+D4, and so on until in cell D10 you type =C10+D9. Cell D10 should show 1.
Exercises
Exercise 1. How many residents had at least two falls in the last 24 months? Put the answer in cell B15.
Exercise 2. What percentage of the nursing home residents had at most eight falls in the last 24 months? Put the answer in cell B16.
Exercise 3. What percentage of the nursing home residents had at least four falls in the last 24 months? Put the answer in cell B17.
Exercise 4. What is the mode of this sample data. Put the answer in cell B18.