Center Home -> Content Areas Home -> Math Home -> Project Activities -> Excel Activities ->

Collecting and Numerically Analyzing M&M’s Data

 
Activity Description Activity Guide Class Data Sheet


As students work through the activity, some direct instruction about the spreadsheet may be necessary.

Part 1: Investigating M&Ms by Hand

  • Introduce the problem by discussing what is in a bag of M&M’s.  Have students generate ideas of contents and list all colors in the bag.  From students’ experience, do any colors seem to dominate the mixture?  Are there any colors that appear the least?  What is the newest color (blue)?  Once curiosity is peaked, discuss how data collection could help us answer these questions and test any hypotheses.

  • Give each student an individual sized bag of M&M’s.  Use a transparency copy of the Class Data Sheet or set up a spreadsheet similar to it for use with a projection system.  Students will need to see everyone’s data in order to enter it into their own spreadsheet.

  • Open your bag of M&M’s.  Sort the M&M’s by color and record your data on the Class Data Sheet (either on a transparency or spreadsheet).

  • On a sheet of paper, each student should draw a horizontal line to represent a “color axis”.  Break this color axis into six equal segments and label the segments with the six colors. Above this horizontal line, use an individual bag of M&M’s to create an “object frequency graph” by stacking M&M’s above the corresponding color name (see example below).
Red
Yellow
Green
Orange
Brown
Blue

  • On individual student's object frequency graphs, which M&M’s color(s) have the greatest frequency? least frequency?

  • Compare the object frequency graphs of individual bags of M&M’s.  Describe similarities and differences between the content of students' bags.

Part 2: Entering Data in Excel

  • Open an Excel spreadsheet. Follow the directions below to format the spreadsheet in a way that is useful for organizing the M&M’s data.

    • In cell C1 type a title for the data (e.g., What’s in the Bag?).

    • Create column headings in row 2.  In cell A2 type NAME, in cells B2 through G2 type the name of each color  (RED, YELLOW, GREEN, ORANGE, BROWN, BLUE) and in cell H2 type TOTAL (for total number of M&M’s in the bag).  Change the color of the text or the cell to correspond with each color name.  You can also change the font, size of the text, or alignment similar to a word processing document.

To change the color of text:
  • Select the text you wish to change.
  • Click on the down arrow on the Font Color button (upper right hand corner on toolbar) and choose a color for the text.

 

To change the background color of a cell:
  • Select the cell or cells to that you wish to color.
  • Click on the down arrow on the Color Bucket button (upper right hand corner on tool bar) and choose the color.

  • Enter all data from the Class Data Sheet.  Each row will have a student’s name in the first column and that student’s subtotals for each color.  Do not enter any data in the TOTAL column.

  • Should each 1.69 oz. size bag of M&M’s contain exactly the same number of M&M’s?  Why?

  • Explore the totals for each student’s bag.

  • To calculate the total for each bag and enter that value into the column labeled “TOTAL” (column H) first select cell H3. The value to be entered in this cell equals the sum of the values in cells B3, C3, ... G3.  In cell H3 type in the formula =SUM(B3:G3) and then press return.  (NOTE: a calculation to be entered in a cell must start with the = sign.  B3:G3 refers to the cell range where the data of interest appears.)

  • The spreadsheet also has a feature that allows the user to copy a formula and paste it in other cells.  Use the Fill Down command to copy the formula in cell H3 into the cells below it.

To fill down a formula:
  • Select cell containing the desired formula.
  • Hold the mouse down and drag downwards until all cells are highlighted which you want to apply the formula to.
  • Release the mouse and choose the Fill option under the Edit menu.  Another menu will appear to the side.  Select Down and release the mouse. The formula will copy itself into each of the cells highlighted.
SHORTCUT: Select the cell containing the desired formula, click and hold the black square in the lower right hand corner of that cell, then drag downwards until all cells are highlighted which you want to apply the formula to.  When you release the mouse, the formula will copy itself into each of the cells highlighted
  • The total number of M&M’s in each bag should now appear in column H.

  • Did every bag have the same number of M&M’s?  Reconcile any differences with the weight printed on each bag.

Part 3: Using Basic Statistics to Analyze the Data

  • We would also like to know how many bags of M&M’s are in the data set and the class totals for each color of M&M’s.

    • After the last row of student data, skip a row and type SAMPLE SIZE in column A.

    • To find the number of bags in our sample, in column B of that row, type in the function =COUNT( )  (with the appropriate range in parentheses) and then press return.

    • In the next row, column A, type CLASS TOTALS.

    • Write a formula to calculate the sum for all data in column B (in the CLASS TOTALS row) and then fill the formula across to column H.

  • How does the color data from individual bags compare with the class totals?

  • Measures of central tendency (mean, median, and mode) also give important information about data.  Underneath the TOTALS row create three more rows for the MEAN, MEDIAN, and MODE.  Use what you know about each of these measures to gather this information for each of the colors.

  • How did you calculate the three measures of central tendency?

  • Compare and contrast the three measures of central tendency to describe which measure most accurately summarizes the data.

  • Using Excel functions, the spreadsheet can calculate each of these for you.  The function command for the mean is  =AVERAGE(    )  with the cell range reference inside the parentheses (e.g.  =AVERAGE(B3:B7)  to find the mean for the data in column B cells 3 through 7) .  Replace the word AVERAGE with MEDIAN or MODE to find those measures respectively.  Use each of these formulas and compare with your previous results.

  • It is also useful to compare the number of M&M’s by analyzing the percentage of each color to the whole bag.  Using the class total data (in row labeled CLASS TOTALS), we can calculate the percent for the total of each color compared to the total number of M&M’s in the class.  In a new row (below MODE) label the first cell PERCENT.  Then in each color column, calculate the percentage (think about what you need to calculate a percent!).

    (Hint: If you have your answer in decimal form, you can use the button to express a decimal number as a percent.)

  • Why are percents useful when comparing data?

  • Which tells a better story of your data, frequency counts or percentages?  Why?

  • Suppose Moe came in late to class and he wanted to be involved in the data collection.  His bag of M&M’s has 7 blue, 17 brown, 8 green, 3 orange, 10 red, and 13 yellow. What is the best way to add his data to the worksheet while minimizing the calculations to account for the added data?  (Hint:  Excel allows you to Insert rows.)

  • Did your calculations adjust to reflect the new data?  If so, why?  If not, why not?  (Hint:  check your cell references)

  • Discuss the advantages of using a spreadsheet to organize and numerically analyze data.

Extension:

  • Collect other data from students (e.g., height, arm length, shoe size, head circumference, birth date (month and/or date), etc.), organize in a spreadsheet, and explore similar calculations of the data.  Discuss implications for the results of the data collected.  (e.g., shoe manufacturers would be interested in a distribution of shoe sizes for production purposes.)


Back to Project Activities | Back to Math Homepage

Send questions or comments here.
Last modified on July 30, 2001.