Doing Simulated Experiment to Verify a Fit Procedure

  Suppose you want to do an experiment but you're not sure if the results will be as accurate as needed. One can do a simulation first to evaluate the results. The first thing we need is some simulated experimental date. To be more specific let's assume the data will approximate the equation of a parabolic arc with random measurement errors having a normal distribution with mean μ and standard deviation σ.

  We can generate a set of random errors and add them to the computed y values of a given parabola as follows.

  The second column of random numbers are pasted values of the first column and is needed to preserve the data in the spread sheet since the random numbers and the worksheet are recalculated every time the value of a cell is changed.

  We can use Least Squares to find the coefficients of the parabola that gives the best fit to the data. To do so we create a table containing the simulated y values along with the powers of the x values, the fit functions. Putting y on the left makes it easier to do a higher order polynomial fit later on if so desired.

  In order to use the Least Squares formula for the coefficients we evaluate a table corresponding to the normal equations which is linear and easily solved. The calculation of the table can be done in one step.

  A plot shows that the fit is a good one for the data but the coefficients are slightly off. The random errors can bias the fit if they are not uniformly distributed and consequentially the coefficients have some error associated with them. 

Copying and pasting the values of a new set of random numbers allows one to "repeat" the experiment and observe the variations in the coefficients.