Using EXCEL as a classroom tool for statistics

Author: John
Lesson Plan:

Notes to the teacher regarding statistics and Microsoft Excel                                 

 

The most important branch of applied mathematics is probably statistics.   A statistical analysis of a group, for example heights of boys in a high school is not very meaningful, at least in the context of experimental science.  In science, statistics is meaningful as an evaluation of data that should be the same.  Statistics is used to answer questions such as:

 

·                    How confident can we be of this experimental result? 

·                    Is this measurement truly different?

·                    What is the probability that this product is out of specification?

·                    how small of a value can we measure?  (detection limit)

·                    what are appropriate significant figures?

 

Statistical concepts are difficult to grasp.  This short communication recommends using Microsoft EXCEL to calculate and present data to the students.  By looking at the tables, including data, constants and results, the students will begin to wonder what all this information means.  That is what statistics is all about;  What do all these numbers mean.

 

This could be used in a classroom that is involved in an experiment where the students are generating quantitations that are supposed to be the same.  This applies especially to chemistry, where the students might be finding molecular weight, or calculating molecular ratios, or finding density.

 

 

 

 

 

The EXCEL is opened up and hopefully projected onto a screen.   The table is something like this:

 

The columns might be:

 

student team name 

experimental value one

experimental value two 

constant

calculated value

final result

 

 

As the students (teams) finish the experiment they go to the computer and enter their data into the next row.  The calculations are a click and drag function.  (students will like that!)

 

Have the students look at the class results.  The values are not the same.  How close are they?  Are there some answers that are clearly "outliers".

 

A team of students does an experiment.  How confident can they be of the value that they have generated?  That is the essence of standard deviation, but do not tell the students that! 

 

The definition of significant figures is "those values that are known with certainty plus one that may be in doubt."  Give the students this definition and see what they say. 

 

EXCEL is an amazing software package and worth your and the student's time.  It will calculate standard deviation, of course, but stick to the concepts for now