In this video, learn how to generate random values when all of the events are equally likely, such as in a random drawing.
- [Instructor] Many business cases analyze events that aren't equally likely. Some products are more popular than others, and your website gets more visitors during certain parts of the day. In this movie, I will show you how to generate random values when all of the events are equally likely, such as in a random drawing. My sample file is the Uniform workbook, and you can find it in the chapter four folder of your exercise files collection. In a uniform probability distribution, every value is equally likely to occur. If you play games with dice, then you would hope that the values one through six on a six-sided die will come up about the same number of times with equal probability.
In Excel, you can generate whole numbers with equal probability by defining your range in the RANDBETWEEN function. Let's start by entering our minimum and maximum values. In cell B3, I'll type the number four, then enter, and in B4, I'll type 18, and enter. So, any value from four to 18, inclusive, is possible. To calculate the number of values, in cell B5 I'll type equal, and then I'll start with the larger value in B4, and subtract the smaller value in B3, but because I want to include that smaller value, I need to add one.
So plus one, and enter, and I see that there are 15 values. If I subtract three from each of the values, it goes from one to 15, and you can see that I do in fact have 15 values. So let's practice by generating a couple of random values using RANDBETWEEN. In cell B7, I'll type equal, and then RANDBETWEEN and the bottom value's in B3, comma, top value in B4, and enter, and I get the number nine.
If I do the same thing in cell B8, type an equal sign, RANDBETWEEN, B3, comma, B4, and enter, and I get two separate values, 12 and 11. Once you generate a random number, if you want to keep them you can replace the formula with the cell's contents. There's a keyboard shortcut for that, and I try to teach this whenever I can 'cause it is extremely useful. I select cells B7 and B8, which contain my formulas, then I'll press Control + C to copy the cell's values, and then I will use a keyboard sequence to paste in the values.
I'll press the Alt key by itself, and you can see that I can now work on the ribbon using the keyboard. The Home tab is represented by the letter H, so I'll type H, and then I want to paste, so I'll press the letter V, and then finally, I want to paste value, so I'll press V again. And if you look at the formula bar, you can see that the value in cell B7, which is the active cell, has been replaced by 12, and if I click cell B8, you see that it has been replaced by 11.
And I'll press Escape to get rid of the selection. If I want to calculate the probability of a specific value occurring in a uniform distribution, I just need to divide by the number of possible values. I calculated earlier that there are 15 possible values from four to 18, inclusive, so in cell E3, I'll type an equal sign, and that's one, divided by the value in B5, enter, and it's about six and two-thirds percent. If I want to calculate the probability of one value or another occurring in a specific trial, then I need to add the probabilities together.
So in cell E5, I'll type an equal sign, and I will type one divided by 15, plus one divided by 15, and enter, and I get .133 repeating. I could've also multiplied the number of items by the probability of an individual value. So if I were to, in this case, multiply the probability of a single value by two, 'cause I'm looking at two values. So in cell E5 again, I'll type two times E3, and enter, and I get the same value.
If you want to calculate the probability of one value occurring in the first trial and another value occurring in the second trial, perhaps it could be two values repeating, or two specific values that you want, then you multiply their probabilities together. So in cell E7 I'll type equal, and then the probability of one value occurring is in cell E3, so that's E3 times E3 again, or I could've also squared it, enter, and the probability of value one occurring in the first trial and value two occurring in the second trial is about four-tenths of a percent.
Finally, if you want to generate real number values between zero and one, then you can use the rand function. In cell B10, I'll type an equal sign, and then the function rand, followed by an open and closed parentheses. We don't provide any inputs, then press enter, and I get a value of 0.8026. If I go to the Formulas tab and click the Calculate Now button, I get a different value, and again, and every time I click I will get a different value.
That value actually goes out to 16 decimal places, so it's a lot longer than what you see here. You can't really calculate the probability of a specific real value occurring, because real numbers don't terminate. In other words, you can have .32323 repeating, or you could have .32333, and because decimals can be expanded infinitely there's no way to calculate the probability of any individual value, because that probability is essentially zero.
- Distinguish between the mean, median, and mode.
- Describe the relationship between variance and standard deviation.
- Identify a nondirectional hypothesis.
- Point out the difference between COVARIANCE.P and COVARIANCE.S.
- Explain correlation.
- Analyze Bayes’ rule.