|
About Us
Middle School
Elementary School
Preschool
Current School Families
Support Our School
Visit Our Church
|
Excel - Candy Counting
Do you know how many M&Ms are in a typical bag? How many of
each color? Is each bag always the same? These are just some of the
questions we are going to answer with this activity. We will be
following the instructions below as a class by reading each step and
then completing it before we go on to the next step.
- BEFORE YOU OPEN YOUR BAG OF M&Ms:
-
Write the following labels on the
bottom or your sheet of paper leaving room above each color to
stack the appropriate M&Ms: Red, Yellow, Orange,
Green, Brown and Blue.
-
Write a guess as to how many of each
color you will find in your bag as My Estimate below
each color name. Don't
let the example below influence you.
-
Open your bag of M&M's and sort them
by color on your paper creating a graph by stacking your M&Ms
by color on your paper as shown in the example below:
|
|
|
|
|
|
|
|
Red |
Yellow |
Orange |
Green |
Brown |
Blue |
| My Estimate: |
|
|
|
|
|
|
| My Actual Count: |
|
|
|
|
|
|
-
Count your totals by color and write your totals on
your sheet of paper. After you have written your totals on your
paper, put your M&Ms into the bag provided. The candy is yours
to keep but do not eat it in class!! Then, go write your
totals on the board next to your name.
- OPEN EXCEL and create a new workbook following the directions
below:
-
Type the title <Your
Name>'s Candy Counting in cell A1. Select cells A1,
B1, C1, D1, E1, F1, G1 & H1 and then click on Format >
Format Cells > Alignment and put a check mark next to Merge Cells.
Then, center your title.
-
Add the following column
headings to row 2 in the cells indicated:
A2: Student Name (literally type Student Name not your name!)
B2: Red
C2: Yellow
D2: Orange
E2: Green
F2: Brown
G2: Blue
H2: TOTAL
-
Change the font color and/or
background color of the cells to match each M&M color. For
example, either change the font color or background color of
cell B2 to red, cell C2 to yellow, etc.
-
Add each student and their totals to
your spreadsheet starting in row 3. Don't worry about the
Totals column yet. SAVE YOUR WORK as YOUR NAME - CANDY.
-
Now we're going to calculate totals
for each student.
-
For the first student, go to cell
H3 and enter =B3+C3+D3+E3+F3+G3 and then press the
Enter key. Did this calculate the total?
-
For the second student, go to
cell H4 and enter =SUM(B4,C4,D4,E4,F4,G4) and then
press the Enter key. Did this calculate the total?
-
For the third student, go to cell
H5 and enter =SUM(B5:G5) and then press the Enter
key. Did this calculate the total?
-
For the fourth student, select
cells B6 through H6 and press the Auto Sum Key 
which is found on the Standard Toolbar and then press the
Enter key. What displayed in your cell when you pressed
the Auto Sum Key? Did this calculate the total?
-
Now, we're going to add the
totals to the remaining students by copying the formula in
Step iv above to the remaining cells. To do this:
- Select
cell H6
- Hold
the mouse over the little square in the bottom right
corner of the cell and drag downwards until all cells
where you want the formula applied are highlighted
- Release
the mouse and the formula will copy into each
cell highlighted changing the cell addresses to match
the row it is on. Check the formula in the row of the
last student in the class and see what's there.
Did
you notice that no matter how we entered the formula to calculate
the total that we started with the equal sign (=)? What does the colon (:)
mean in the formula in Step iii (and after)?
-
SAVE YOUR WORK
-
Now we are going to add some totals
and calculations for the whole class:
-
Skip one line after the last student
and in Column A of the next line enter the label Sample
Size and press the Tab Key or right arrow key to move to
Column B of that same row. In Column B enter the formula =COUNT(B3:B<whatever
the row number of the last student is) and the press enter.
What did this do? This is so we have a total number of people in
the class who counted candy. You don't need anything in columns
C through H for this row.
-
On the next row, enter the label Class
Totals in Column A. Then press the Tab Key and go to
Column B. Now add up each column - we want the total number of
Red M&Ms for the class and the total Yellow and the total
Orange, etc. and we want the total of all M&Ms for the
class. As an example, if you had 10
students in your class, the formula in cell B15 which would
total all of the Red M&Ms for the class would be =SUM(B3:B12)
because B3 is the cell address for Red M&Ms for the first
student in the class and B12 is the cell address for Red
M&Ms for the last student in the class. Note: This is not
the exact formula you would use since you will probably have
more than 10 students! Once you have the formula for column B
you can use the method we used in step 4e-v to copy this to the
other columns just dragging to the right rather than dragging
down.
-
SAVE YOUR WORK
-
Now we are going to calculate the MEAN
(we usually refer to this as the average), MEDIAN (what's the
middle number in the series) and MODE (what's the number that
occurs the most in the series) for each of the colors.
In the row after Class Totals,
enter the label MEAN in column A and press the Tab
Key to go to column B. To calculate the Mean in Excel we use
the AVERAGE function. The AVERAGE function is formatted just
like the SUM function we have been using. For example, if
you had 10 students in your class, the formula you would
enter to calculate the MEAN would be =AVERAGE(B3:B12).
Note: This is not the exact formula you would use since you
will probably have more than 10 students and you may have
more or less blank lines! Once you have the
formula for column B
you can use the method we used in step 4e-v to copy this to the
other columns just dragging to the right rather than
dragging down.
Repeat what you did for MEAN in
the next two rows but calculating MEDIAN and MODE
instead of MEAN. The functions for MEDIAN and MODE are =MEDIAN(Cell:Cell)
and =MODE(Cell:CELL)
The last calculation we're going
to do is a percentage to show what percentage of the total
each color in the bag is. We are going to use the Class
Totals row for this calculation. First in the row below the
Mode row, enter Percents in column A then tab over to
Column B. Enter the formula to calculate the percentage
of Red M&Ms to the total number of M&Ms. Hint:
Divide (use the slash / for division) the number of Class
Totals Red M&Ms (Column B) by the Total Class M&Ms
(Column H) - e.g., if the Class Totals are on row 15, the
formula you would enter for the percentage calculation of
Red M&Ms would be: =B15/H15. This will give you a decimal answer and we want
to show this as a Percentage - to do this click on the
Percent Button  on your
toolbar. Calculate percents for all of the M&M columns
(B through G).
SAVE YOUR WORK
Now it's time to see if you have understood
what we've been doing.
-
Type your name and grade into the
boxes below. Answer each question. Feel free to scroll back and
review what we've done or open Excel and try something to help you
find the answers. You must answer all of the questions.
-
After you have answered all of the
questions click the Send Answers button to send the answers to
your teacher. You will be returned to the main Computer Lab page
after you send your answers.
Back to Computer Lab
|
