[RESOLVED] Problems passing decimal value to worksheet application Percentile

[PinInCalgary]

Office 2003 SP3, VBA: Retail 6.5.1024

I'm sure that I'm missing something obvious but I'm stumped. I'm trying to calculate the 95th percentile of an array of numbers. I've searched the web and found

Function u_percentile(arr() As Single, k As Single)
    Dim i As Integer, n As Integer, x As Integer
    Dim upper As Double
    
    On Error Resume Next
    upper = UBound(arr)
    If Err.Number Then
        If Err.Number = 9 Then
            u_percentile = 0
            Exit Function
        End If
    End If

    n = UBound(arr) - 1
    Call Sort(arr)
    x = Application.Max(Application.Min(Int(k * n), n), 1)
    u_percentile = arr(x)
    Exit Function

End Function

but all this does is return 17.8400810185185 when I have 17.8400810185185 and 24.3514814814815 as the two values in my test array. The excel percentile function returns something more realistic like 24.02591146 when I create a cell with a formula.

So I figured I'd try using the excel function by calling it from VBA but I seem to be getting the .9 percentile rather than the .95 percentile

Function u_percentile(arr() As Single, k As Single)
    Dim i As Integer, n As Integer, x As Integer
    Dim upper As Double
    
    On Error Resume Next
    upper = UBound(arr)
    If Err.Number Then
        If Err.Number = 9 Then
            u_percentile = 0
            Exit Function
        End If
    End If
    
    u_percentile = Application.WorksheetFunction.Percentile(arr(), 0.95)
    exit function
End Function

but this returns 23.7... which I get when plugging in 0.90 in to the formula cell I created in the worksheet rather than 24.02591146 when I plug 0.95 in to the formula cell I created in the worksheet.

Is there some way to force the call to Percentile to use 0.95 rather than 0.9?

Thank you in advance

[anhn]

I've never used this function. Look at the last remark.

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 k-th percentile.

[PinInCalgary]

Thank you for the response.

I was looking at that but not certain that it applies. I'd expect that whether I call percentile via

worksheetfunction.percentile(arr(),0.95)

or if I just plunk the formula

=percentile(a1:b1,.95)

in a cell in the worksheet it should provide the same result?

Maybe I'm out in left ballpark with my thinking?

[anhn]

Your function is not good and it works with array but not sheet Range.
Why build your trouble function while you already have WorksheetFunction PERCENTILE() that do the samething for you?
=PERCENTILE("A1:A20", 0.95)

[PinInCalgary]

Thank you again for your reply.

As indicated both in my original post, and thought the title helped, and also in my response to your previous post I did try

worksheetfunction.percentile(arr(),0.95)

which seems to return the 90th percentile rather than the 95th percentile.

Guess I wasn't clear.

Anyway, I guess in this thread I'm trying to figure out why

worksheetfunction.percentile(arr(),0.95)

returns the 90th percentile and not the 95th percentile?

[westconn1]

if your function parameters are both doubles (or variant), do you then get the correct result?

reason to use variant is it will accept a range as an array

[PinInCalgary]

Thank you for the response. I've updated the code to dimension everything as double but still no joy.

Function u_percentile(arr() As Double, k As Double)
    Dim i As Integer, n As Integer, x As Integer
    Dim upper As Double
    
    On Error Resume Next
    upper = UBound(arr)
    If Err.Number Then
        If Err.Number = 9 Then
            u_percentile = 0
            Exit Function
        End If
    End If
    
    u_percentile = Application.WorksheetFunction.Percentile(arr(), 0.95)
    Exit Function

End Function

[PinInCalgary]

Also replaced the .95 in the worksheetfunction.percentile with "k" which is defined as double and passed from the calling subroutine.

[anhn]

For checking, can you show values of your arr() ?

[PinInCalgary]

Your last post triggered something with me and I've figured out what my problem is.

While my worksheet range only had the two values, when I build arr() I start adding at arr(1) rather than arr(0). But when I pass arr() to worksheetfunction.percentile it gets the whole arr() which starts at index 0.

So arr() is actually

arr(0): 0
arr(1): 24.3514814814815
arr(2): 17.8400810185185

when it is passed to worksheetfunction.percentile(arr(),.95) rather than what I thought was

arr(1): 24.3514814814815
arr(2): 17.8400810185185

and so the value is 23.70034144 rather than 24.02591146.

It just happened that the two numbers I was using had a P90 that came to the approximately the same value as a P95 of arr() with the three values resulting in this embarrasing goose chase.

Thank you everyone for the help. I'll now update the thread as resolved.

[westconn1]

i tested before i posted

i used your same values, in 2 cells of work sheet
put your formula into 3rd cell for control

i read the cells into an array an passed to your function
which when modified returned the same results as the worksheet formula