For our first computer lecture, we would like to use the computer to evaluate the mean of the given data set. Suppose we have the following data 20 35 64 84 74.
We will find the mean in two ways. First, we will use what we have learned in the class.
Follow these steps after you open the Excel program.
Click on cell A1.
Type 20.
Click on cell A2.
Type 35.
Click on cell A3.
Type 64.
Click on cell A4.
Type 84.
Click on cell A5.
Type 74.
Now we will evaluate the sum of all data in the cell A7 .
Click on cell A7.
Type =SUM(A1:A5)
Press Enter. Cell A7 should display 277.
Click on cell B7.
Type Sum
Now we will evaluate the average of all data in the cell A10 .
Click on cell A10
Type35
Click on the cell A7
Type /
Type 5
Press Enter. Cell A10 should display 55.4.
Click on cell B10.
Type Mean.
Now, there is a short cut in Excel to evaluate the mean. We will do it in Cell B11.
Click on cell B11.
Type =AVERAGE(A1:A5)
Press Enter. Cell B11 should display 55.4.
EXERCISES
One of the advantages of using Excel is that when you change some of your data, you can obtain the new result quickly. To illustrate that, we would like to know how the mean changes if one of the elements, say 35, in our data increases.
Click on cell A2 where the 35 is located. Now type 56 instead.
Press enter.Observe how both means automatically change into 59.6.
Ex 1. Did the mean increase or decrease? Put your result in cell A13
Ex 2. Why? Put your explanation in cell A14
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.
