Google Docs

Google Apps

Presented By: Mike AgostinelliDAY 1 - Doc's, Spreadsheets, and Presentations

Google Docs

Activity - Using M&M's to find trends and patterns


Students will be introduced to the concept of graphing and data analysis as a way to communicate information.


Students will work in pairs to record color distribution in a spreadsheet format.
Students will use formulas in an Google spreadsheet.
Students will convert the spreadsheet information into several kinds of charts.
Students will work collaboratively to analyze classroom data to find averages.
Students will utilize information obtained through hands-on research and analysis to predict distributions and write a basic research report.

Lesson procedure

Introduction - 20 min

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.

In this lesson you will learn how to use charts to communicate information effectively by conducting market research about M&M's. First, you will sort and classify the contents of several bags of M&M's by color, using bags with different weights. Next you will record the number of candies for each color and summarize your findings on a worksheet created in Google spreadsheet. Then you will convert the numbers into several kinds of charts and make predictions about color distribution in other bags of candy. After that you will work in a collaborative Google spreadsheet where teams will contribute their individual data to find classroom averages. You will analyze your data and present your process and results in a written report.

Step 1: Color distribution - 10 min
Tool: Google Docs

What to do:

Before opening any bag of candy, make a few predictions in the shared Google Doc entitled "Candy is Dandy!"  Answer these questions in the Google Doc next to your team name:

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?

You may now open your bags.  MAKE SURE you keep them separated into the Regular sized bag and the King sized bag.  And DONT EAT ANY YET :)

Step 2: Create a worksheet - 15 min

Tool: Google Spreadsheet

Finished Example Spreadsheet:

What to do:

Create a spreadsheet with the data you have gathered
Open a worksheet. Merge cells A1 to E1, then type the title "Candy Is Dandy!"
In row 2, beginning in cell A2 and continuing to B2, C2, and so on, type the following headings:

Normal Bag
King Size Bag
Our Average
Class Average

In column A, beginning in cell A3, list all of the colors of M&M's you found in your bags.

Step 3: Add formulas - 30 min

Tool: Google Docs, Google Spreadsheets

What to do:

Add formulas to your spreadsheet

Enter the number of same-color M&M's in the corresponding cells, beginning with Bag 1. Use the formula =sum(B3:B8) to total your columns in the "B" column. (Press Enter) Highlight cell B9. Grab the Autofill function on the bottom right corner of B9:

Now you can automatically calculate the average number of each color per bag. Click inside cell D3, then type the formula =average(B3:C3). Highlight cell D3. Again grab the Autofill dot at the corner of D3 and drag to D8. In the Average column, format cells so that a whole number (that is, no decimal places) is returned.

Open the Shared Google Spreadsheet: M & M Project Class Average Open at this Link

Enter your name to replace the generic "Team" place holder and enter you individual data in this spreadsheet. This will give us the class average. This is the POWER of Google Spreadsheets - the power to have multiple users working simultaneously.

Interpret your data. Answer these questions in your Google Doc (About the Class Data):

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?

Step 4: Create graph - 20 min

Software: Google Spreadsheet

What to do:

We are going to create a bar graph to compare our average with the class average. The first thing we need to do is get our spreadsheet organized to grab just the need data. The data we need are the M&M colors, your average, and the class average. Google Spreadsheets will only graph data in adjacent columns or rows. We have our individual bag counts in between colors and our averages so we need to move them to be next to each other. So, lets start in B13 and add the formula =A2  We are telling the spreadsheet to take whatever is in A2 and put in into B13. Now grab the Autofill dot in B13 and drag down to B19. You should now have all of the colors. Now in C13 add D2 Again Autofill to C19. And finally in D13 add =E2 Autofill to D19. Your spreadsheet should now look like this:

Highlight B13 through D19 and click on Insert then Chart

Now fill in your details to your graph as listed below

Step 5: Create Presentation - 30 min

Software: Google Presentation

What to do:

Analyze your data and use Google Presentation to present findings

In your presentation, do the following:


Slide 1: Title Page, Insert You Tube Video: Go to Insert, then You Tube, search for M & M commercial. Choose the commercial you want and click the button "select video"

Slide 2: What method did you use to sort and count the candies

Slide 3: What the data reveals about candy colors and color distribution

Slide 4: Compare your initial hypotheses about the average number of candies per bag (in Step 1) and the expected color distribution with actual survey results.

Slide 5: Discuss these questions below:

  • 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 calculating the weight of the bags?

Now go to Share and add me ( as a viewer.

Step 6: Present Presentation - 30 min

2 minute presentation showing off your presentation

Thank you for your hard work today!!!