Newton Excel Bach, not (just) an Excel Blog

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