Agriculture Technology Class
Spreadsheet Unit – Lesson #9
Part 1
Many of the worksheets you will be creating won't be complete until you throw in a few statistics. Today, you're going to learn how to use functions to simplify the process of calculating averages, deviations, maximums, modes and more.
Let's say that you work for a motion picture studio. Your studio is on the verge of releasing what it hopes will be a blockbuster summer picture. However, an ending for the film has not yet been agreed upon, so there are actually two versions of the film in existence.
In order to help choose an ending for the film, the studio created a focus group in a large American city.
Ten individuals, representing a randomly selected sample of the movie going population in this city, were divided into two groups with five members each. The first group was asked to view version 1 of the film. The second group was asked to view version 2 of the film.
At the end of the preview, the participants were asked to rate the film on a scale of 1 to 10, with the number 10 representing the highest possible rating. In other words, participants should give the film a rating of 10 if they consider it to be a contender for the academy award or the number 1 if they felt the film was a turkey.
Your job is to create an Excel worksheet that will make sense of data collected from the focus group participants. Because the study will be repeated in nine other cities, your worksheet will need to be designed for reuse.
The survey is completely anonymous, so you won't need to worry about identifying the participants. You will simply refer to them as 'participant 1,' 'participant 2' and so on.
You decide to create a worksheet containing all the data collected from the participants and the following statistics:
Average  also known as the arithmetic mean, this value will be calculated by adding all the ratings from one group together and dividing by the number of participants. One might be tempted to interpret this lone statistic as if it represented a consensus rating. However, it can be dangerous to rely on averages alone. A rating of 1 and a rating of 9 will both average to 5, as will a rating of 4 and a rating of 6. But the former scores reflect a high level of disagreement, while the latter scores reflect a fair amount of agreement. In order to completely understand the meaning of an average of a group of numbers, it is important that we also calculate the standard deviation of those numbers.
Standard Deviation  this number will describe how far your participants' ratings diverge from one another. A low standard deviation will indicate that there was a high degree of agreement. A high standard deviation will indicate polarizationparticipants either loved your film or hated it.
Mode  this value will represent the most common rating given by participants to your film.
Max  this value will represent the highest rating given to your film by any participant.
Min  this value will represent the lowest rating given to your film by any participant.
Part 2 –In reading assignment
Let's begin by starting Excel. Once Excel is ready, please place your cell pointer in cell A1 of a new worksheet. Let's insert some labels in column A. Please type the information below in the cells indicated:
A1: Study 1
A2: Participant 1
A3: Participant 2
A4: Participant 3
A5: Participant 4
A6: Participant 5
A7: Average
A8: Std Dev
A9: Mode
A10: Max
A11: Min
Adjust the width of column A as needed, and then move your pointer to B1. Type the following values in this column:
B1: Version 1
B2: 7
B3: 5
B4: 7
B5: 7
B6: 5
Now, go to C1 and fill in the final column as follows:
C1: Version 2
C2: 9
C3: 4
C4: 5
C5: 3
C6: 9
Now, click the 'File' menu and choose 'Save As' to save your worksheet. Name the file 'stats.xls' and click the 'Save' button.
Part 3
Time to do some statistics!
First, we'll need to calculate some averages. Although you could probably write an equation that would sum the numbers in column B or C and divide that total by 5 (the total number of responses), I don't recommend it. Let Excel do all the work for you instead!
First, position the cell pointer in cell B7.
Write down or memorize the addresses of the first and last cells in the column that will need to be averaged. In this column, we will want to average cells B2 through B6, inclusive. We must remember these addresses because we will need to supply this information to Excel shortly.
Note: after you begin writing your function, most versions of Excel will allow you to identify the cells you want to average by dragging your mouse through the cells. However, I find that it is easier and usually more accurate to type in the addresses rather than point them out with your mouse.
Once you know which cells you want to average, click the 'Paste Function' button (or, in older versions of Excel, the 'Function Wizard' button). This button is on your standard toolbar, just to the right of the AutoSum button. The button features an oversized lowercase 'f' next to a small 'x.'
Functions are procedures Excel can carry out on your numbers in order to simplify complex mathematical tasks. When you click the button described in the previous paragraph, a dialog box should appear. On the left side of this dialog box, you will see a list of function categories.
You will notice that Excel has functions that can help you carry out a wide variety of financial, logical and mathematical activities. You will learn more about these types of functions in upcoming lessons.
We need to do some statistics on the data we've collected, so look for the word 'Statistical' in the column on the left and click on it.
The column on the right side of the dialog box will fill with a list of functions that can help you calculate a wide variety of statistical measures.
To find out more about a statistical function listed in the column on the right, click once on its name. A brief description of the function should appear at the bottom of the dialog box.
Unfortunately, these descriptions are often less than uselessfilled with jargon that only a person with a doctorate in statistics could understand. If you crave a more detailed, plainEnglish descriptions of most all of Excel's statistical functions, I recommend the book entitled "Using Excel" from Que Publishers.
I usually rely on the name of a function rather than the description to find the one I think I need. We want to calculate an average at this time, so I think it would be appropriate to click the function named 'AVERAGE.'
Once you've selected the 'AVERAGE' function, click the 'OK' button (or the 'Next' button in older versions of Excel) to continue to the next step.
Another dialog box should appear. This dialog box will contain two or more text boxes. Click in the first of these text boxes and type the addresses of the cells you wish to average. In this case, you should type the following:
B2:B6
This tells Excel that you would like to average the cells between B2 and B6 (inclusive). You are not writing an equation here, so you do not need to type an = sign in front of the addresses.
When you finish typing the addresses, click the OK button. You will be returned to the worksheet, and the number 6.2 (representing the average score for this version of your film) should appear in cell B7.
With your pointer sitting in cell B7, take a peek at the formula bar. Notice how Excel has inserted the following function into this cell:
=AVERAGE(B2:B6)
The function literally says "Average the cells from B2 through B6."
Now, let's copy this function over to cell C7. When you copy the function, Excel will change the column letters so the function will work in its new location.
Make sure that your cell pointer is still sitting in cell B7. Position your mouse over the copy handle so that the mouse pointer looks like a black cross. Hold down your left mouse button and drag the copy handle to cell C7. When you reach cell C7, release the mouse button and your function will be copied and Excel will calculate the average for this column of numbers to be 6.
Click your mouse once on cell C7 and glance at the formula bar. Notice that this copy of the function says:
=AVERAGE(C2:C6)
Click the 'File' menu and choose 'Save' to save your worksheet again.
That takes care of our averages. Now, move the cell pointer to cell B8 so we can calculate the standard deviation of the values in cells B2 through B6.
Click the 'Paste Function' button (or, in older versions of Excel, the 'Function Wizard' button). Again, this button is on your standard toolbar, just to the right of the AutoSum button. The button features an oversized lowercase 'f' next to a small 'x.'
A dialog box should appear. Once more, we'll need to do some statistics. Click on the word 'Statistical' in the leftmost column in the dialog box and click on it.
Now, scroll down through the list of functions on the right until you locate the function named 'STDEV.' Click once on 'STDEV' and then click the 'OK' button (or the 'Next' button in older versions of Excel) to continue to the next step.
Another dialog box should appear. This dialog box will contain two or more text boxes. Click in the first of these text boxes and type the addresses of the cells Excel should use to calculate the standard deviation. In this case, you should type the following:
B2:B6
When you finish typing the addresses, click the OK button. You will be returned to the worksheet, and the number 1.095445115 (representing the standard deviation for this version of your film) should appear in cell B8.
With your pointer sitting in cell B8, take a peek at the formula bar. Notice how Excel has inserted the following function into this cell:
=STDEV(B2:B6)
The function literally says "Calculate the standard deviation for the cells from B2 through B6."
Now, let's copy this function over to cell C8. When you copy the function, Excel will change the column letters so the function will work in its new location.
Make sure that your cell pointer is still sitting in cell B8, and drag the copy handle over to cell C8. When you finish copying your function, Excel will place the value 2.828427125 in cell C8.
Although both versions of the film racked up fairly similar averages, the standard deviation for the second version of the film was more than two and a half times higher than the standard deviation for the first version of the film. This indicates that there was far more agreement over the ratings for the first version of the film than there was for the second version.
Click your mouse once on cell C8 and glance at the formula bar. Notice that this copy of the function says:
=STDEV(C2:C6)
Click the 'File' menu and choose 'Save' to save your worksheet again.
Now, let's calculate the mode (most common rating). Click in cell B9 and then click the Paste Function (or Function Wizard) button.
When the dialog box appears, click the 'Statistical' category from the list on the left. Then, scroll through the list on the right until you find the word 'Mode' and click it. Click the 'OK' or 'Next' button and another dialog box will appear. Type 'B2:B6' (without the quotes) in the first text box and then click the 'OK' button to return to your worksheet.
The number 7 should appear in cell B9. Make sure your pointer is still in cell B9 and use the copy handle to copy the function to cell C9.
Note: If you see a message that says "#N/A" occasionally in the cell where you entered the MODE function, don't worry. #N/A means 'not applicable.' The mode is designed to show you the most common response. If you were to put a different number on each line of rows 2 through 6, then no one response would be more common than any of the others. Therefore, there could conceivably be conditions under which your worksheet data would have no mode. If we had a larger sample (more than 10 responses), we would always have a mode. But with only five responses, there may be times when the mode does not apply.
Click the 'File' menu and choose 'Save' to save your worksheet again.
Next, let's calculate the max (highest rating). Click in cell B10 and then click the Paste Function (or Function Wizard) button.
When the dialog box appears, click the 'Statistical' category from the list on the left. Then, scroll through the list on the right until you find the word 'Max' and click it. Click the 'OK' or 'Next' button and another dialog box will appear. Type 'B2:B6' (without the quotes) in the first text box and then click the 'OK' button to return to your worksheet.
The number 7 should appear in cell B10. Make sure your pointer is still in cell B10 and use the copy handle to copy the function to cell C10.
Click the 'File' menu and choose 'Save' to save your worksheet again.
Part 4
By now, you should have noticed a pattern. After clicking the 'Paste Function' (or 'Function Wizard') button, you will almost always carry out the following steps:
Click the 'Statistical' category;
Click the function most appropriate to your needs and then click the 'OK' (or 'Next') button;
Identify the cells containing the data to be analyzed; and
Click the 'OK' button. Copy the function to other cells as needed.
Exercise
Go to cell B11 and insert a function that will calculate the Min (lowest rating) for the values in cells B2 through B6. Copy the function over to cell C11. Save your work when you finish.
Now, pretend that you've just received the results of another study. Type some new numbers in cells B2 through B6 and C2 through C6. Notice how Excel automatically and instantly recalculates all of the statistics on rows 7 through 11 for you!
Save the resulting file as assignment 9 and print. You are now ready for lesson 9 quiz.
btaylor@rolandstory.k12.ia.us
Ag Technology—Taylor Spreadsheet Unit # 9 Page #
