Probability Calculation Help Needed

**Kiffar** · 11-12-2012, 10:44 PM

On a sheet I'm working on, I have cell L40 doing this formula, and showing its result as a percentage: =(N27/I27-I27/N27)/2. The seed cells are calculated using a variable amount of data sets that include several random numbers (thousands of possible outcomes on the low end, going into the billions and occasionally higher), and thus L40 changes every time I press F9. I want a series of cells to display the probability that L40 will display a percentage greater than 40 or higher, 15 to 39, 5 to 14, 4 to -4, -5 to -14, -15 to -39, and -40 or lower. I don't wish for the sheet to take long whenever it generates fresh random numbers, so I'd also like a way to limit the number of performed simulations to achieve its projections. Additionally, I'd like one more cell to read, based on the current display of L40 "Major Surplus", "Considerable Surplus", "Modest Surplus", "Shortfall Danger", "Shortfall", "Major Shortfall", and "Perilous Shortfall". Solved this part here in blue, though I already had some grasp of if statements in Excel. I have no grasp of probability calculations within Excel, and little mathematical acumen, so many of the terms I read when trying to find information on my own don't mean anything to me, so any insight is appreciated.

**martindwilson** · 11-13-2012, 06:19 AM

since you are using random there is no probability of anything well maybe its more likely to be positive or negative than zero

**Kiffar** · 11-13-2012, 06:39 AM

What do you mean? From a mathematical perspective I know that the data I want to generate exists, for the same reason that you can define the probability of the combinations of dice rolls, I just don't know how to get to it. The data going into the cells that make up L40 consists only of static integers and randbetween functions. Did you mean to say Excel is incapable of what I want to do?

**martindwilson** · 11-13-2012, 07:12 AM

no i dont know,this is an excel help forum not a math forum. how would you calculate it manually? then perhaps i can show you how to do it in excel

**Kiffar** · 11-13-2012, 07:36 AM

Let's say cells A1 and A2 are =randbetween(1,10), and cells B1 and B2 are =randbetween(1,11). Cell A3 is =A1+A2, and cell B3 is =B1+B2.

Now let's say cell C1 is =(A3-B3)/((A3+B3)/2), a type of percentage difference calculation. Every time I hit F9 C1 will of course be a different number.

I would like Cell D1 to tell me how often C1>.4, D2 to tell me how often .4>C1>=.2, D3 to tell me how often .2>C1>-.2, D4 to tell me how often -.2>=C1>-.4, and D5 to tell me how often C1<=-.4

**MrShorty** · 11-13-2012, 10:52 AM

Here's how I'd approach it. The basic idea is, rather than creating one copy of the calculation and running it multiple times, I make multiple copies of the calculation. Here's how I'd arrange it:

A1 and A2 are still =randbetween(1,10)
A3 and A4 are randbetween(1,11)
A5=sum(a1:a2)
A6=sum(a3:a4)
A7=percentage formula

copy these across until you get the desired number of "samples"
To get the frequency of each range of values, use the =FREQUENCY() function (see the help file if needed). I would probably put the bins array in A11:A15 (-1,-.4,-.2,.2,.4), then put the =frequency(a7:aa7,a11:a15) in b11:b16. Be sure to note that =FREQUENCY() is an array function with one more element than the bins array.