The purpose of the Ponderosa Computing Statistical Distributions Excel add-ins are to provide alternative statistical (probability) distribution functions to those provided as built-in Excel functions. These distribution functions:
- Provide explicit probability mass or density functions and lower and upper tail probability and quantile functions.
- Treat distribution function parameters as exact, rather than truncating them to integer values.
- Support full random variate domains without implicit truncation of values.
- Use peer-reviewed and publicly documented algorithms with error estimates.
The Ponderosa Computing Statistical Distributions Excel Add-ins support Microsoft Windows 7, 8, and 10. There are two Add-ins:
- PcStaDistXLL_32.xll supports 32-bit Excel 2007 and later.
- PcStaDistXLL_64.xll supports 64-bit Excel 2010 and later.
Each Add-in has a separate installer package available for download from the software downloads page.
The Distribution Functions
The Ponderosa Computing Statistical Distributions Excel Add-ins provide the following statistical distribution functions:
| Distribution | PMF/PDF | LTP | UTP | LTQ | UTQ |
|---|---|---|---|---|---|
| Beta | SD.BETA.PDF | SD.BETA.LTP | SD.BETA.UTP | SD.BETA.LTQ | SD.BETA.UTQ |
| Standard Beta | SD.SBETA.PDF | SD.SBETA.LTP | SD.SBETA.UTP | SD.SBETA.LTQ | SD.SBETA.UTQ |
| Binomial | SD.BINOM.PMF | SD.BINOM.LTP | SD.BINOM.UTP | SD.BINOM.LTQ | SD.BINOM.UTQ |
| Cauchy | SD.CAUCHY.PDF | SD.CAUCHY.LTP | SD.CAUCHY.UTP | SD.CAUCHY.LTQ | SD.CAUCHY.UTQ |
| Chi-Squared | SD.CHISQ.PDF | SD.CHISQ.LTP | SD.CHISQ.UTP | SD.CHISQ.LTQ | SD.CHISQ.UTQ |
| Exponential | SD.EXPON.PDF | SD.EXPON.LTP | SD.EXPON.UTP | SD.EXPON.LTQ | SD.EXPON.UTQ |
| Maximum Extreme Value | SD.EVMAX.PDF | SD.EVMAX.LTP | SD.EVMAX.UTP | SD.EVMAX.LTQ | SD.EVMAX.UTQ |
| Minimum Extreme Value | SD.EVMIN.PDF | SD.EVMIN.LTP | SD.EVMIN.UTP | SD.EVMIN.LTQ | SD.EVMIN.UTQ |
| Gamma | SD.GAMMA.PDF | SD.GAMMA.LTP | SD.GAMMA.UTP | SD.GAMMA.LTQ | SD.GAMMA.UTQ |
| Geometric | SD.GEOM.PMF | SD.GEOM.LTP | SD.GEOM.UTP | SD.GEOM.LTQ | SD.GEOM.UTQ |
| Hypergeometric | SD.HGEOM.PMF | SD.HGEOM.LTP | SD.HGEOM.UTP | SD.HGEOM.LTQ | SD.HGEOM.UTQ |
| Lognormal | SD.LNORM.PDF | SD.LNORM.LTP | SD.LNORM.UTP | SD.LNORM.LTQ | SD.LNORM.UTQ |
| Logistic | SD.LOGISTIC.PDF | SD.LOGISTIC.LTP | SD.LOGISTIC.UTP | SD.LOGISTIC.LTQ | SD.LOGISTIC.UTQ |
| Negative Binomial | SD.NBINOM.PMF | SD.NBINOM.LTP | SD.NBINOM.UTP | SD.NBINOM.LTQ | SD.NBINOM.UTQ |
| Normal | SD.NORM.PDF | SD.NORM.LTP | SD.NORM.UTP | SD.NORM.LTQ | SD.NORM.UTQ |
| Standard Normal | SD.SNORM.PDF | SD.SNORM.LTP | SD.SNORM.UTP | SD.SNORM.LTQ | SD.SNORM.UTQ |
| Poisson | SD.POISSON.PMF | SD.POISSON.LTP | SD.POISSON.UTP | SD.POISSON.LTQ | SD.POISSON.UTQ |
| Snedecor F | SD.F.PDF | SD.F.LTP | SD.F.UTP | SD.F.LTQ | SD.F.UTQ |
| Student’s t | SD.T.PDF | SD.T.LTP | SD.T.UTP | SD.T.LTQ | SD.T.UTQ |
| Weibull | SD.WEIBULL.PDF | SD.WEIBULL.LTP | SD.WEIBULL.UTP | SD.WEIBULL.LTQ | SD.WEIBULL.UTQ |
For each statistical distribution this add-in provides either a probability mass function (PMF) or a probability density function (PDF) worksheet function, depending on whether the distribution is discrete or continuous, respectively. For each statistical distribution this add-in also provides explicit lower tail probability (LTP), upper tail probability (UTP), lower tail quantile (LTQ), and upper tail quantile (UTQ) worksheet functions.
Distribution Parameters are Assumed Exact
Each of the statistical distributions is indexed by one or more parameters A describing the likelihood of observing a value or range of values of a random variable defined by that distribution. Since the distributions are defined by these parameters the Ponderosa Computing Statistical Distributions Excel Add-in worksheet functions assume these distribution parameters are provided exactly, and do not truncate or round distribution parameters.
If a particular distribution function is not defined for some parameter value A, the Ponderosa Computing Statistical Distributions Excel Add-in worksheet function returns the #NUM! error value. This is in contrast to some built-in Excel statistical functions [6], such as BINOM.DIST(), which truncate integer-defined distribution parameter values to integers.
Support Full Random Variate Ranges
The probability mass or density function, the lower tail probability function, and the upper tail probability function for any random variable X is defined for values x in the domain of affinely extended real numbers, [-∞, +∞]. The Ponderosa Computing Statistical Distributions Excel Add-in worksheet functions PMF, PDF, LTP, and UTP support this domain, and do not truncate an argument x to discrete values.
The lower or upper quantile function for a discrete random variable X may logically return a value outside the range of nonzero probabilities. In these cases the Ponderosa Computing Statistical Distributions Excel Add-in worksheet functions LTQ and UTQ return such values, which are valid arguments for the PMF, LTP, and UTP worksheet functions.
Numerical Precision
The Ponderosa Computing Statistical Distributions Excel add-in implements its statistical functions using the double precision format defined by the IEEE Standard 754 for Binary Floating-Point Arithmetic [7]. This includes supporting signed zeros and normalized, denomalized, and non-finite (infinite and indeterminate) numbers. Excel, however, does not fully support this format [8]. This can impact the return values for these functions.
Documented Computational Methods
Microsoft improved the accuracy of Excel’s statistical distribution functions in Excel 2010 over its previous releases in response to various academic papers over the years detailing accuracy issues in Excel [1][2][3]. In doing so, Microsoft implemented completely new algorithms [4][5] for these functions. Microsoft [5] published a preliminary paper in October 2009 describing the new algorithms used by Excel 2010 to achieve greater accuracy.
However, these new algorithms and their error estimates have not been fully and publicly disclosed, and consequently users of Excel 2010 and subsequent releases do not know the accuracy limitations of the build-in Excel statistical distribution functions.
The Ponderosa Computing Statistical Distributions Excel Add-in uses the Boost C++ Math Toolkit, part of the Boost C++ Libraries provided by Boost.org, as the computational engine of its statistical distributions functions. The Boost C++ Math Toolkit is a peer-reviewed, open source library under continuing development and testing. The Ponderosa Computing Statistical Distributions Excel Add-in includes some front-end checks to implement the expanded argument ranges for its distribution functions.
Further Details
See the Ponderosa Computing Statistical Distributions Excel Add-ins User Manual for more details on these design criteria and worksheet function implementation details. The manual is available for viewing and download on the software downloads page.
Software Release and Download
I have released version 1.4.0 of the Ponderosa Computing Statistical Distributions Excel Add-ins. The Windows installation packages are available for download on the software downloads page.
