Perhaps, the most simple distribution is the uniform distribution. Distribution is kind of tell you the likelihood of getting a value. In the uniform distribution, any number between the minimum and the maximum is equally likely. So, this is like rolling a dice. One, two, three, four, five, and six are all equally likely. If I asked to pick a number between 1 and 100, that would also be a uniform distribution. Keep in mind those two examples that I just went through are discrete variables. No one would really pick a number of 23.72, for example. In this example, this is a continuous uniform distribution, where you can have a value between minimum and maximum, and any number between those is equally likely. The area underneath a probability distribution function is always 1. We can use the random number generators that are built into Excel and VBA to generate a number between 0 and 1, based upon a uniform distribution. And then, we can scale that up to any uniform distribution in general. We can scale it up and translocate it. To generate a random number that follows the uniform distribution, what we'll do is we first choose a random number, between 0 and 1. This first step here, is going to be done in all of our examples. We use the random number generators that follow a uniform distribution in Excel or VBA. So that's rand function in Excel, or the Rnd function in VBA, that gives us equally likely value between 0 and 1. And then, you can scale that up using this following formula here to easily generate any number that follows a uniform distribution between the minimum and max value. What we do is we take the minimum and we add that to the difference between max minus min, and then we multiply that by R. And that will give us then a value between min and max. Just as a simple example, what if I chose for my random number, I chose R equals 0.5? Then, that would be half way. We always start from the left, and we fill in the distribution that we start from the left and we plug in the probability or the random number R. So if R were 0.5, then we would start to fill up the left of the distribution. And then, wherever we are on the x-axis, that is the the random number that's generated. So here, that would be the average of min and max. So, I want to show you how to do this in Excel and VBA. In Excel, there's the rand function, so that if you read here, it says, returns a random number greater than or equal to 0 and less than 1, evenly distributed. That's the uniform distribution. If I drag this down, maybe generate a bunch of these, then these are all uniformly distributed variables. So, we can generate using the rand function in Excel, a number that follows the uniform distribution. In the example that I work through in the previous screen cast, and an example that we'll continue with, one of the uniformly distributed variables is flour which we have a minimum of 45 cents per pound and a max of 0.70 cents per pound. Let's go ahead and work through this example. I put my min, max, recall that the equation to generate any number that follows a uniformly distributed variable is the min plus the max minus min times that random number, that uniformly distributed number between 0 and 1 which is R. So, this is going to be my random number here, that follows the uniform distribution. I'm going to just type in equals, and I do the min, that gives me 45 cents plus the difference between max and min, and I take that times a random number. All right. I'm just going to plug in the rand function. So that means, just by random, the first value that I get is 45 cents. I actually going in here, and I'm going to put absolute references in here to make them, such that I can drag this down. And now, I'm generating a bunch of data points that are between 45 and 70, that are uniformly distributed. So, any value between 45 and 0.7, is going to be equally likely. So, that's how you can generate data points in Excel. Let me show you how to do this in VBA. I'm just going to do a sub test and I'm going to do a message box, just we're going to message box, and I'm going to do the minimum which was 0.45 plus the difference between max and min which is 0.7 minus 0.45 times, now in VBA, you can just do RnD. Either RnD by itself or you can also add in an empty parentheses. I'm going to leave it as that, and we'll go ahead then. And if I run this using F5, we generate a random number, between 45 cents and 70 cents there, 0.45 and 0.7. I can run this again, you know, I get a different one. But what you'll see, is that these are always between 0.45 and 0.7 because we scaled our solution between the minimum and maximum using this formula. So, that's how you can use the uniform distribution and the random number generators in Excel and VBA, to output any number that follows a uniform distribution between your minimum value and your maximum value. And each value in there is equally likely.
