Episode 11: Using formulas in Excel to find the Mean, Median and Mode

NOTE: These videos were prepared when the Census at School Project was managed by Statistics Canada. Most of the information is still relevant.

Duration: 3:23  min.

This episode will show you how to use spreadsheet formulas to find the three measures of central tendency: mean, median and mode.

To access the Rich-Text Format (RTF) version, use the document conversion features available in most word processing software, or use a file viewer capable of reading RTF.
Download Transcript:

Video Transcript

Hi, I’m Angela McCanny and I am a resource teacher for Statistics Canada.

Your students probably already know how to calculate mean, median and mode by hand, but this can be a tedious process for larger datasets. Spreadsheet programs, like Excel, have built-in formula commands that make these calculations fast and easy. This episode will show you how to use the spreadsheet formulas to find the measures of central tendency: mean, median and mode.

Since mean and median are numeric calculations, we will be working on numeric data.

So, for our first calculation, let’s find the mean, or the average, (these are the same thing) for the height data.

Go to the column containing the height data, which is column D for me. You can use any empty cell to calculate the mean. But I like to keep my work organized, so I am going to click in the cell at the bottom of the row containing the height data, and type Mean. Now, click in the cell beneath that, and this is where you will paste the formula.
To enter the mean formula, first click on the fx bar in the menu bar above the data to open the Function Wizard.
In the function category, click on All or Statistical. Then, in the list of functions, scroll down until you find the word average, which is the same as mean. Click OK.
Using the mouse, highlight the cells you wish to use. In this case, I will click in cell D2, keep the mouse button pressed down and drag down to the bottom of column D. Now release the mouse button. The cells you have selected will appear in the brackets of the formula. Click OK.
The average appears in the cell you selected earlier, and you’re done.
To find median or mode, repeat the steps. Click in the cell where you want to enter the formula, click on the fx button, scroll down the formula list, and select median or mode, whichever formula you are using. Select the range of cells that contain your data and click OK. The calculation appears in the cell.

You can find some Census at School lessons that analyse data using mean, median and mode on the website.

Go to www.censusatschool.ca, click on the Learning activities section, and go to the Grades 4 to 8 lessons.
The lesson How many people live in a Canadian household? allows students to compare how the mean, median and mode change as they examine small, then larger and larger sample sizes.
Or, try the lesson Are you a “modal” student? to have students create a profile of a typical student in their class, using the mode for various attributes from the class dataset.
Using spreadsheet formulas for larger datasets allows students to get beyond the mechanics of doing calculations, to really thinking about what the measures of central tendency tell them about the data.

I hope these simple techniques will lead to some interesting discoveries in your classroom.

This entry was posted in video-tutorials-students, video-tutorials-teachers. Bookmark the permalink.