A study of simulated normal probability functions using Microsoft Excel

Abstract

Simulations are used in many calculations and forecasting procedures, from market analysis to estimation of measurement uncertainty. Microsoft Excel offers an innate procedure to simulate a Gaussian distribution and a pseudorandom number generator (PRNG). Using the latter, five additional simulations models with a potential to simulate the normal probability distribution function (PDF) were explored and compared with the theoretical Gaussian PDF based on a defined average (location) and standard deviation (width). The simulated distributions appeared “displaced” from the Gaussian function. Simulations of the distributions by a large number of observations confirmed different properties of the six simulation procedures. The range of the distributions differed, i.e. the difference between the maximum and minimum simulated values. The innate procedure yielded an unsymmetrical distribution that was shifted to the right (higher values). A miniscule divergence from expected values was demonstrated. A systematic difference between the simulated distributions and the theoretical Gauss distribution may not be of any practical importance in applied metrology, whereas it may be devastating in other applications, e.g. encoding and market simulations. There were differences between the models which may influence their use. It is suggested that a root cause may be found in the PRNG.