## ALGLIB Statistics Functions

I have recently imported all the ALGLIB statistics functions into VBA, and written interface functions where required, so that all the functions may now be used as User Defined Functions (UDFs) on the spreadsheet.  A spreadsheet including full open source code and descriptions and examples of each function may be downloaded from AL-Stats.zip.  The available functions are shown on the screenshots below:

ALGLIB Statistics Functions - 1 (click for full size view)

ALGLIB Statistics Functions - 2

ALGLIB Statistics Functions - 3

The screen shots show output from the ALGLIB functions together with the equivalent Excel results, where available.  Note that many of the Excel functions are only available in Excel 2010, and will not work in earlier versions.  Also note that in some cases Excel returns statistics for an estimate of the population value, whereas the ALGLIB functions always return the statistic for the sample.  See for example this Wikipedia article on the Kurtosis function.

The ALGLIB functions with names not preceded by AL_  call the ALGLIB code directly.  For the remainder it has been necessary to write an interface function for various reasons:

• The ALGLIB routine is written as a sub, rather than a function
• The ALGLIB routines often require a 1D, base 0, array as input, or have several values as output, which must be assigned to an array so they can be returned by a UDF entered as an array function
• A few of the ALGLIB routines written as functions, which could otherwise be called directly, have the same name as the equivalent Excel function

Code for a typical interface function is shown below:

```
Public Function AL_DistMoments(XA As Variant) As Variant
Dim XA0() As Double, N As Long, Ncols As Long, Rtn As Long, DMRes(1 To 4) As Double

Dim Mean As Double, Variance As Double, Skewness As Double, Kurtosis As Double
Rtn = VarAtoDouble1D_0(XA, XA0, N, Ncols)
Call CalculateMoments(XA0, N, Mean, Variance, Skewness, Kurtosis)
DMRes(1) = Mean

DMRes(2) = Variance

DMRes(3) = Skewness

DMRes(4) = Kurtosis
AL_DistMoments = DMRes
End Function
```

The VarAtoDouble1D_0 routine converts a VBA Variant array into a 1D, base 0, Double array and returns the number of rows and columns of the array:

```Function VarAtoDouble1D_0(XL_A As Variant, ByRef Cpp_A() As Double, ByRef Nrows As Long, ByRef Ncols As Long) As Long
Dim i As Long, j As Long, LB As Long
On Error GoTo iErr
If TypeName(XL_A) = "Range" Then XL_A = XL_A.Value2

LB = LBound(XL_A)

Nrows = UBound(XL_A)

Ncols = UBound(XL_A, 2)
' Copy 2D base 1 variant array to 1D base 0 double array
ReDim Cpp_A(0 To Nrows * Ncols - 1)

For i = 1 To Nrows

For j = 1 To Ncols
Cpp_A((i - 1) * Ncols + j - 1) = XL_A(i, j)

Next j
Next i
VarAtoDouble1D_0 = 0
Exit Function
iErr:
VarAtoDouble1D_0 = 1
End Function
```

The download spreadsheet includes all the necessary code, but for the benefit of those wanting to use the code direct from the ALGLIB site, or to use the versions in different languages, all the necessary code modules are listed below:

• binomialdistr: Binomial distribution
• chisquaredistr: Chi-Square distribution
• correlation: Pearson/Spearman correlation coefficients
• correlationtests: Hypothesis testing: correlation tests
• descriptivestatistics: Descriptive statistics: mean, variance, etc.
• fdistr: F-distribution
• gammafunc
• hqrnd: High quality random numbers generator
• ibetaf
• igammaf
• jarquebera: Hypothesis testing: Jarque-Bera test
• mannwhitneyu: Hypothesis testing: Mann-Whitney-U test
• nearunityunit
• normaldistr: Normal distribution
• poissondistr: Poisson distribution
• stest: Hypothesis testing: sign test
• studenttdistr: Student’s t-distribution
• studentttests: Hypothesis testing: Student’s t-test
• variancetests: Hypothesis testing: F-test and one-sample variance test
• wsr: Hypothesis testing: Wilcoxon signed rank test