Generate random inputs - Microsoft Excel Tutorial

- [Instructor] One of the benefits of using sovereign Excel is that you can generate random inputs through your model rather than entering values directly. In this movie, I will show you how to generate and manage random values in a worksheet. My sample files are 01_03 random inputs and you can find that in the exercise files collection. This is the model that I worked with in the previous movie, it's about shipping items from two suppliers to three cities. And I want to minimize the total item miles. This model is the one that we've worked with earlier, I'm trying to minimize the number of item miles used to send items from two suppliers to three cities. There are a couple of changes to the workbook. The first is if I click on cell C4, we'll see that instead of a value, we have a formula pointing to cell C21 and then we have the same for C5, C6, and C7. So let's scroll down and take a look at the cell C21 through C24. So I'll scroll down and here is where I am generating my random demand. I've clicked cell C21, and here I have a formula that will generate a random value or the city of Portland. I use the norm.INV function to generate a random value based on the mean or average in cell D21 and the standard deviation in cell E21. Mean is the average of the values that we'll see over time for demand from Portland and standard deviation tells you how spread out the data is. In general, about 68% of all values will be within plus or minus 1 standard deviation, so in this case between 190 and 250, about 83% within two standard deviations and 99.7% within three standard deviations. So values of more than 310 will be rare, but not impossible. And we have the same type of formula in C22 for Oregon City and C23 for Beaverton, and then C24, just finds the sum of the values for Portland, Oregon City, and Beaverton. And if I go back to cell C23, you can see that this formula also uses the ROUNDUP function. So we have ROUNDUP, then the number generated by the norm.INV function, comma zero, and what the zero indicates is the number of digits to the right of the decimal point. So in this case, we're looking for an integer value that will be rounded up regardless of the decimal component. So 23.00001 would be rounded up to 24. All right, everything looks good for now. I'm going to go back up to the top of the worksheet and click sell C16 and run my solver model, but I'm going to it from the workbook and not from VBA yet. So I want to show you what happens and how we get around it. So I will go to the data tab, click solver, parameters cell like box looks good, I'll click solve. We got a solution, I'll click okay, and if you look closely, you can see that the randomly generated demand, the cell C4 through cell C6 does not match the total volume shipped to those three cities, and those totals are found in cells J11 through J13. And what happened is that the random values are recalculated every time you change your workbook. And even if you set calculation to manual, solver forces a recalculation so that the model can work. So here's how I'm going to get around it. So if you run a single model, you can see the data for demand that actually went into creating the model. So for that, I will go to the visual basic editor by pressing Alt + F11, and here I have some code and I have added a fair bit to it, so rather than make you type it in, I will just explain what I've done. I start at the top by changing the workbook calculation method to manual, and I'll do this for every workbook that is currently open and in this instance of Excel. Below that, I activate the starting cell on the model sheet worksheet, and I will use cell C4 as my base. All the references that I create below will be based on cell C4. So if I go down two rows, that would take us down to C6, if I go right in one column that would take us to cell D4. Later in the course, I will add code to activate other worksheets, and that is what explains the duplicate worksheets model sheet activate command here. We don't specifically need it here, but we will need it later, so I put it in. The next bit of code below that reads the values from cells C21 through C24 into an array that I defined called int read values. And if we go up to the declarations area, you can see that I declared itn read values 3 as integer. Now because we're computer programmers and not mathematicians, we start counting at 0. So the 3 actually indicates a four item array. So we're reading in the values from C21 through C24, which is four cells, so C21 goes into a right position 0, C22 goes into a right position 1 and so on. And below that I use the upper and lower bound of int read values in my for next loop. So it will be a looping from 0 the lower bound to 3 the upper bound. Then I write the values into the active cell and the offset from that will be the element, which is my counting variable, again going from 0 to 3 and we'll grab the value from the cell 17 rows below. So C4 goes to C21, C5 goes to C22 and so on. So that's the end of that for next loop, and below that, I have the code from earlier where I run the solver model and we use the user finish equal true so that we don't have to interact with the solver solution dialog box. So that is what I did to change the code. So now when I click inside of the sub-routine and press F5 to run it, no error, that's always a good sign, I'll press Alt + F11 to move back to the workbook, and I see that instead of having a different set of input data in cell C4 through C26, I see that the values do in fact match. So my Portland demand from C4 is matched in cell J11 from C5 to J12 and C6 to J13. So this is a benefit, I can see the exact data that went into generating the solution that we find in the range, H11 to H13.