Evansville, IN

Cell - A section of a spreadsheet where labels, values, or formulas may be stored; also an intersection of a column and a row.

Cell Address - This identifies a cell. It is the intersection of a column and a row. For Example: where column C and row 3 intersect, the address would be C3.

Formula - This feature calculates math problems using formulas in the form of numbers and/or cell address(es). An equal sign (=) usually comes before a formula to let the spreadsheet know that a formula is being called for.

Range - A rectangular block of cells indicated by highlighting or reverse video. Ranges of cells are used when a formula is copied or moved from one cell to several other cells.

References - Reference identifies a cell or group of cells on a worksheet. There are two basic types of references: 1) relative references and 2) absolute references.

A relative reference is like giving someone directions that explain where to go from where that person started, "Go up three blocks and over two". A relative reference tells the spreadsheet how to find another cell, starting from the cell containing the formula. References such as B3 and F19 are relative references.

An absolute reference is like giving a street address: "8811 Nevada Drive". An absolute reference tells the spreadsheet how to find a cell based on exact location of that cell in the worksheet. By adding a dollar sign ($) before the column letter and row number an absolute reference is indicated. For example: $B$3 and $F$19 are absolute references.

Format - This feature is used to indicate the format in which you wish numbers to be printed. Formats that are common in spreadsheets are currency, percent, fixed, and scientific.

Spreadsheet - A spreadsheet is a large on-screen computerized worksheet, resembling a chart of a ledger sheet. It is made up of many rows and columns with thousands of cells. It is an electronic chart in which mathematical data can be organized and manipulated. It is a decision-making took, helping the user both define problems and experiment with different solutions.

This lesson will give your students a basic understanding of how a spreadsheet works. It will introduce the parts of a spreadsheet and how to create a worksheet.

Microsoft Works or any other spreadsheet. A blank copy of the Spreadsheet. A copy of Spreadsheet basics for each student.

Allow students to explore around the spreadsheet. Move around the spreadsheet by using the cursor key. Go over the Spreadsheet Basics worksheet.

We will begin by making a simple spreadsheet that will tell us our basic multiplication tables. Ask, "What would we need to have on our spreadsheet in order to have it calculate our multiplication tables?" Answers such as, titles, a column for our ones, twos, etc., or formulas that multiply. A good idea would be to have the students fill out the spreadsheet on paper before they go to the computer.

Step 1: Title the spreadsheet "Multiplication Chart". Place it in cell A1.

Step 2: Label the headings starting at cell A2 N, Cell B2 NX1, Cell C2 NX2 and so on until they reach NX10.

Step 3: Next place a dotted line under all the headings. You must be careful here because a dotted line is thought of as a minus sign and since that is a mathematical operation the spreadsheet thinks you are putting a formula, so place a " in front of the dotted line. It will then treat it as text. Fill Cell A3 with "---------------------. Next we will show how to select a range. By either using your mouse or your shift and cursor keys, block of the cells from A3 to K3. The cells should be highlighted. Next from the top of the worksheet, select "edit" and the "fill right". The dotted line should be under all those cells.

Step 4: Now we will type the number 1 through 10 under the N column from Cell A4 to A13.

Step 5: We now will go to Cell B4 and write our first formula. Although students may know what 1 X 1 is, the idea of the exercise is to get familiar with a spreadsheet, so we will write a formula. Ask, "What do I want to multiply in the cells from B4 to B13?" Answer - 1. "Where is the numbers that we want to multiply by one at?" In cells A4 to A13. Let’s tell the spreadsheet to take what is in Cell A4 and multiply it by 1, thus we place in Cell B4 the formula =A4*1. When you hit enter it should return the value of 1.

Step 6: Instead of rewriting the formula 9 more times we will copy it down. Highlight the Cells B4 to B13, like we did before. Then from the menu at the top select "edit" and "fill down". Presto! The ones column is complete. Explain that the formula is a reference formula. The spreadsheet thinks "move one place to the left and multiply by one". So in Cell B13 it more one to the left sees 10 and multiplies by 1.

Step 7: Now we simply repeat the process for the rest of the table. In Cell C4, we write the formula =A4*2. Notice that we always reference back to the first column. Fill down the cells and continue on.

Step 8: Center the cells by highlighting the entire worksheet. From the menu select "format", "style" and then "center".

(Note all formulas must begin with an equal sign =)

AVG (Cell A₁: Cell A_n) - Gives the average value in a range of cells.

Count (Cell A₁: Cell A_n) - Gives the number of non-empty cells in a range of cells.

