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?

Improve this question
2
  • 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". Commented Aug 11, 2011 at 14:25
  • i too have the type cast error when it goes to large data Commented Mar 19, 2013 at 12:44

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.
Improve this answer

2 Comments

Watch out! It uses straight 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.
Thanks JFC. I have a focus here on minimizing runtime as much as possible. Can you suggest an alternative?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.