probability

[johnpdavey]

i would like to set up a spread sheet to show relative frequency and plot a graph of this tending to a limit.
my idea was to set up a list of random numbers 1 or 2, using rand between, to represent heads and tails.
i then want to extract the relative frequency of getting a 2 at say 10 events, 50 events, 100 events 200 events 500 events and plot the results on a graph, number of events along the x axis, relative frequency on the y axis, to show tendancy to 0.5.
i would like to be able to use the F9 key to show the students that as the numbers change the relative frequencies change but they still tend towards 0.5.
any suggestions as to how i can achieve this would be gratefully received.
ps i am not allowed to run any macros at school and we dont have easy access to the internet to be able to use any sites that have aplets of this activity
thanks in advance for your help
john

 

[Juan Pablo González]

To get the probability of getting 2 you could use something like

=COUNTIF(A:A,2)/COUNT(A:A)

you can redefine the range to adjust to your sample, i.e, for ten events:

=COUNTIF(A1:A10,2)/COUNT(A1:A10)

or even better, if you set your spreadsheet something like this:

you can plot (And change) the number of events with the probabilities associated.

Book2
	A	B	C	D	E	F	G
1	Heads/Tails		Events/Result	10	100	500	1000
2	1		1	50%	55%	51%	51%
3	1		2	50%	45%	49%	49%
4	2
5	1
6	2
7	1
8	1
9	2
10	2
11	2
12	1
13	1
14	1
15	1
16	1
17	1
18	2
19	2
Sheet1

 

[johnpdavey]

thanks for this Juan.
it does exactluy what i want it to do.
my students should be impressed.
john