3

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?

kavmeister
  • 87
  • 3
  • 8
  • 3
    I can confirm the "problem". There is an array size limit when returning an array from a UDF even in Excel 2007/2010. I also don't have any other links for you, so this is just a comment. Finally, I couldn't see in the links you posted anything that suggests the array size limit *for return values from a UDF* is limited by "available memory". – jtolle Aug 11 '11 at 14:25
  • i too have the type cast error when it goes to large data – steave finner Mar 19 '13 at 12:44

2 Answers2

1

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.

jblood94
  • 10,340
  • 1
  • 10
  • 15
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.
Community
  • 1
  • 1
Allen Gammel
  • 396
  • 2
  • 4
  • 2
    Watch out! It uses [straight insertion sort](http://en.wikipedia.org/wiki/Insertion_sort)! Straight insertion is an N² routine, and should be used only for small N e.g. N < 20, not N=100000 for which it will be quite inefficient. – Jean-François Corbett Aug 15 '11 at 08:32
  • Thanks JFC. I have a focus here on minimizing runtime as much as possible. Can you suggest an alternative? – kavmeister Aug 15 '11 at 09:12