31 May 2010

DIST Direct: Build DISTs without array ranges

To create a DIST in Excel, you need a formula that returns an array of the desired size and content. In general, this means that somewhere in the spreadsheet there's a column of a thousand or ten thousand samples to be converted.

It struck me that this shouldn't be necessary if we're using a parametric distribution (e.g. uniform, gaussian,..).

The impediment is that in both the Excel stats package and XLSim, the parametric generators only return a single value which you then use in an array formula to create a range that you then feed to DstCreate. To fix this, we can take a page out of R and make generators that return arrays. This seemed straightforward but, as you'll see, there's a twist.

We'll start with a uniform random array generator of the simplest form:

array = runif(count)

This should allow us to create a DIST without an intermediate array formula range, like this:


producing a DIST with 1000 samples.

We should also be able to do the usual combinations, e.g.


Here's a VBA function that does it:

Function runif(count As Integer) As Variant
' Returns an array of fresh uniform random
'   numbers between 0 and 1
' Note the required dimensioning of the result.

    Application.Volatile True
    Dim ar() As Single
    ReDim ar(1 To count, 1 to 1)
    For i = 1 To count
        ar(i, 1) = Rnd()
    Next i
    runif = ar
End Function 

The "As Variant" is mandatory, as is dimensioning the array to a column.

runif is also usable in an array formula, but the array range has to be vertical. Wrap it with TRANSPOSE to create a horizontal range.

Doing it with an XLSim Generator

Update 5 April 2011 With recent versions of XLSim, some or all of the gen_ functions return a (1,1) array instead of a scalar. You'll need conditional coding to accomodate the different results. I've adjusted the code in the example below. Note the use of z.

Thanks to Graham Jeffery for showing us how to use XLSim generators this way. Here's the VBA:

Function rgnorm(count As Long, mean As Single, _
    sd As Single) as Variant
'   Returns an array of numbers from 
'     a Gaussian distribution
'   Using XLSim function
    Application.Volatile True
    Dim ar() As Single
    ReDim ar(1 To count, 0)
    Dim z
    For i = 1 To count
        z = Run("XLSim.xla!gen_normal", _
            mean, sd)
        ar(i, 0) = IIf((8204 = VarType(z)), _
             z(1, 1), z)        
    Next i
    rgnorm = ar
End Function


  1. If you want to use the gen_?? function in the XLSim library for your random numbers, use something like this.

    Dim Fn as workbook
    Fn = Workbooks("XLSim.xla").Name & "!gen_uniform"

    Ar(I, 0) = Application.Run(Fn, 0, 1)

  2. Oops! Fn should be dimensioned as a String, not a Workbook...

  3. There's been a problem using XLSim's gen_Myerson function from VBA. I haven't checked, but it may affect other gen_ functions. See my update in the article to be safe.