Practical Teaching Experience Excel Candy Color[1]

PRACTICAL TEACHING EXPERIENCE: Instructional Plan Names: Rich Zielaskiewicz and Jim Ruff Grade Level: Middle Grades Data Analysis Title: Candy is Dandy! - Using Graphs to Investigate Candy Color Distribution Date: August 6, 2009 Maryland VSC Content Standards: Standard 4.0 Knowledge of Statistics 1. The student will collect, organize, analyze, and present data. 2. The student will design and/or conduct an investigation that uses

statistical methods to analyze data and communicate results. 3. The student will use the measures of central tendency and/or variability to make informed conclusions. NCTM Standards: 1. Students will formulate questions, design studies, and collect data about a characteristic shared by two populations or different characteristics within one population. 2. Students will select, create, and use appropriate graphical

representations of data, including histograms, box plots, and scatterplots. 3. Students will use observations about differences between two or more

samples to make conjectures about the populations from which the samples were taken. Maryland Teacher Technology Standards Standard V: Integrating Technology into the Curriculum and Instruction Design, implement and assess learning experiences that incorporate use of technology in the curriculum-related instructional activity to support understanding, inquiry, problem-solving, communication or collaboration. INTASC Principles: Through this lesson, we hope to address the 10 INTASC Principles Teacher preparation:

Microsoft Word, Excel, Power Point, computer for each pair of students, bags of M & Ms Technology Integration: Data analysis for this activity will be completed using Microsoft’s Excel spreadsheet software which will be located on desktop computers. Students will create a spreadsheet and use it to organize their data and find measures of central tendency. They will use the Excel software to create a variety of visual displays (charts, graphs, plots, etc.) of the data. Microsoft Word will be used to create a document in which students will analyze the distributions of colors in bags of M & Ms. Objective: By organizing data in an Excel spreadsheet, the students will be able to analyze and present the results of the statistical sample in an essay submitted in Word. Instructional Procedures: Engagement: Ask students if they like candy. Most students will answer yes. Ask them if they like M&M’s. Ask them what colors are in a bag of M&M’s. List colors on the board. Ask them if the distribution of all of these colors is the same. Ask them if it is consistent. Explain that during this lesson will answer these questions and eat M&M’s! Exploration: Step 1: Color distribution • Work in pairs to gather data from several small-sized bags of M&M's or from two larger-sized bags 1 Before opening any bag of candy, think about our recent class . discussion and make a few guesses about the colors you expect to find. Ask yourself: • Do bags of equal weight have an identical number of candies? • Are all colors represented equally, or are some more popular than others? • Does color distribution remain constant, no matter how small or large the bag?

2 Record your answers in a Word document and save it for later . reference. Step 2: Create a worksheet • Create an Office Excel spreadsheet with the data you have gathered 1 Open a worksheet in Excel. In cell A1, type the title "Candy Is Dandy!" . 2 In row 3, beginning in cell A3, type the following headings: . • • • • • •

Colors Bag 1 Bag 2 Bag 3 Bag 4 Avera ge

3 Type in the weight of the bags of M&M's in a comment. Here's how: . Click inside cell B3. On the Insert menu, choose Comment. Then, type the weight in the yellow pop-up box. Now, any time you move your cursor over that cell, the weight of the bag will appear. Cell notes pop-up box in an Excel spreadsheet

4 In column A, beginning in cell A4, list all of the colors of M&M's you . found in your bags. 5 Sort the list alphabetically. Here’s how: Highlight the cells in Column . A. On the Data menu, choose Sort. Type "Total" in the cell below your last entry. 6 Now, you can format your worksheet in a variety of ways. On the

. Format menu, choose AutoFormat and select the format of your choice. Step 3: Add formulas • Add formulas to your spreadsheet 1 Enter the number of same-color M&M's in the corresponding cells, . beginning with Bag 1. Use the AutoSum function to total your columns. Highlight cells B10:E10.On the Edit menu, choose Fill, and then choose Right. 2 Now you can automatically calculate the average number of each . color per bag. Click inside cell F4, then type the formula =average(B4:E4). Highlight cells F4:F10. On the Edit menu, choose Fill, and then choose Down. In the Average column, format cells so that a whole number (that is, no decimal places) is returned by using the Decrease Decimal button on the Formatting toolbar. 3 Interpret your data. Answer these questions: . • Are some colors more numerous? • Do all bags have the same number of candies? • Compare your observations with the findings of students in other groups. Are the same colors more numerous from one group to another? • Is color distribution consistent in bags of differing weights?

Step 4: Create graphs – Formative Assessment: The students need to reflect on what the graphs mean. Can the student describe what the graphs display? Can they describe what makes a good graph? Do the student graphs show any trends in the data? If the students can answer these questions, they can go on. If not, the teacher will stay with the students having trouble to assist them while the others go ahead. • Create two charts and add your data 1 Use the Chart Wizard in Excel to produce at least two different charts. . 2 Choose the chart types that best represent the data, such as a bar

. chart or a column chart. For example, you might choose to chart the distribution of colors in a single bag or the total number of candies for all bags in your sample. 3 Highlight cells A3:E9. On the Insert menu, choose Chart. Click the . Column chart type, and then click Next. Click the DataRange tab, and be sure Columns is selected next to Series In, and then click Next. In step 3 of the wizard (Chart Options), click the Titles tab. Add titles for the chart and its axes. Click the Gridlines tab, and experiment with gridline options. Choose a gridline that makes it easier to understand the data. Click the Legend tab, choose a location for the legend, and then click Next. 4 Use Excel to place the chart as an object on the original worksheet . you created (with your guesses about color distribution), so that worksheet entries are visible as you examine the chart. Step 5: Writing the report • Analyze your data and use Word to write up your report In your report, do the following: 1 Describe: . • The research project • The procedures you used to sort and count the candies • What the data reveals about candy colors and color distribution 2 Compare your initial hypotheses about the average number of . candies per bag (in Step 1) and the expected color distribution with actual survey results. Copy and paste spreadsheet and chart data into Word where appropriate. 3 Discuss the advantages/disadvantages of representing data in . spreadsheet tables, column charts, bar graphs, or pie charts. For example: • When is it best to use one chart type instead of another? • What is the value of using more than one bag of candy to conduct this experiment?

• What is the value of using bags with different weights?

Explanation:

Not all information is the same. To communicate different kinds of information effectively, we need to graph the information in different kinds of charts. Some data collections are better represented by bar charts, for example, while others may be easier to interpret as line charts or pie charts. It is important to select a chart type that gets the message across in the most effective way. There are no sure-fire rules that determine which chart type to select for a particular data set, but there are helpful guidelines. For example, bar charts allow for a comparison of values within a category, line charts emphasize a progression of change, and pie charts show the relationship of a part to the whole.

Extension: • After the lesson, encourage students to use Internet Explorer to visit the M&M's Web page and take a virtual tour of the factory. They can send electronic mail to the company, indicating which colors they like best, which colors they would like to see more of, and which new colors they would like to see in future bags of candies. • Encourage older students to summarize their research findings in a letter to the Consumer Affairs Department, M&M/Mars, 800 High Street, Hackettstown, NJ 07840-1503. Students can use the Microsoft Word Letter Wizard to get started. Evaluation (Assessment):

Assess the students on: • The accuracy of their data collection • The successful completion of a spreadsheet that includes formulas • Their ability to work and problem solve collaboratively • Their demonstrated understanding of the uses of different charts • The thoroughness and effectiveness of their final report