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.
Similar content being viewed by others
References
Kallner A (2015) Microsoft EXCEL 2010 offers an improved random number generator allowing efficient simulation in chemical laboratory studies. Clin Chim Acta 438:210–211
Farrance I, Frenkel R (2014) Uncertainty in measurement, a review of Monte Carlo simulation using Microsoft EXCEL for the calculation of uncertainties through functional relationship, including uncertainties in empirically derived constants. Clin Biochem Rev 35:37–49
Evaluation of measurement data—supplement 1 to the “Guide to the expression of uncertainty in measurement”—propagation of distributions using a Monte Carlo method. JCGM 101:2008. Downloadable from www.bipm.org. Accessed Feb 2016
Yalta AT (2008) The accuracy of statistical distributions in Microsoft® in Excel 2007. Comput Stat Data Anal 52:4579–4586
Monti KL (1995) Folded empirical distribution function curves—mountain plots. Am Stat 49:342–346
Krouwer JS, Monti KL (1995) A simple, graphical method to evaluate laboratory assays. Eur J Clin Chem Clin Biochem 33:525–529
Kallner A (2014) Comprehensive method comparisons, getting more from the data. Accred Qual Assur 19:451–457. doi:10.1007/s00769-014-1089-9
Rukhin A, Soto J, Nechvatal J, Smid M, Barker E, Leigh S, Levenson M, Vangel M, Banks D, Heckert A, Dray J, Vo S (2001) A statistical test suite for random and pseudorandom number generators for cryptographic applications. NIST special publication 800-22 (with revisions dated 15 May 2001)
Author information
Authors and Affiliations
Corresponding author
Rights and permissions
About this article
Cite this article
Kallner, A. A study of simulated normal probability functions using Microsoft Excel. Accred Qual Assur 21, 271–276 (2016). https://doi.org/10.1007/s00769-016-1200-5
Received:
Accepted:
Published:
Issue Date:
DOI: https://doi.org/10.1007/s00769-016-1200-5