Worksheet Function of part of ARRAY

[ronakv]

Hi All,

I am running a percentile function on an array of data.
My array consists of Past 5 years of data & i am looking to calculate percentile on values for each year.
In Excel sheet, I could refer to the Rows for each year and do it. But now i have to create it as a function in VBA.

Is there a way by which i cam apply something like .Percentile(Array(365 to 730), 25%) ? (i.e. this is like calculating percentile for Second Year)

 

[pgc01]

Hi
Welcome to the board

There are no vba native functions to extract part of an array.

The usual solution in this case is to create an auxiliary array with the values that you want and then process it.

There is, however, the possibility of using worksheet functions to get a part of the array (may have problems if more than 64k).

This is an example.

I load an array and a range with the same 1000 random values between 1 and 10000.
I then calculate the 25% percentile using a part of the range and the corresponding part of the array, and check that they are the same.

Try:

Sub Test()
Dim vArray(1 To 1000) As Long
Dim j As Long, k As Long

' Load some values in A1:A1000 and in the array for testing
For j = 1 To 1000
    k = Application.WorksheetFunction.RandBetween(1, 10000)
    Range("A" & j) = k
    vArray(j) = k
Next j

With Application
    MsgBox "Percentile 20% (200,600)" & vbNewLine & _
               "From Range: " & .Percentile(Range("A200:A600"), 0.25) & vbNewLine & _
               "From Array: " & .Percentile(.Index(vArray, [row(200:600)]), 0.25)
End With
End Sub