This lesson introduces students to basic descriptive statistics used for initial data analysis. The lesson teaches students how to run introductory calculations in Microsoft Excel in order to begin to see what stories might exist in the data. Students then apply those lessons to determine how to run the same calculations using Google Sheets.
- Students will be able to define basic descriptive statistics, including mean, mode, median, range and correlation.
- Students will be able to calculate these basic descriptive statistics using Excel formulas and then run the same calculations in Google Sheets.
- Students will create their own guides for using commands in Google Sheets.
Common Core State Standards
|CCSS.ELA-LITERACY.RST.9-10.7||Translate quantitative or technical information expressed in words in a text into visual form (e.g., a table or chart) and translate information expressed visually or mathematically (e.g., in an equation) into words.|
|CCSS.ELA-LITERACY.RST.11-12.9||Synthesize information from a range of sources (e.g., texts, experiments, simulations) into a coherent understanding of a process, phenomenon or concept, resolving conflicting information when possible.
|CCSS.MATH.CONTENT.HSS.ID.A.2||Use statistics appropriate to the shape of the data distribution to compare center (median, mean) and spread (interquartile range, standard deviation) of two or more different data sets.|
Google Forms & Sheets access
(Student surveys from Google Forms completed in the previous lesson)
1:1 computer access
Note to teachers: This lesson uses spreadsheets developed in both Microsoft Excel and Google Sheets in order to familiarize students with both programs. This lesson follows the “Introduction to Spreadsheets” lesson, which provides much of the material students will work with here. Teachers who are not comfortable in Excel should spend some time using Excel tutorials online to gain a basic level of comfort.
1. Guided instruction — 45-75 minutes
Students should be at computers with Excel open and should also be able to see a whiteboard or projector screen. Use the accompanying slideshow to walk students through the main types of descriptive statistics. The presentation notes for each slide indicate when you should pause, troubleshoot or review answers.
2. Application — 45-60 minutes
Once students have navigated the descriptive statistics command in Excel (this is a good place to take a quick break from the intense computer and calculation work), instruct them to open their Google survey results from the previous lesson.
Instruct students to set up rows at the bottom of the spreadsheet similar to what exists in the practice Excel sheet they just used. They should make a row for:
They should already have two extra sheets in this spreadsheet — one labeled “diary,” and one labeled “dictionary.” They should create one more additional sheet and label it “calculations.” Instruct students to use Google and the “Help” function to find out how to run these calculations in Google Sheets. What are the appropriate commands? Does the click-and-drag feature work the same way? Some calculations will be the same as those used in Excel, and some will be different (Note: Only the range command calculation is different. The rest are almost exactly the same).
As they find the proper commands, they should make the calculations for as many columns as possible and write a brief tutorial or “how to” for calculating that number on the sheet labeled “calculations.” The format can vary, but the directions students provide should be clear enough that someone with no Google Sheets experience could follow them and be successful.
At the end of this portion of the lesson, students should have done the following:
- Calculated the descriptive statistics labeled above.
- Written a brief “how to” for using the proper Google Sheet commands for each these calculations.
A note on the correlation calculations: Students should identify which columns they would like to correlate in a way that makes sense. Aim to run at least two correlations. Remember, they can only correlate columns with numerical values. So, using the survey example from the previous lesson, a student could correlate hours spent on homework with hours spent sleeping. The teacher may need to give suggestions on which columns to correlate.
Students should submit their final Google Sheets to the teacher when they have completed both the calculations and the “how to” portion. Teachers should check for the correct “how to” for each function in Google Sheets using the guide below (instead of checking each individual calculation — depending on class size, this would be an enormous undertaking).
Google Sheets commands
Mean =AVERAGE(A1:A16) or =AVERAGE(A1, A2, A3…)
Mode =MODE(A1:A16) or =MODE(A1, A2,…)
Median =MEDIAN(A1:A16) or =MEDIAN(A1, A2,…)
Range =MINUS(value 1, value 2) where value 1 is the largest number in the column and value 2 is the smallest number in the column.
Correlation =CORREL(A1:A16, B1:B16)
This is a difficult lesson to differentiate because it does require students to have math abilities on par with statistical calculations. Students who are below level may be overwhelmed by the first portion, especially going back and forth between instructions and Excel practice. In that case, ask these students to take notes during the instruction and then while students are practicing, to summarize the instruction in their own words. This will provide time for reinforcement and allow them to internalize the material before they have to practice it. Advanced students can use a hard copy of this slideshow to go at their own pace.