EXCEL^™

and

BASIC STATISTICS

© 2001 Mark Wiener

www.mathmark.com

Every piece of information collected about you, every count of how many of anything, and every detail that can be listed, tabulated, or evaluated are each an example of a statistic. 5'8", 140 lbs., lives in Edison, New Jersey, male, blue, Chevrolet - these are all statistics.

But "Statistics" (usually with a capital "S") is also the study of statistics (with a small "s").

"Statistics" is frequently broken into two separate parts - Descriptive Statistics and Inferential Statistics.

Descriptive Statistics is the collection, organization, presentation, and analysis of data statistics. The Microsoft spreadsheet product known as Excel^™ is often used for this purpose.

"Microsoft Excel provides a set of data analysis tools - called the Analysis ToolPak - that you can use to save steps when you develop complex statistical or engineering analyses. You provide the data and parameters for each analysis; the tool uses the appropriate statistical or engineering macro functions and then displays the results in an output table. Some tools generate charts in addition to output tables." (Microsoft Excel help topic)

This workshop will give you a brief exposure to some of these capabilities of Excel and its chart and data analysis tools.

Basic One-Variable Statistics

Mean Commonly called the "average", the mean is calculated by summing the data and dividing by the number of data entries.

example: the mean of 14, 32, 28, and 32

14 + 32 + 28 + 32 = 106

106 ¸ 4 = 26.5 or 26½

Median After putting the data in ascending or descending numerical order, the median is the middle item (if there is an odd number of items) or the average of the two middle items (if there is an even number of items).

example: the mean of 14, 32, 28, and 32

in order: 14, 28, 32, 32

average 28 and 32 =

Mode The value (or two values) which appear most often.

example: the mode of 14, 32, 28, and 32 is 32.

These are all called "Measures of Central Tendency".

Entering Data

Click (or in some cases double-click) on the Excel icon () and you should get a new blank Book open to a new blank Sheet:

with the cell at Column A Row 1 outlined.

Practice using your arrow keys to move right, down, left, and up to return to cell A1.

Now, move to cell B1 and type in the name you want to use for the data which will be entered.

Hit the ¿Enter button and your selected cell will become cell B2.

Enter your data by typing the appropriate number and hitting the ¿Enter button between entries.

You should now have a column of data:

Use your arrow keys to move to the 1st column and the next available row. In the case of the example above, you would move to A14.

Entering Statistics commands

The easiest way to get descriptive statistics about our data set is to use the Data Analysis Add-In which can be found in the top

toolbar:

Find Tools and click on it to trigger the drop-down menu which should have Data Analysis listed. Arrow down until Data Analysis is highlighted and click on it. In the new window that opens up, highlight Descriptive Statistics and then click OK (or you could double click on Descriptive Statistics).

This will open up a new window entitled Descriptive Statistics and, because you had previously moved to the cell at the bottom of the data column, the Excel program should automatically have entered the data column address as the "Input Range".

Please notice that there is a moving broken line boxing in the data and corresponding to the data elements.

Make sure that the circle next to New Worksheet Ply is filled in and check the box next to Summary statistics.

Then click OK.

A new sheet will open up and you should see the Summary statistics:

Format, Column, Autofit Selection to widen the columns so that the full words fit:

There you see, in this example, a Mean of 3.16666667, Median of 3, Mode of 3. Down further, you can see that our data has a Range of 3, from a minimum of 2 to a Maximum of 5, and that there were 12 items (Count).

Return to the sheet where we entered our data and again in the top toolbar find Tools and click on it to trigger the drop-down menu which should have Data Analysis listed. Arrow down until Data Analysis is highlighted and click on it. In the new window that opens up, this time find and highlight Histogram and then click OK (or you could double click on Histogram).

Now you want to make sure that the Input Range is properly identified and matches with the moving fence around our data, that the circle for New Worksheet Ply is filled in, and that the box for Chart Output is checked. Then click on OK.

A new sheet will open up with the Histogram (or bar chart) for our data:

Play around with the Histogram labels. Try clicking on the vertical word "Frequency" and deleting it. What about the horizontal word "Frequency" with the box? Do you need to see this or can you delete it. Change the word Bin to the more descriptive language "Number of Children." Notice how the histogram changes depending on the space available: