127i - Simulations with Digital Tools

Lesson 127 — Running Simulations with Digital Tools

Strand: Probability | Descriptor: AC9M7P02 | Duration: 45 minutes

Room/equipment: devices with spreadsheet software (as Lessons 80, 92 and 112).

Learning Intentions

• To use a spreadsheet to simulate a chance experiment many times.
• To use large-scale simulation to estimate probabilities.

Success Criteria

I can:

1. Use RANDBETWEEN to simulate a die roll or coin toss.
2. Fill a simulation down to run hundreds of trials at once.
3. Count outcomes with COUNTIF and calculate relative frequency.
4. Compare simulated results with theoretical probability.

Warmup

(6 minutes — why simulate? whole class)

Yesterday the class tossed coins by hand — perhaps tosses across the room, in fifteen minutes.

1. How long would tosses take by hand?
2. Why might anyone want that many?
3. What could a computer do differently?
4. Would a computer’s results be real chance?

Answers: 1. At two seconds each, over hours; 2. To see the relative frequency settle very precisely, or to study rare events; 3. Generate them in seconds; 4. The results come from a pseudo-random algorithm — not physical chance, but statistically indistinguishable for our purposes.

Q4 is worth one honest sentence: spreadsheet random numbers are produced by a formula, so they are not truly random. For simulating dice and coins they behave well enough that the distinction does not matter here — but it is a real distinction, and cryptographers care about it enormously.

Activities

Activity 1 — Guided Build: Simulating a Die (16 min)

Teacher-led; students follow.

Stage 1 — one roll. In cell A2:

=RANDBETWEEN(1,6)

Press F9 (or the recalculate key) a few times and watch it change. Name the function: RANDBETWEEN(low, high) returns a whole number from low to high, each equally likely.

Stage 2 — many rolls. Fill A2 down to A601 — 600 simulated rolls in one drag.

Stage 3 — count the outcomes. In columns C and D:

	C	D
1	Result	Count
2	1	=COUNTIF($A$2:$A$601,C2)
3	2	=COUNTIF($A$2:$A$601,C3)
…	…	fill down
7	6
8	Total	=SUM(D2:D7)

The $ signs matter — the range must not slide as the formula fills down (Lesson 92’s lock). The total must read ; if it does not, the range is wrong.

Stage 4 — relative frequencies. In column E: =D2/600, filled down. Compare each with .

Press recalculate several times and watch. The counts jump around each — perhaps , perhaps . The relative frequencies stay close to every time. That stability across recalculations is the point.

Stage 5 — change the scale. Repeat with only rolls, then with . Record how far the worst relative frequency strays from in each case.

(Expect: with rolls, values from about to ; with , roughly –; with , roughly –. The scatter shrinks visibly.)

Activity 2 — Simulation Tasks (14 min)

Pairs. Each task is a simulation plus a comparison with theory.

Task 1 — the coin. Simulate coin tosses using =RANDBETWEEN(0,1), where means heads.

1. Count the heads with =COUNTIF(…) and find the relative frequency.
2. Recalculate five times, recording each result.
3. How much does the relative frequency vary?

Task 2 — the biased spinner. A spinner has sectors: red, blue. Simulate spins using =RANDBETWEEN(1,10), treating – as red.

4. Use =COUNTIF(range,"<=3") to count reds.
5. Find the relative frequency and compare with the theoretical .

Task 3 — the drawing pin. Yesterday’s pin gave an estimated . Simulate drops at that probability.

6. If your estimate was , use =RANDBETWEEN(1,100) and count values .
7. Compare the simulated relative frequency with your experimental estimate.
8. What does the simulation not tell you about the real pin?

Socratic scaffolding for Task 3 Q8:

Prompt	Purpose
What probability did the simulation use?	The one we supplied — .
Where did come from?	Yesterday’s physical drops.
So can the simulation check whether is right?	No. It can only show what would happen if were correct.
What would test the real probability?	More physical drops.
So what are simulations good for?	Exploring the consequences of an assumed probability, quickly and at scale.
Looking back	A simulation is only as good as the probability it is given — the same lesson as Lesson 80’s spreadsheets.

(Answers: 1–3. Relative frequencies typically – across recalculations; 5. typically –; 6–7. typically –; 8. as scaffolded.)

Activity 3 — Inquiry: how Many Trials Are Enough? (7 min)

Pairs, using the die simulation.

Set up one simulation and vary only the number of rolls.

1. Run rolls. Record the relative frequency of sixes. Recalculate five times.
2. Repeat with rolls, then , then .
3. For each, record the highest and lowest relative frequency you saw across five recalculations.
4. What happens to the spread of the results as trials increase?
5. How many trials would you want to estimate a probability to within ?

Socratic scaffolding:

Prompt	Purpose
At rolls, what range did you see?	Often to — enormous scatter.
At ?	Typically – — tight.
What is shrinking?	The variability of the estimate, not the probability itself.
Q5: to get within ?	Thousands — students should see the order of magnitude, not a formula.
Does more data make the probability more accurate?	The probability never changes. More data makes our estimate of it more reliable.
Looking back	Exactly Lesson 120’s point: more trials fix chance variation. They do not fix a wrong model.

