11 June 2012

Sample Distributions for Excel - SDXL Ver.0.4.0

SDXL is a free, open-source Excel Add-in I developed to bring Probability Management techniques and strong array handling to Excel. Most of the functions manipulate and calculate with sample distributions represented as arrays. The arrays passed into functions as arguments can be of several types that the functions detect and handle automatically. The types are:

  • a VBA array
  • an XML string.
  • a CSV (Comma Separated Values) string
  • A cell range

There are functions for converting one format to another and for reading and writing XML files containing sample distributions. There are also simple functions to present sample distributions graphically with histograms, scattercharts and percentile charts (cumulative probability).

The math functions include all the usual arithmentic, boolean and comparison functions. It's also possible to mix array and scalar arguments.

The code to multiply two sample distributions and return the result as a CSV string in a cell is:

= toCSV( sdMul( arrayA, arrayB))

The arguments arrayA and arrayB could be just about anything that can be interpreted as a sample distribution.

There are also a lot of functions that assume the arrays are sample distributions and do statistical stuff. There's a complete set of sampling, sorting and permuting functions dedicated to manipulating the array elements – in particular, their order.

And, of course, there are a bunch of random number generators using both VBA's built-in generator and an efficient Mersenne Twister implementation.

I think everything needed to develop fairly interesting simulations and models is there. There are two things that are conspicuous by their absence: Coherent sets of distributions and distribution time-series. I like to have real use cases to drive a design and since I've been concentrating on project planning and estimating, I haven't seen any of these – yet.

To get SDXL, go to smpro.ca/SDXL .

There's also a Google+ Community at +SDXL Users

No comments:

Post a Comment