Monte Carlo Simulation in Google Sheets

Basic Steps (the numbers are general steps, and the sub-bullets are how they are applied to our particular example):

1. Calculate the Mean and Standard Deviation of all your measurements as outlined in a previous section.
   • In our case, these are radius and height.
2. Set up column headers for trial and for each measurement and the final calculation.
   • In our case, these are: Trial, Radius(cm), Height(cm), Volume(cc).
3. Now we are going to use NORMINV to pull random measurements from a normal distribution.
   • In our case, for the first row, enter “1” under Trial. Under Radius(cm), type “=NORMINV(RAND(), [value of mean radius], [stdev. of radius])”. Under Radius(cm), type “=NORMINV(RAND(), [value of mean height], [stdev. of height])”.
   • This will pull numbers from a normal distribution.
   •  By [value of mean radius], we mean the number.
   • It is important that we hard code (type in) the value here, rather than just click on the cell that is occupied by this value.
     • This is because ultimately we will have very many trials, and we would like to drag our formulae down for each trial. We learned previously that when we drag formulae that has a cell(D2 or B4 for example) used in its equation in Google Sheets, the cell changes rows or columns when we drag through rows or columns respectively.
4. Now code up your calculation
   • In our case, for the first row, under Volume(cc), use an equation (V=πr²h) to solve for volume using the radius and height from Trial 1.
   • For this column, we do not want to hard code in the values for radius and height.
     • This is because we do want the radius and height to change for each calculation; Trial 1 uses the first radius and the first height, Trial 2 will use a new second radius and second height.
5. Run a lot of trials.
   • In our case, we ultimately we will have 3000 trials, and thus 3000 rows. For this to be an easy process, we want to be able to highlight a row, and use the plus tool to drag it from Trial 2 all the way down through 2998 cells to Trial 3000.
   • Our Radius(cm), Height(cm), and Volume(cc) are already set up to do this.
     • Radius and height vary with every row, as RAND() takes a new random value every time it is used.
     • Volume varies with every row, as it depends on each trial’s radius and height.
     • All that we have left to set up is our trials such that they increment by one for every row we drag down to.
       • `In the second row, under Trial, type “=1+[click cell in first row under Trial]”.
         • After you hit enter, the cell should read “2”.
6. Use the plus tool to drag each measurement and your final calculation down to the second row (Trial 2).
7. Highlight the whole second row, from “2” and use the plus tool to drag  all the way down through all of your trials.
8. You should now have many results of your calculation. You can calculate the mean and standard deviation of these to arrive at your final result.