The integration tool uses Monte Carlo simulation to estimate definite integrals up to four dimensions (quadruple integrals). To estimate an integral, the tool uses the so-called basic or ordinary method. To learn more about the nuts and bolts of this method, refer to the blog article on the method.
For this tutorial, we will estimate the following double integral.
This integral can be solved analytically which we'll use to show accuracy (and error) later. The integral evaluates to 173.3333. The function over the integral limits can be visualized in the following surface plot.
The function peaks sharply as x and y approach 3. The goal of integration is to find the volume under this function and a potential source of error is sampling enough points where x and y are close to 3 since the function is changing rapidly in this area.
Before using the integration tool, we need to set up a model in a worksheet. Each independent variable must be modeled as a uniformly distributed variable over its interval. Then the function is evaluated in a separate cell. The function refers to each cell that represents the independent variables.
Since the integration limits are 1 to 3 for both x and y, both variables are modeled as uniform random variables on [1,3]. They are modeled using the RVUNIFORM(1,3) functions in cells B1 and B2 respectively.
The function is evaluated in cell B4.
Using the Integration Tool
Once the spreadsheet is set up, we're ready to use the integration tool. To start the tool, click on the Special Tools button on the ribbon, and then the Integration button.
The integration form will appear.
For the function cell, either enter B4 or use the minimize button and select cell B4. For this example, we'll run 100,000 iterations. Since this is a double integral, select "Double" from the Dimension drop-down box. We are integrating from 1 to 3 for both variables, so we enter 1 for lower bound and 3 for upper bound of each variable.
There are two tabs on the integration form. We've already seen the Main tab, and now we'll look at the Settings tab.
Assuming that the Excel calculation mode is set to manual, you can change how Excel makes calculations in order to speed up operation. If all variable cells are on the same sheet as the function cell, then select to only calculate the function sheet.
If variable cells are located on another sheet, then select calculate all open workbooks.
Generally, speed mode should be used. When a simulation is run, all data generated is stored in an array to speed up operation. For a large number of iterations, you may get an out of memory error message. In this case, select safe mode. When running in safe mode, each iteration is written to a worksheet when the data is generated to avoid large arrays.
Random Number Generation
There are two random number generators. The multiplicative congruential generator (MCG) is slightly faster.
Random Number Seed
If you want to repeat the random number sequence for successive simulations, you must first select the MCG random number generator. This pane will become active and you can enter a seed value.
For our example, we'll leave everything at the default settings and run the simulation. Click the Run button to start.
Once the simulation is finished, the Integration form will re-appear with the results.
In this particular simulation, we get an estimate of 173.9397. Also note that an error is calculated, which we'll discuss later.
We can paste the results to a worksheet by clicking the Paste Results button. The Integration form will disappear, and a selector box will appear. Select the upper left cell where results will be pasted.
After the cell is selected, click OK. The Integration form will re-appear.
Deleting the Simulation Data Sheet
Each time a simulation is run, all simulation data is written to a new worksheet. You can save this data by doing nothing. If you don't want to save this data, check Delete simulation data sheet on exit. The simulation data sheet will be deleted when the Integration form is closed.
Now that we've estimated an integral, let's focus on error. Simulation Master calculates error according to the following :
 Ross, S., Introduction to Probability Models, 7th Ed., Academic Press, 2007, page 720.
As we can see, error decreases as the number of iterations increase. However, it's with the square root of n, so we run into diminishing returns as n gets large. If we run a series of simulations, each time increasing the iterations, we get something like the following data.
Note: Running a simulation with the same number of iterations will likely result in a slightly different result due to the random nature of Monte Carlo simulation. If we plot the data above, we get the following chart.
As iterations increase, error decreases in a non-linear fashion (remember it decreases by the square root of n). The estimate rapidly approaches the actual value up to 10,000 iterations, then slowly gets closer to the actual value up to a million iterations. This is a somewhat difficult integral because of the sharp spike when both x and y approach 3. Other integrals could converge more quickly or more slowly. As the iterations approach infinity, error approaches zero.