Formula for normal distribution bell graph?

Hi.

I have to make a column graph as a result of an experiment I am supposed to do with a pinball machine.

Let's say that the machine has 1000 cups and 10000 balls and each ball can only go left or right.

All I know is that I am supposed to use =RANDBETWEEN(0,1), 0 meaning that the ball will go left and 1 meaning that the ball will go right.

The number of cups (1000), I'm thinking, needs to go across the spreadsheet, and the number of balls (10000) should go down. I'm also thinking that I need to use some other functions such as COUNTIF, AVERAGE, and STDEV.P, but there may be more.

Please note: I am still a beginner at using Excel so I am not very familiar with how to make graphs like this, but I need to make it for school. I'm not looking for straight-up answers, only hints. But I am sorry if I end up asking you many questions following up on your replies.

Thank you.

P.S. If you need more information on this assignment, feel free to ask.

All I know is that I am supposed to use =RANDBETWEEN(0,1), 0 meaning that the ball will go left and 1 meaning that the ball will go right.

It is not clear to me how "left or right" eventually leads to "fell into cup a". If I understand what this "Monte Carlo simulation" is supposed to do, this part seems like the central core of the overall computation. What exactly is a single "trial" in this experiment supposed to look like.

As you note, you have this computation go across the columns in a single row, then make copies of that row to simulate all 1000 balls. (If it helps, and it just might add confusion, you can see how I did this in simulating the Monty Hall problem here: https://www.excelforum.com/tips-and-...l-problem.html ).

Then, once you have your 1000 trials simulated in the spreadsheet, then you would need to compute your summary statistics. I don't yet understand exactly what you are trying to do, so I am not sure what those summary statistics will be. I, too, would suspect some kind of counting function for each cup to summarize how many of the 1000 trials end up in each cup.

From there, drawing the histogram is a simple matter of creating a column chart https://support.office.com/en-us/art...0-e9ab353c4c00

That should be a good overview, so what are the details of the calculation for each trial?

Originally Posted by smehehehehhe

I have to make a column graph as a result of an experiment I am supposed to do with a pinball machine. Let's say that the machine has 100 cups and 1,000 balls and each ball can only go left or right. [....] The number of cups (100), I'm thinking, needs to go across the spreadsheet, and the number of balls (1,000) should go down. [....] I'm not looking for straight-up answers, only hints. [....] If you need more information on this assignment, feel free to ask.

Yes, the exact text of the assignment might be helpful. Since you acknowledge that this is for school and you do not want a turnkey solution, only hints, we should have no problem offering direction. However, our misunderstanding of the assignment can lead to misdirection.

Off-hand, it sounds like you are being asked to simulate a "bean" or Galton machine. Click here [1] to see a good youtube demonstration. Also read the wiki "bean machine" page.

Technically, your instructor is incorrect about the binary outcome. Watch the video. A ball can go left or right and drop to the next row of pins; or it can be bumped or bounce left or right and hit a pin in the same row. Nevertheless, the first binary outcome is a reasonable simplification, IMHO. It should be sufficient to demonstrate the expected distribution.

As you can see, balls are dropped from the center onto a field of interleaving pins, finally falling into a row of cups. The reference to "only go left or right" describes what the ball does when it hits each pin. IMHO, it would be better to "drop" more than 1000 balls, and to have fewer than 100 "cups".

Off-hand, I don't know how to approach this from a beginner's point of view. I would use VBA to implement the simulation, not physical cells in a spreadsheet. I think it would prohibitive to arrange a spreadsheet like a "bean machine" arrangement, especially for 1000s of "balls".

But perhaps someone with more imagination can guide you in that direction. Or perhaps the actual assignment description will help us guide you.

In fact, based on your brief description ("I have to make a column graph as a result of an experiment"), perhaps you are not expected to use Excel for the experiment itself, but only to do the statistics and graph. OTOH, that would presume that you must build a "bean machine", which seems unlikely.


-----
[1] https://www.youtube.com/watch?v=PM7z_03o_kk

PS....

Originally Posted by joeu2004

In fact, based on your brief description ("I have to make a column graph as a result of an experiment"), perhaps you are not expected to use Excel for the experiment itself, but only to do the statistics and graph.

In fact, your title presupposes the outcome of a "bean machine" experiment, to wit: you want to graph a normal distribution.

So perhaps one interpretation of the assignment is as follows....

Simulate the normal distribution of "1000" (I would say 10,000) balls distributed into "100" (I would say maybe 15) cups. Admittedly, that has nothing to do with using RANDBETWEEN(0,1) to simulate balls going left or right through a field of interleaving pins.

Be that as it may, to do the simulation that I describe, and numbering the cups 1 to 15, I would say that the middle cup (mean) is #8, and the standard deviation is (15-8)/4.

So in A1:A10000 (for 10,000 balls), we would enter the formula =ROUND(NORMINV(RAND(),8,7/4),0) to generate the cup number that each ball falls into.

Errata.... Because of the nature of the normal distribution and the physical limitation of the "bean machine", the formula should be =MAX(1,MIN(15,ROUND(NORMINV(RAND(),8,7/4),0))). Do you understand why? If not, try it first without MAX and MIN.

Read the RAND and NORMINV help pages to understand why that might simulate the expected normal distribution.

For the graph, use FREQUENCY to construct a histogram table. The "bins" would be the numbers 1 through 15.

The graph of your data is "straight-forward", although a beginner might need more direction.

To overlay the expected normal distribution is more complicated. Frankly, most online methods are incorrect, IMHO. I can offer a turnkey solution. But let's cross that bridge when we come to it.

I hope this is not a complete misinterpretation and misdirection of the assignment.

PPS....

Originally Posted by joeu2004

So perhaps one interpretation of the assignment is as follows.... Simulate the normal distribution of "1000" (I would say 10,000) balls distributed into "100" (I would say maybe 15) cups. Admittedly, that has nothing to do with using RANDBETWEEN(0,1) to simulate balls going left or right through a field of interleaving pins.
[....]
=MAX(1,MIN(15,ROUND(NORMINV(RAND(),8,7/4),0)))

Please forgive the incessant responses. School assignments are so much more interesting these days, that it is difficult for those of us from the pre-computer age to contain our enthusiasm. :-)

If the assignment does indeed ask you to simulate the left-or-right behavior of a "bean machine", I would say that the instructor expects you to have more than a beginner's knowledge of Excel.

Specifically, I would replace the NORMINV formula above with something like =balldrop(), which is a reference to a VBA function that returns a cup number based on a simulation of the physical design of a "bean machine".

Alternatively, I might set up a single worksheet with 10,000 rows (number of balls) and some number of columns representing the number of pins (not cups) in the "bean machine", which is populated with =RANDBETWEEN(0,1) formulas. A final column would have a formula that interprets the values in the "pin" columns in a manner that emulates the binary decision tree that the "pins" represent and results in a cup number.

Off-hand, that last formula seems very messy to me, for any reasonable number of rows of interleaving "pins". But perhaps there is a clever implementation.

I have a better idea: the formula for each "pin" can take into account the outcome of the one or two pins "above" it (in some previous column; you need to "link" the pin logic correctly). Thus, there would be only one 1 in the final row, and its position determines the "cup" number. You need to be sure that there are enough "row" of pins so that all "cup" are possible. Hint: it is easy to calculate based on the number of "cups".

FYI, for performance reasons, I would take precautions to ensure that the worksheet of RANDBETWEEN formulas is calculated manually. It is easy to do. But it might seem beyond the reach of a beginner.