## 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:

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
This entry was posted in AlgLib, Excel, Maths, Newton, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

### 4 Responses to ALGLIB Statistics Functions

1. Dave Ranf says:

Very nice! Appreciate the code sharing and conversion. Do you have a function for generating a skewed Student’s t distribution? I checked everywhere for a VBA or worksheet functions to do this, but alas none exists! ‘R’ has it as do other commercial software packages, but nothing native to VBA is out there currently. Will follow in case this is undertaken as it has wide applications in many disciplines.

Cheers!

Like

• dougaj4 says:

Dave – I don’t know, but I have posted the question at the Alglib Forum (http://forum.alglib.net/viewforum.php?f=2).

Like

• dougaj4 says:

Dave – see response from Sergey at ALGLIB at the link below:

http://forum.alglib.net/viewtopic.php?f=2&t=64&sid=5ca381415ee78e3d3fe981d53c0c567b

This is outside my area of expertise, so probably best to follow up directly at the ALGLIB site if you have any further questions.

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.