W. Gary Martin home page > CTSE 7970 > Lab 3
CTSE 7970, Summer
2001
Lab 3 – Spreadsheets III (Probability and Statistics)
Descriptive statistics.
1. Enter the following scores from an exam going down column A: 77, 62, 46, 68, 74, 91, 85, 67, 96, 74
a. At the bottom of the column, type “=average(a1:a10)”. In the cell below that, type “=median(a1:a10)”. And below that “=stdev(a1:a10)”. What do these three numbers represent?
b. One by one, change the first three scores to 100, returning the preceding scores to their original value (so there is only one score of 100 at a time). If you wish, you may set up four separate columns for the original case and the three new cases to make this clearer.
- Compare and contrast the how the “average”, “median”, and “stdev” change for the different cases. Explain what is happening!
2. As time permits: Compute these three values without using the built-in functions. (You may find SUM, COUNT, and SQRT particularly helpful.)
Comparative statistics. Are height and weight related?
3. To begin, we will gather some class data for this analysis. Enter the data into two columns of the spreadsheet, leaving the first row for labels.
a. Create a scatter-plot. Does there appear to be a relationship?
b. Let’s fit a line to the data. Enter the following formulas:
- “=slope(b2:b12,a2:a12”), where the first range is the y-values and the second the x’s.
- “=intercept(b2:12,a2:a12)” as above.
Does this appear to be a strong relationship?
NOTE: You could define “height” and “weight” for the respective arrays of values to make this clearer!
4. Add a column of predicted values, using the estimated slope and intercept. Add the predicted values to your graph. Does this help to answer the question?
NOTE: You could define names “m” and “b” to make this clearer.5. Finally, use “=correl(b2:b12,a2:a12)” (with values as above) to compute r. How does this add to your understanding?
6. As time permits: Clean this all up so that your work is easy to understand, adding labels, spacing and comments.
Probability simulations.
7. Let’s create a series of rolls of a single die.
a. “=rand()” generates random numbers between 0 and 1. Set this up so it generates 100 numbers between 0 and 6.
b. “=round(value,places)” rounds off a value to a certain number of places. Use this to make your die rolls integers. Note that you may need to make a small adjustment to make this work correctly.
c. Rather than having two separate columns, combine these into a single column by combining the two functions into a single formula.
d. Now let’s count up the frequencies.
- In B1 to B6, enter the numbers 1 to 6. (These are actually upper bounds for the intervals of values to be counted.)
- Highlight C1 to C6.
- Without hitting RETURN or ENTER: Click in the formula bar and type “=frequency(a1:a100,b1:b6)” (or wherever you put the ranges of rolls and values to be counted.)
- Now hit CONTROL+SHIFT+RETURN. This enters the formula into the array of cells you highlighted.
Hold down the “apple” key and type “=” a few types, causing it to recompute the values. Does this work as you expected?
e. Insert a bar chart of the frequencies. (NOTE: You only need to highlight the frequencies, not the values counted.) How does this make your results easier to understand?
8. As time permits, set up the sum of 2 dice and count the frequencies. Set up other combinations of 2 dice.