Creating+a+Computer+Spreadsheet

Please download documents to have access to pictures.


 * //Teacher Page//** **//Explore 1//**

//ACTIVITY 3: CREATING A COMPUTER SPREADSHEET//

 * Challenge:**

How can the computer be used to create an M&M spreadsheet?

Guide the students in creating the M&M spreadsheet on the computer from their workspace spreadsheet


 * Materials:**

For each group of 2 to 3 students:

A computer with a spreadsheet program; these instructions are Word and Excel

Completed information from their //M&M Data Group Record Sheet//


 * Instructions for entering the spreadsheet:** These instructions are similar to pages 25—27 of //2007 Microsoft Office System: A Resource for Teachers// (your course text).

||
 * Open a NEW spreadsheet. As you move the cursor the cell name in the upper left hand corner changes. In this shot the cursor is in cell **A1.** Move the cursor to see the cell name for the cell to the **right** of **Z1**.

3. Enter this sample data for an experiment with two bags of M&Ms. The intent of this experiment is to answer this question: What is the probability of selecting a green M&M from a bag of M&Ms?


 * **Cell** || **Data** || **Format** ||
 * A1 || What is the probability of picking a GREEN M&M? || Bold ||
 * A3 || Experiment with two (2) bags || Bold ||
 * A5 || Bag || Bold ||
 * B5 || Green || Bold ||
 * C5 || Yellow || Bold ||
 * D5 || Brown || Bold ||
 * E5 || Red || Bold ||
 * F5 || Blue || Bold ||
 * G5 || Orange || Bold ||
 * A6 || Average || Bold ||
 * A8 || Sara || Bold ||
 * A9 || Shawn || Bold ||
 * B8 || 10 ||  ||
 * C8 || 15 ||  ||
 * D8 || 9 ||  ||
 * E8 || 5 ||  ||
 * F8 || 9 ||  ||
 * G8 || 7 ||  ||
 * B9 || 14 ||  ||
 * C9 || 9 ||  ||
 * D9 || 7 ||  ||
 * E9 || 7 ||  ||
 * F9 || 7 ||  ||
 * G9 || 13 ||  ||

The results should appear as in this screen:



4. Save your spreadsheet as **M&MSpreadsheet.xls** (this is the format for 97-2004 but if you save in the 2007 MS Excel, you may see the file saved as **M&MSpreadsheet.xlsx**. It is probability best to save as .xls so everyone regardless of versions can read your file.

5. Enter the formulas to have the computer calculate the averages for each color.

a. Select cell B6

b. Enter this formula: =round (average(B8:B9), 0)

Once you enter //the = round (average (// then you can simply select the first and last cell and the spreadsheet formats the //B8:B9//. Then finish the formula. In this formula you used two functions: round, average. The average function will find the average of B8:B9. The round function takes two parameters – the first parameter is what is to be rounded (in this case //average(B8:B9);// the second parameter is to what decimal place which in this case is to the nearest whole number. Note that the formula can be either lower case or upper case.

c. When you press return, the value will appear in the cell.



6. Fill in this formula across the row to copy the correct formulas for C6, D6, E6, F6 and G6. Click on B6 to highlight that cell. You will note a small handlebar at the lower right of the highlighted cell. Grab that handlebar (creates the plus sign) and drag across the row. When you lift the mouse, the cells will fill with values created by the appropriate formulas.



Note that the formulas have changed as you move from C6 to D6 to E6, … The formulas now **reference** the column in which they are. This action is an important copy feature for spreadsheets. When you copy a formula to another cell, the cell names will be changed to reference the particular row and column into which the formulas are copied.

**Be sure to periodically resave your spreadsheet!**

7. Pie charts are a good way to look at this type of data. Create a **Pie Chart** of the average bag of M&Ms for this two-sample experiment. Complete all of the bullets before selecting **OK** to see your pie chart.

• Select from the Insert option, the Chart • In the chart options select Pie choosing the first pie representation. Note that the pie chart appears and information that you had highlighted in the spreadsheet is now recorded in the pie chart. But also note that the color names are not the colors displayed and the title of Average is not particularly descriptive for this chart. · Restructure this pie chart to be more descriptive of the data. First fix the colors of the slices of the pie chart. o Select the spreadsheet and then select View, Toolbars, and Drawing. This action will display the drawing tools including the paint bucket at the bottom on the left. o Double click on the green slice to select that slice to be colored green. You will see handlebars around the slice. Form the paint bucket; select the appropriate color (green) for this slice. Similarly correct the colors of all the slices to match the color indicators in the legend. o Click on the title to highlight it and change the name from Average to Experiment with 2 bags of M&Ms. o Double click on the green slice of the pie chart to select that slice (explore the slice); drag the slice out from the center as a way of highlighting that data. o Show the percent values for each slice. Select the chart (handlebars will appear around the slices). From Format, select Labels and tell it to Show percent. (You may need to use the Toolbox where you select the percent.) The values will immediately be placed in each slice. ||  Using Format Or using the Toolbox view ||
 * • Highlight A5 to G6.

**Be sure to periodically resave your spreadsheet!**

8. Analyze the results of this 2-bag experiment.

o What is the probability that you will draw out a green M&M on the first try?

o How confident are you that the results of this experiment accurately display the probability?

o What might you do to improve your confidence in determining the probability of selecting a green M&M?