CSCI A113
Lab Notes Six

First semester 2000-2001

Statistical Analysis with Excel
Microsoft Excel 2000 provides a wide range of features that can help you analyze statistical data. Built into the program are a number of functions, that assist in simple analysis tasks.

Let's use some of them in today's lab/lecture.

Problem One: Use the data on page 32 in your textbook (the thin green book) to investigate whether the sum of the deviations about the mean is zero or not.

  1. Enter these numbers in Excel: 
    8, 10, 13, 9, 7, 15, 10, 12, 10, 5, 11

  2. Then calculate the average, and the deviations from it for each of the values.

  3. Create a chart that plots the values, and the deviations.

  4. Calculate the sum of all deviations.

  5. Compute the mean in two ways:

The result should be similar to what you can find on the first worksheet (One) of the spreadsheet that can be downloaded using the link at the bottom of this set of notes.

Problem Two: Enter 30 random values (between 1 and 6) into your spreadsheet thus simulating a die being thrown 30 times. You will be simulating what is described in your textbook on page 38-39 (the thin green book).

  1. Calculate frequencies using COUNTIF.

  2. Compute the MODE, MEDIAN, and AVERAGE.

  3. Calculate the squared deviations from the mean.

  4. Sum them up, to produce the sum of squares.

  5. Divide by the total number (COUNT) of measurements to obtain the variance.

  6. Take the square root of the variance (SQRT) to obtain the standard deviation.

  7. Calculate VAR and STDEV and compare with the values obtained by us.

  8. Compute the range using MIN and MAX.

  9. Draw the histogram to show the distribution.

The result should be similar to what you can find on the second worksheet (Two) of the spreadsheet that can be downloaded using the link at the bottom of this set of notes.

Problem Three: Look over the problem described in section 3.5 in your textbook. Think how you could implement it using what you know about Excel. Can you obtain all the charts and all the calculated values?

Problem Four: For the following data set, construct a scatterplot, calculate the lines representing x and y means, and calculate the deviations from these means. Without doing any further calculations, do x and y appear to be related? Why or why not? 

x:  2 -3  7  8 -2 -5 -7  4 -6  2
y:  0  4 -5 -7  8  9  9 -3  5 -1

  1. Construct scatterplot.

  2. Calculate means.

  3. Calculate deviations.

  4. Plot deviations and explain.

  5. Plot x and y and explain.

  6. Calculate sum of products of deviations and divide by number of measurements.

  7. Divide by the product of the two standard deviations.

  8. Calculate CORREL and compare.

The result should be similar to what you can find on the third worksheet (Three) of the spreadsheet that can be downloaded using the link at the bottom of this set of notes.

Here's the spreadsheet that you can use to check your answers.

Last updated: November 8, 2000 by Adrian German for A113