If (logical_test, value_if_true, value_if_false) - Determines whether the condition is true, if it is, it will return a value, if false, it will return another value. For example (If (A1=1,1,0) would read if Cell A1 has a value of 1, then put 1, if not, then put a zero.

INT - Rounds a number off to the nearest integer. INT (5.89) = 5

Rand () - Will return an evenly distributed random number. If you wanted to randomize an integer between 0 and 2 you would use the formula - INT (RAND()*2), this would return either a 0 or a 1, but not a 2. To include the 2 simply add a +1, INT (RAND()*2+1). To enlarge the numbers to be randomized, change the number to the desired range. To randomize a number between 1 and 10, INT(RAND()*10+1). To recalculate a random generator, press the function key F9.

Sum (Cell A₁: Cell A_n) - will return the sum of the range of cells.

Introduction:

This lesson was taken from the book, The Art and Techniques of Simulation, from Dale Seymour Publications and adapted to use on the spreadsheet.

Materials:

Spreadsheet software.

Procedure:

This assignment will take us through an eight-step process in solving a problem through simulation.

Example: Mary has not studied for her history exam. She knows none of the answers on a seven-question true-false exam, and she decides to guess at all seven. Estimate the probability that Mary will guess the correct answer to four or more of the seven problems.

Example: Answering the seven questions on Mar’s exam forms the seven key components in this case. We must first simulate the answering of one question and then repeat that simulation six more times for the remaining questions.

Example: We assume that Mary’s guesses make her equally likely to answer true or false on each question. Thus, Mary has a probability of one half guessing the correct answer to any one question. We assume that her guesses are independent - that is, her answer to any one question is not affected by her answers to previous questions.

Example: Since the probability that Mary guesses the correct answer on any one question is one half, we can model her answering a single question by randomizing a number on the spreadsheet, either 0 or 1. One will stand for a correct answer and 0 an incorrect answer.

Example: We will randomize 0 or 1 seven times to simulate the seven questions.

Example: With the spreadsheet you can use a conditional statement (IF) to record the number of correct guesses.

Example: With the spreadsheet this is done very easily. We only have to copy down the set of seven random numbers the number of times we wish to repeat the simulation. The spreadsheet then uses the conditional statements to determine the number of correct answers and then using a sum function to total up the repeated simulations.

Example: We can now estimate the probability that Mary correctly answers four or more questions by looking at:

The total number of trials in the experiment

You may be interested to know how close your simulation results are to the theoretical probabilities. The mathematical formula for these probabilities, in the case where each component has only two possible outcomes, is as follows:

Let n=number of key components in each trial

k=number of favorable observed

p=probability of getting a favorable as the outcome of a key component

k (k-1) (k-2) ... 2X1

For example, the theoretical probability of seeing exactly k=4 favorable in series of n=7 random numbers 0 or 1 is:

7 (6) (5) (4) (1) ⁴ (1) ³ = 35 (1) ⁷ = 35 = 0.27

4 (3) (2) (1) 2 2 2 128

True and False

In a simulation of probability of events, the number of simulations is critical when trying to achieve the expected probability. The law of large numbers is stated as follows:

"Suppose that the probability of getting a "success" is p in a single trial of a given experiment. Suppose also that the trials are independent of each other in the sense that on each and every trial the probability of a success remains p no matter what has happened on previous trials. Then if we perform n such experiments, the number of successes divided by n tends to approach p as n approaches infinity." or "The more times we repeat a random event the more it tends to lean towards its expected outcome"

Getting students to understand the above law can be very time consuming. When performing a simulation, a sample of 10 is less likely to give us our expected percentage of successes than simulating a sample of 1000. When dealing with coin tosses, 10 trials could be done quickly but 1000 trials would take a lot more time. The spreadsheet allows us to simulate a large number of trials in a very short time. Using a spreadsheet’s random generator function, logic functions (If-then) and sum functions this simulation will bring meaning to your students about the law of large numbers.

Spreadsheet stat sheet for each student, computers (Students can work in groups to fit the number of computers that you have) or you can demonstrate the procedure on a PC-viewer or large screen television with one computer, spreadsheet software (Excel is used in this document), calculators (optional), graph paper.

In this lesson we will use the spreadsheet to simulate coin tosses. We will simulate a 10 trial, 100 trial, 500 trial and a 1000 trial experiment. The student will do each simulation 5 times and record the experimental number of heads, the expected number of heads, the experimental percentage of heads, the expected percentage of heads and the difference between the expected and experimental percentage (error). The probability of landing heads is 1/2 or 50%.

The examples on the next page show a way of simulating tossing a coin 10 times. The first chart shows how the actual spreadsheet would look, while the other shows the formulas associated with those cells. You will notice that an "if-then" situation is used to randomize a 1, which represents heads and a 0, which represents tails. This is done in order to be able to sum the number of heads and tails. The student will generate a ten trial coin toss 5 times and record the information on the Spreadsheet Stat sheet. The student is actually using the worksheet to collect data from the spreadsheet. You can have the spreadsheet repeat the procedure by manually recalculating. In order to do a 100, 500 and 1000 trial coin toss the student will have to copy down the formulas in columns A and C to the appropriate number of cells If the teacher prefers, they can have the worksheet templates done before class and have templates for the 10, 100, 500 and 1000 trial to the right of each other. The formulas for the spreadsheet may vary from program to program but should stay similar. If your spreadsheet will not go down to 1000 rows, then you may have to modify the recording sheet. After the Spreadsheet Stat sheet is completed the students should answer the questions about their data. The students should notice that the proportion of heads gets closer to 1/2 or 50% the greater the number of trials done. The error between the expected and the experimental probabilities will be analyzed by graphing the results. This activity will cause students to discover for themselves the law of large numbers.

(how the spreadsheet will look to the student)

(Spreadsheet above with formulas)

Questions:

1. What do you observe about the percentage of head outcomes as you do more simulations?

2. Make a line graph of the results for the difference between the experimental and the expected % for 10, 100, 500 and 1000 coin toss simulation. What do you observe about the differences between each line graph?

3. The law of large numbers states: "The more we repeat a random event the more it tends to lean towards its expected outcome"

Based on the definition of the Law of Large Numbers, how does this experiment support that law?

4. What implication do think the Law of Large Numbers has in the lottery?

This simulation uses the spreadsheet to random a number between 1 and 6. The spreadsheet does it twice, simulating the throw of 2 dice. The spreadsheet then sums up the two dice. With the spreadsheet the simulation can be done many times, in the case, 100 and then using a conditional statement the spreadsheet figures the sums of 2’s, 3’s, 4’s, etc., that the dice add up to be.

This exercise can be used as a experimental exercise, where the spreadsheet is already completed before hand or for a spreadsheet exercise that the students create the spreadsheet.

Two dice for each pair of students, spreadsheet program, a copy of the spreadsheet titled "Dice", a data recording sheet, and calculators.

With dice in hand, ask, "When I throw these two die, what are the possible outcomes when I add them together? Write down all possible outcomes." Answer: 2 through 12. Ask, "What would be the best number to bet on when rolling the dice? Why?" Have the students to work in pairs and record the sum of two dice. Have them do 20 trials. Discuss their results. Pass the data collection sheet, "Dice Data". Have the students go to the computers and load up your spreadsheet program and load the worksheet called "Dice". Explain what the spreadsheet does. Tell the students that the spreadsheet does 100 throws of the dice at once and then tells you the number of 2’s, 3’s, etc. The totals are recorded in Cells E2 through O2. By hitting the F9 key they can re-toss the 100 die. Record the totals 10 times on your worksheet hitting F9 after each recording.

Have students graph the Cells E1 to O1 on the x-axis and Cells E2 to O2 on the y-axis. If a printer is available, print out the graph. Have the students answer question #3.

Have students return to their seats and finish the Dice Data Worksheet.

1. Which number comes up the most? The least?

2. Do you think this would always happen? Explain.

3. What did the graph look like?

4. Check your data with your classmates. Did they come up with the same data?

5. What conclusions did you make from this experiment?

spreadsheet software, a copy of the Data Collection Worksheet - TV Ratings, calculators

Discuss with the students about Nielsen Television ratings. You may want to cut out of the paper the ratings to show to the class. Ask how they think the Nielsen company does their collection of data. Do they call everybody?

Have students open the spreadsheet called TV. Introduce the spreadsheet to the students. Explain that this spreadsheet simulates calling 100 homes and asking them what network they were watching Friday night at 7:00 p.m. The totals are done for you. Your job is to record the totals 10 times which would simulate calling 1000 households. Remember to generate another 100 random numbers, press the F9 key. After students have collected the data have students return to their desk and answer the questions on the worksheet.

Nielson Ratings

DATA COLLECTION - TV RATINGS

1. What is the percentage of those who watched CBS? None?

2. Create a bar graph of your data.

3. Would you find the same results if you called 2000 households?

4. Did your results turn out the same as other people in the classroom?

5. What is your conclusions from this data?

Many states have a lottery. But many people play the lottery without knowing the tremendous odds of playing. Others know the odds, but have no tangible way to understand the enormous of the odds. This exercise will bring to light the reality of playing lotto and dash the hopes of those who plan to win the lottery instead of working hard in school or at a vocation.

Simulation is a great way to have students discover the realness of a probability event. When those probabilities are small, coins and dice can be used in simulation. The chances to win most state lotteries are in the tens of millions. The use of coins, dice or pulling numbers out of a bag are going to be impossible in trying to simulate playing the lottery. Spreadsheets offer a solution to this dilemma. Spreadsheets are able to crunch numbers very quickly. Spreadsheets can randomly generate numbers and compare those numbers to a set of numbers that the user selects in a matter of seconds.

The spreadsheet below can be designed with the use of functions and formulas by the teacher and then be utilized in the classroom. At this time I will refer to the spreadsheet below and the formulas that need to be placed in certain cells to reproduce the lottery simulation.

You will notice that in columns A - F, the spreadsheet is randomizing the picks. The formula in cell A4 is =INT(RAND()*$F$1+1). Notice that the formula will random up to the number in cell F1. This is done so that you can customize the randomization to fit the expanse of the lottery game in your state. Now all you have to do is to copy that formula into all the cells below row 3 in columns A - F. In columns H - M in row 3 is where the student can place his " lucky numbers". Remind students that in this simulation each number must be different in each cell. Below the player’s pick in row 4 - 19 is where the computer checks the students’ numbers against the computer’s picks. This is a long formula, but once you get it in you can copy it and do some minor adjustments to each cell.

Here is the formula in cell H4: =IF(OR($H$3=A4,$H$3=B4,$H$3=C4,$H$3=D4,$H$3=E4,$H$3=F4),1,0). You ask what does it do? The formula looks at the number in Cell H3 and checks to see if it is equal to the numbers in cells A4 - F4. If the number matches, it will return a value of 1 for a match, if does not match, it will return a value of 0 for no match. Next, enter the formula in cells I4 - M4 changing the $H$3 to $I$3 and so on through cell M4. After you get one row done, just copying the formulas down to row 19.

Cell O4 simply adds up the values in cells H4 - M4 with the formula: =SUM(H4:M4). Copy this formula down to row 19. If the number 6 appears in column O anywhere, then you have a possible winner. The purpose of column P is the possibility of a repeated computer pick. In the actual state lotteries, the computer will generate six different numbers. The only way I could figure a way to get my simulation to work around this was to place this formula in cell P4 and copy it down: =IF(OR(A4=B4,A4=C4,A4=D4,A4=E4,A4=F4,B4=C4,B4=D4,B4=E4,B4=F4,C4=D4,C4=E4,C4=F4,D4=E4,D4=F4,E4=F4),"invalid","OK"). What this formula does is check to see if any numbers are repeated in cell A4 - F4. If the number is repeated then the word "invalid" appear, if not then the word "OK" appears. Thus if a student comes up with a 6 in column O followed by an "OK" they are millionaires.

I know that the above explanation is pretty lengthy, but once you get it completed and saved (I save it as Lotto), it can be used over and over. Here is how I use this in class. Discuss the lottery system in your state. Ask, "Does it pay to play lotto or do you stand a good chance of winning? What are the odds of winning? How many tickets would you have to buy in order to have a good chance in winning?"

Explain that, today, students will play a simulation of Lotto on their computer. They will be able to pick 6 numbers from 1 to 50 (or whatever number you choose). If they pick the same 6 numbers that the computer picks, they win! Explain that when one actually buys a ticket they have the option of letting the computer pick their numbers. This simulation will allow the student to play 15 times every time that they hit the F9 key. Remember that is 15 dollars. They may pick another 6 numbers anytime during the exercise. They need to keep track of the number of times that they hit the F9 key. To see if they have won, they need to look to the far right column, if a 6 appears followed by the word "OK" that means they have matched all six numbers. A "3" would mean 3 matches and so on. Also the numbers that the computer picks must all be different and occasionally they are not. In order to check that the computer pick is indeed valid it must have an "OK" beside the number, otherwise, it will state that it is "invalid". Good luck!

Have students go to the computers and open up the program called Lotto. Have them keep track of the number of times they hit the recalculation function (F9 key on my computers).

After they have done the simulation for a while, it is doubtful that anyone will win (at least I have never seen it happen and I have been doing this for 5 years now). Discuss how much money they spent. Talk about the actual odds of winning. Does it pay to play Lotto? What could you have done with the money that you spent on tickets?

Note to teachers: I have seen students try and fix the simulation. Some will simply type over the formula in cells O4 - O19 by placing a 6 in that cell. You can check the winner by making sure the formula is still intact in that cell and it is truly a match. They may also type over the random generated numbers in columns A - F, again check to see that the formula is intact before declaring a winner. I find that telling the students ahead of time that I am aware of all possible cheating angles will detour such activity.

Lottery Simulation Spreadsheet

This is another problem similar to the True and False Quiz problem. The probability is 1/2.

Spreadsheet software, data collection worksheet entitled, The Passing Game, calculator.

Pass out the worksheet and have the students record the data and answer the questions. Students should open the spreadsheet, Football.

________________________________________________________________

A quarter back on a football team completes 50% of his passes. Suppose he makes 10 passes in a game. Use a simulation model to find the following estimates.

1. Estimate the probability that he completes all passes.

2. Estimate the probability that he completes exactly five passes.

3. Estimate the probability that he completes at least five passes.

4. What number of completions, per 10 passes in a game, is most likely?

5. Calculate the average number of completions per game from your simulation.

average # of completions= number of trials

Data Collection - The Passing Game

---------------------------Completions------------------------

This simulation is one that has a probability that differs from one half.

Spreadsheet software, data collection worksheet - Traffic Lights.

This problem states that the probability that a light is green 0.3 of 30%. To simulate this on a spreadsheet we would randomize a number between one and ten. If the number is a 1, 2, or 3, then the light is green. We can use a conditional "if" statement like, "If (A3<4, 1, 0)". This statement would look at the cell to see if it is less than 4 (3, 2, 1) and place a 1 in the cell to indicate a green light. The probability that the light is not green is 0.7 or 70%. The conditional statement would return a 0 for not green the stated probability because 100% - 30% is 70%.

The spreadsheet will do 15 simulations at a time. If the number 3 appears in the far right hand corner then the light is green at all three. Record the findings and answer the questions.

________________________________________________________________

Coming to school each day, Anne rides through three traffic lights, A, B, and C. The probability that one light is green is 0.3, and the probability that it is read is 0.7. Use a simulation to answer these questions.

1. What is the probability that Anne will find all traffic lights to be green?

2. What is the probability that Anne will find one light to be green? Two lights?

Data Collection - Traffic Lights

Totals______ ________ ________ _______

In many cases, a situation under study may have more than one characteristic of interest. For example, a Democratic candidate in an election may be interested no only in the number of registered Democrats, but also in how many of the Democrats vote for him or her. Assume for the moment that all voters can be classified as Democrats and Republicans and that there are equal numbers of voters registered as Democrats and Republicans. Each voter now has two characteristics, the party he or she belong to and the candidate he or she prefers. Suppose, historically in this district, 75% of the Democrats vote along party lines, whereas 80% of the Republicans vote along party lines. We now have the following information:

Our objective here is to find the number of voters voting for each of the two candidates.

Spreadsheet software, data collection worksheet - Voting, calculators

Explain to the class the problem. Go through the eight step process with them.

Step 1 The objective in the simulation is to determine the number of votes that the Democratic candidate gets.

Step 2 The situation is a combination of two key components, the party that a voter belongs to and the candidate that he or she votes for.

Step 3 The assumptions are: (a) there is an equal number of Democrats and Republicans; (b) 75 percent of Democrats vote for the candidate of their party, and 80% of Republicans vote for the candidate of their party.

Step 4 We will model the simulation using a spreadsheet and randomizing two sets of numbers. The first random number will indicate the voter’s party. We will random a 1 or a 2. One will be a Democrat and 2 will be a Republican. The second random number will determine the voter’s choice as it pertains to the percentages given.

Step 5 Our trial consists of selecting two random numbers.

Step 6 The observation of interest is the outcome of the trial, namely:

1-1 Democrat voting for a Democrat

1-2 Democrat voting for a Republican

2-1 Republican voting for a Democrat

2-2 Republican voting for a Republican

Step 7 Use the spreadsheet. At this time have the students go to the computer and record their answers on their data collection worksheets. They will record 100 voters a number of 10 times for a pole of 1000 voters. Remember to hit F9 for a new 100 voters.

Step 8 Answer questions on the worksheet.

Voter Simulation

Date Collection Worksheet - Voting

D Voting for D R Voting for D D Voting for R R Voting for R

Totals Democrats_______ Republicans ________

1. What is the chance of a randomly selected voter being a Republican who would vote for a Democrat?

2. How much money should a Republican put into their campaign for this region?

3. How much money should a Democrat put into their campaign for this region?