Checks for Understanding

(5 minutes — exit ticket, on paper)

1. Write the formula to simulate one roll of a standard die.
2. Write the formula to simulate a coin toss.
3. Write a formula to count how many cells in A2:A501 contain a .
4. A simulation of die rolls gives sixes. Find the relative frequency and compare with theory.
5. Reasoning. A simulation of a drawing pin used taken from an experiment. Explain what the simulation can and cannot tell you.

Answers: 1. =RANDBETWEEN(1,6); 2. =RANDBETWEEN(0,1) (or (1,2)); 3. =COUNTIF(A2:A501,6); 4. against — slightly high but well within ordinary variation; 5. It shows what would happen over many drops if were the true probability. It cannot verify that is correct — only more physical drops can do that.

Common Misconceptions

Misconception	How to pre-empt it
Believing a simulation proves the probability it was given.	Task 3 Q8 and exit Q5.
Expecting simulated counts to be exactly equal.	Recalculating repeatedly shows them jumping.
Forgetting the $ locks, so counting ranges slide.	Carried from Lesson 92; the total-check catches it.
Thinking spreadsheet randomness is true randomness.	The warmup’s honest one-sentence caveat.
Believing more trials change the probability.	The inquiry — the probability is fixed; the estimate improves.
Trusting a simulation without a by-hand sanity check.	Lesson 80’s rule: compare against theory.

Enrichment — Competition-Style Problems

E1 (Kangaroo style). =RANDBETWEEN(1,20) is used times. About how many results of exactly would you expect?

Answer

.

E2 (AMC Junior style). To simulate an event with probability , a student uses =RANDBETWEEN(1,100). Which values should count as the event occurring?

Answer

to — fifteen of the hundred equally likely values.

E3 (Challenge). How would you simulate a spinner with red, blue and green sectors? Give the formula and the counting rule.

Answer

=RANDBETWEEN(1,10): count – as red, – as blue, – as green. Count with COUNTIF(range,"<=5"), COUNTIFS(range,">=6",range,"<=8") and COUNTIF(range,">=9").

E4 (Challenge). A simulation of coin tosses gives heads. Another of gives . Which is stronger evidence about the coin, and why?

Answer

The first. from trials is very close to ; from trials is further off but from far fewer trials, so it carries less weight. The larger simulation pins the value down much more tightly.

E5 (Challenge). Why can a simulation never tell you whether a real physical die is fair?

Answer

A simulation uses whatever probabilities we program into it. Telling it the die is fair makes it behave fairly; the output reflects our assumption, not the physical die. Only rolling the actual die many times can test its fairness. (The same trap as Lesson 80’s spreadsheets — the machine computes what you asked, not what is true.)

Homework

(Paper-based; no devices assumed.)

1. Write the formula to simulate: (a) a die roll (b) a coin toss (c) a spinner with sectors (d) a random number from to .
2. To simulate an event with probability (a) (b) (c) using =RANDBETWEEN(1,12) where possible — state which values count as the event.
3. Write a formula to count values equal to in the range B2:B801.
4. Write a formula to count values less than or equal to in the same range.
5. A simulation of die rolls gives these counts: , , , , , . (a) Check the total. (b) Find the relative frequency of s. (c) Comment on how close the counts are to expectation.
6. A simulation of spins of a -red-in- spinner gives reds. Find the relative frequency and compare with theory.
7. Explain how you would simulate a bag of red, blue and yellow counter.
8. Reasoning. Explain why a simulation cannot verify the probability it was given.
9. Reasoning. Explain why simulated results differ each time you recalculate, even though the probability does not change.
10. Challenge. Design a simulation to estimate how many die rolls are typically needed before a six appears. Describe the procedure and what you would record.

Answers: Q1 — (a) =RANDBETWEEN(1,6) (b) =RANDBETWEEN(0,1) (c) =RANDBETWEEN(1,8) (d) =RANDBETWEEN(1,100). Q2 — (a) – of (b) using RANDBETWEEN(1,10), values –; with –, is not achievable in whole sectors (c) – of . Q3 — =COUNTIF(B2:B801,3). Q4 — =COUNTIF(B2:B801,"<=4"). Q5 — (a) ✓ (b) (c) all six counts sit near the expected , ranging from to — ordinary variation. Q6 — against — slightly high, unremarkable. Q7 — =RANDBETWEEN(1,10): – red, – blue, yellow. Q8 — the simulation generates results from that probability, so its output reflects the assumption rather than testing it; only physical data can check the real value. Q9 — each recalculation draws a fresh set of random values, so the counts vary by chance while the underlying probability stays fixed. Q10 — simulate rolls with =RANDBETWEEN(1,6) down a column; record the position of the first ; repeat many times and take the mean of those positions. (The theoretical answer is , though Year 7 need only observe it.)