WorksheetFunction array size limit

Question

I'm trying to calculate the 99.5% percentile for a data set of 100000 values in an array (arr1) within VBA using the percentile function as follows:

Pctile = Application.WorksheetFunction.Percentile(arr1, 0.995)
Pctile = Application.WorksheetFunction.Percentile_Inc(arr1, 0.995)

Neither works and I keep getting a type mismatch (13).

The code runs fine if I limit the array size up to a maximum of 65536. As far as I was aware calculation limited by available memory since Excel 2007 array sizes when passing to macro limited by available memory since Excel 2000.

I'm using Excel 2010 on a high performance server. Can anyone confirm this problem exists? Assuming so, I figure that my options are to build a vba function to calculate the percentile 'manually' or output to a worksheet, calculate it there and read it back. Are there any alternatives and what would be quickest?

Answer 1

Here is a Classic VBA example that mimics the Excel Percentile function.

Percentile and Confidence Level (Excel-VBA)

In light of Jean's exposure of the Straight Insertion method being inefficient. I've edited this answer with the following:

I read that QuickSelect seems to excel with large records and is quite efficient doing so.

References:

1. Wikipedia.org: Quick Select
2. A C# implementation can be found @ Fast Algorithm for computing percentiles to remove outliers which should be easily converted to VB.

Answer 2

The error would occur if arr1 is 1-dimensional and has greater than 65536 elements (see Charles' answer in Array size limits passing array arguments in VBA). Dimension arr1 as a two-dimensional array with a single column:

Dim arr1(1 to 100000, 1 to 1)

This works in Excel 2013. Based on Charles' answer, it appears that it will work with Excel 2010.