Percentile --> how to?

[BHARATH RAJAMANI]

Hi

I have 5mio currency values in an unsorted VBA array. I need to find the
99.99th percentile highest value without writing the array to a spreadsheet
and using the worksheetfuntion.percentile

Any ideas? TIA!!

Rgds,
BR

 

[Bernie Deitrick]

BR,

You can use

MyVal = Application.worksheetfuntion.percentile(Array, 0.99)

within VBA, without writing the array to the sheet.

HTH,
Bernie
MS Excel MVP

 

[BHARATH RAJAMANI]

Bernie, Thx for the tip. It helps a bit, but I still have a problem with
large datasets. This works well for small array sizes but not for large
arrays. I get a type mismatch error. Here's my macro -

'Macro

Dim EL as Double
Dim EL_99 as double
Dim NetLosses() As Double
'... Read array size = TotalScenarios = values from 250,000 to 5,000,000
ReDim NetLosses(TotalScenarios) As Double
'...
For ctr1 = 0 to TotalScenarios - 1
NetLosses(ctr1) = Ccur(1234567.89) 'Populate array with some value
next ctr1

'Type mismatch error here for large array sizes, works well for small array
sizes
EL = CDbl(Application.WorksheetFunction.Average(NetLosses()))
'Type mismatch error for large array sizes, works well for small array sizes
EL_99 = Application.WorksheetFunction.Percentile(NetLosses(), 0.9999)


TIA!!

Rgds,
BR


--
Capital Markets
GE Capital, London

BR,

You can use

MyVal = Application.worksheetfuntion.percentile(Array, 0.99)

within VBA, without writing the array to the sheet.

HTH,
Bernie
MS Excel MVP

 

[Bharath Rajamani]

Ref: From the Help menu on using the Percentile function:

If the array has more than 8,191 values then it will not work. I guess this
resolves the Q on why worksheetfunction.percentile returns errors with arrays
of 250k to 1mio values


Rgds,
BR




PERCENTILE
See Also

Returns the k-th percentile of values in a range. You can use this function
to establish a threshold of acceptance. For example, you can decide to
examine candidates who score above the 90th percentile.

Syntax

PERCENTILE(array,k)

Array is the array or range of data that defines relative standing.

K is the percentile value in the range 0..1, inclusive.

Remarks

If array is empty or contains more than 8,191 data points, PERCENTILE
returns the #NUM! error value.


If k is nonnumeric, PERCENTILE returns the #VALUE! error value.


If k is < 0 or if k > 1, PERCENTILE returns the #NUM! error value.


If k is not a multiple of 1/(n - 1), PERCENTILE interpolates to determine
the value at the kth percentile.

Example

PERCENTILE({1,2,3,4},0.3) equals 1.9

BR,

You can use

MyVal = Application.worksheetfuntion.percentile(Array, 0.99)

within VBA, without writing the array to the sheet.

HTH,
Bernie
MS Excel MVP