No full-text available
To read the full-text of this research,
you can request a copy directly from the authors.
Uncertainty Arithmetic on Excel Spreadsheets: Add-In for Intervals, Probability Distributions, and Probability Boxes
Abstract
Despite their limitations as a platform for calculations, Microsoft Excel spreadsheets enjoy widespread use throughout much of engineering and science, and they have emerged as a lingua franca for computations in some quarters. Given their ubiquity, it would be useful if Excel spreadsheets could express uncertainty in inputs and propagate uncertainty through calculations. We describe an add-in for Microsoft Excel that supports arithmetic on uncertain numbers, which include intervals, probability distributions, and p-boxes (i.e., bounds on probability distributions). The software enables native calculations in Excel with these objects and ordinary scalar (real) numbers. The add-in supports basic arithmetic operations (+, –, x, ÷, ˆ, min, max), standard mathematical functions (exp, sqrt, atan, etc.), and Excel-style cell referencing for both function arguments and uncertain number results. Graphical depictions of uncertain numbers are created automatically. Using function overloading, the standard Excel syntax is extended for uncertain numbers so that the software conducts uncertainty analyses almost automatically and does not require users to learn entirely new conventions or special-purpose techniques.
... When operations on the interval discretizations are handled with interval analysis, the method constitutes verified computation for probability distributions. The methods of probability bounds analysis are available in several software implementations, including multiple free demonstration programs (e.g., Berleant and Zhang 2004), a full-featured stand-alone commercial program (Ferson 2002), an advanced add-in for Microsoft Excel developed for NASA (Ferson et al. 2011), and a package in development for the statistical computing language R (R Development Core Team 2010). ...
Because machine calculations are prone to errors that can sometimes accumulate disastrously, computer scientists use special strategies called verified computation to ensure output is reliable. Such strategies are needed for computing with probability distributions. In probabilistic calculations, analysts have routinely assumed (i) probabilities and probability distributions are precisely specified, (ii) most or all variables are independent or otherwise have well-known dependence, and (iii) model structure is known perfectly. These assumptions are usually made for mathematical convenience, rather than with empirical justification, even in sophisticated applications. Probability bounds analysis computes bounds guaranteed to enclose probabilities and probability distributions even when these assumptions are relaxed or removed. In many cases, results are best-possible bounds, i.e., tightening them requires additional empirical information. This paper presents an overview of probability bounds analysis as a computationally practical implementation of the theory of imprecise probabilities that represents verified computation of probabilities and distributions.
We describe a software tool for performing automatically verified arithmetic operations on independent operands when the operands are intervals, or probability distribution functions, or one operand is an interval and the other is a distribution. Intervals and distributions are expressed using the same technique, so the algorithms do not need to distinguish between intervals and distributions in their operation. The tool can calculate common arithmetic operations with guaranteed results (as well as confidence limits on a distribution if the distribution is empirically estimated from samples).
A previous paper [1] discusses the concepts, algorithms, and related work. Here we emphasize a software tool that implements the algorithms, interacts with the user via a graphical user interface, and saves, retrieves, and prints the results of its calculations.