+ Reply to Thread
Results 1 to 5 of 5

Formula for normal distribution bell graph?

  1. #1
    Registered User
    Join Date
    03-25-2017
    Location
    United States
    MS-Off Ver
    3.2.8
    Posts
    1

    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.
    Last edited by smehehehehhe; 03-25-2017 at 06:34 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,546

    Re: Not sure which formulas to use to make normal distribution bell graph?

    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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Formula for normal distribution bell graph?

    Quote Originally Posted by smehehehehhe View Post
    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
    Last edited by joeu2004; 03-25-2017 at 11:26 PM. Reason: "technical" correction

  4. #4
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Formula for normal distribution bell graph?

    PS....
    Quote Originally Posted by joeu2004 View Post
    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.
    Last edited by joeu2004; 03-25-2017 at 12:16 PM. Reason: Errata

  5. #5
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Formula for normal distribution bell graph?

    PPS....
    Quote Originally Posted by joeu2004 View Post
    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.
    Last edited by joeu2004; 03-25-2017 at 11:19 PM. Reason: "better idea"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Normal distribution graph - how to?
    By Louise8905 in forum Excel General
    Replies: 2
    Last Post: 02-25-2015, 12:05 AM
  2. [SOLVED] Plot a Normal Distribution Bell Curve
    By Matt1998 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-29-2014, 10:25 AM
  3. Create Normal Distribution Bell Curve
    By cp41 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-20-2013, 04:40 PM
  4. normal distribution graph
    By pulsewidth in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-24-2013, 12:25 AM
  5. Normal distribution curve / Bell curve
    By LAG1 in forum Excel General
    Replies: 0
    Last Post: 05-24-2012, 07:20 AM
  6. How to create a bell curve / normal distribution graph
    By anstasija in forum Excel General
    Replies: 5
    Last Post: 04-17-2011, 10:21 PM
  7. Bell Curve- Normal Distribution?
    By starbwoy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-08-2008, 01:04 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1