Students will use the calculated population totals to create graphs that help to visualize the totals for analyzing and representation. Census data is used as the data to provide information to analyze. Students will then use basic functions and formulas in spreadsheets to help analyze and represent the data.
06.04 – Create and analyze formulas and functions (e.g., apply conditional formula logic, name and cell ranges)
06.05 - Create and modify charts and images. (e.g., pivot tables).
06.08 - Interpret data on line graphs, pie charts, diagrams, and tables
This lesson could be used within a unit that involves learning how to use formulas and functions in spreadsheets. This lesson may be the final of a unit as a culminating activity.
Students will use the calculated population totals to create graphs that help to visualize the totals for interpretation. Census data is used as the data to provide information to interpret. Students will then use basic functions and formulas in spreadsheets to help interpret the data.
Students will be able to:
Organize data into tables with appropriate headings.
Include formulas and functions to obtain outputs that can be analyzed.
Create charts from the organized tables and formulas.
Analyze and represent data based on the results displayed in charts.
Make inferences about the two data samples.
Students should be familiar with/able to:
Use spreadsheet formulas and functions.
Create and modify charts and graphs.
Organize data in spreadsheets.
Know proportions and percent calculations.
What calculations can you perform first to use in other formulas and functions?
How can you modify your graphs to make data analysis easier?
Based on the data in the completed tables and graphs, what conclusions are you able to draw?
To begin the lesson, review with students how to use spreadsheet formulas, functions, and charts. Ask students about how to identify cell data and formulas within cells. Be sure to go over the importance of these differences. Formulas and functions, especially incorrect ones, can manipulate data.
Provide students with the Population Data (see attachments) and look over the data together. Have students write down their initial interpretations.
Provide direct instruction for manipulating the data, including how to manually enter formulas to produce the appropriate calculations. Also include how to select the appropriate functions to achieve similar ends.
The teacher can demonstrate how to perform the appropriate formulas and functions on a given demographic. The Population Data attachment can be shown on the projector using a computer. Then the Population Data can be manipulated using formulas and functions to provide the appropriate direct instruction examples.
The teacher can demonstrate the use of formulas and functions to show students different ways to reach the same ends while demonstrating the efficiency of some methods over others (such as using the SUM function versus using a formula to add values manually). Students can compare and discuss differing methods for their strengths and weaknesses.
After providing an example of how to use formulas and functions, take the time to discuss with students how to interpret topics including the importance of specific calculations, and the preferences of certain functions or formulas over other approaches.
A Population Data Key (see attachments) is provided. See Teacher Notes for more information.
Explain the directions for using the data. Students will be using functions and formulas to organize the data and create charts of the data. Students should:
Calculate the population totals from the data using functions.
Use the totals from each demographic to determine the male percentage and female percentage using formulas.
Use functions to determine the minimum and maximum numbers from each column and display them in the corresponding locations.
Create an if statement for each row in the If column that compares the population totals for that row using arithmetic inequalities. The if statement should display “Female” if true and “Male” if false.
Discuss choices for data representation, such as how to decide which charts or graphs are most effective for specific data types. Choices on how to represent specific data, such as comparisons using a single variable and those using more than one. Examples may include differences in comparisons based solely on gender or based on multiple factors, such those including gender, age, and education.
A suggested strategy for use includes two-computer pair programming. Students work in teams of two to complete the assignments. Pair programming involves a "driver" and a "navigator." The driver is the student controlling the computer. The navigator helps to guide the driver in the necessary direction to complete the task. Navigators are reminded not to "take the wheel" from the driver unless it is an "emergency." Use of two computer pair programming lets each student have her own part of the assignment to complete and the freedom to explore individual approaches without interfering with one another. In other words, student pairs regularly switch roles.
Observe student work throughout the lesson and ask questions to students about their approaches. Give suggestions when necessary. However, ensure that students are able to take ownership of their work by providing enough flexibility for students to find their own ways to organize the data.
To wrap up the assignment, give students the opportunity to write down their findings and a conclusion of what they have interpreted in the form of a summary. Ask questions like:
How did formulas and functions in the spreadsheet help you accomplish your goal?
How might certain functions and formulas be preferable over others?
How did you know which data to include in charts or graphs?
How did you determine which type of chart or graph to create?
This lesson may best be taught after previous lessons on how to use spreadsheet formulas, functions, and charts. This allows the teacher and the students to draw upon previous experiences to help keep the goal of interpreting data in view while engaging in a spiral review of previous lessons.
The teacher can provide some direct instruction to students as the assignment has many charts to work with. The teacher can demonstrate how to perform the appropriate formulas and functions on a given demographic. This includes providing an example method for filling the cells with data that may be used in those formulas and functions.
The assignment provides the opportunity to use IF, MIN, MAX, and SUM functions There are also a number of formulas that are essentially simple math problems involving cell values. This gives the teacher the opportunity to demonstrate data analysis in practice. It is a way to show there is more than one simple step. Understanding how to calculate the data results in more accurate interpretation.
There is a Population Data key provided (see attachments) for one possible way to complete the assignment. There are multiple ways to correctly calculate the data. The students can experiment with different types of graphs or calculations. Yet it is important to ensure that whatever path they choose provides an accurate result.
Use the Magnifier in computer applications to help students who are visually impaired to see the screen better.
Students that struggle with mathematics can be helped with significant guidance on how formulas in spreadsheets behave as they would in math with only slight alterations. This is a great time to review the order of operations.
If necessary, scaffold the assignment by providing students with worked examples that they can directly interact with in spreadsheets.