probability

johnpdavey

Board Regular
Joined
May 30, 2002
Messages
88
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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
ABCDEFG
1Heads/TailsEvents/Result101005001000
21150%55%51%51%
31250%45%49%49%
42
51
62
71
81
92
102
112
121
131
141
151
161
171
182
192
Sheet1
 

johnpdavey

Board Regular
Joined
May 30, 2002
Messages
88
thanks for this Juan.
it does exactluy what i want it to do.
my students should be impressed.
john
 

Watch MrExcel Video

Forum statistics

Threads
1,127,070
Messages
5,622,512
Members
415,904
Latest member
jchi2210

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top