I need to calculate Percentile or Quantile in Access

[Guest]

Microsoft Excel has the funtion percentile, however, I am unable to find any
code or any way in Access to do percentile or Quantile. I have a number of
calculations that I need to do that require me to find the 99th Percentile
P99, 50th Percentile P50 and the 1 percentile - or P1. Does anyone have any
code that can help resolve this issue? The number of numbers as well as
range of numbers will vary from week to week as well. This application is to
do metrics associated with a table that changes every week.

 

[Guest]

Please note: I already have the data in an Array. I just need a way to
calculate the Percentiles.

 

[Tim Ferguson]

=?Utf-8?B?aGFycnkubWFydGluQGdlLmNvbQ==?=

I have a number of
calculations that I need to do that require me to find the 99th
Percentile P99, 50th Percentile P50 and the 1 percentile - or P1.
Does anyone have any code that can help resolve this issue?

Have you actually tried a solution yet? It's not hard, but first of all
you have to be clear about the algorithm you want to use.

Public Function GetPC(a As Variant, p As Integer) As Variant
' assumption: a is an array variant, sorted
' return value is variant in case the result is null

' avoid array bound problems; in any case zero or hundredth
' centiles are meaningless
If p < 1 Or p > 99 Then
GetPC = Null
Exit Function
End If

' jump out if it's not a valid array
If (VarType(a) - vbArray < 2 Or _
VarType(a) - vbArray > 6) And _
VarType(a) - vbArray <> 12 Then
GetPC = Null
Exit Function
End If


Dim n As Integer ' less than 65000 items
' variants always start at zero.
n = UBound(a)

Dim x As Double, f As Double
x = 0.01 * n * p ' get exact position of percentile
f = x - Int(x) ' and how near it is to its neighbours

' interpolate between the two values either side
GetPC = (a(Int(x)) * (1 - f)) + (a(Int(x + 1)) * f)

End Function


Hope that helps


Tim F