probability

johnpdavey

Board Regular
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
john

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
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)

you can plot (And change) the number of events with the probabilities associated.
Book2
ABCDEFG
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
thanks for this Juan.
it does exactluy what i want it to do.
my students should be impressed.
john

Replies
4
Views
197
Replies
4
Views
131
Replies
4
Views
88
Replies
5
Views
159
Replies
7
Views
265

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.

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

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