Hi All:
Is there any formula I can use to calculate percentile
value? Suppose I want to know top 75 percentile value of
a varialbe, can I just write a formula to obtain that
value as we do for mean, max, min, frequency, etc.? Any
advice will be highly appreciated.
If this question is stupid or impossible, please let me
know, too. Thanks.
from
www.anthony-vba/kefra.com/vba
In order to get the percentile, the data needs to be sorted. In the
sub routine (GetPercentile()) below, 10 random numbers between 1 to 50
are assigned to an array. The sub routine calls the percertile
function (u_percentile()). The function calls the Sort sub routine to
sort the array. The function gets the value from the array based on
the percentile (40%), and returns the percentile value back to the sub
routine.
Notice that Application.Max(Application.Min(Int(k * n), n), 1) in the
percentile function makes sure that first, the array index is an
integer and second, the maximum value and the minimum value for the
array index will not excess the number of elements in the data set or
below 1, respectively.
Sub GetPercentile()
Dim arr(10) As Single
For i = 1 To 10
arr(i) = Int(Rnd * 50) + 1
Cells(i, 1) = arr(i)
Next i
Cells(10, 2) = u_percentile(arr, 0.4)
' 0.4 would be the desired percentile,
' change as desired
End Sub
Function u_percentile(arr() As Single, k As Single)
Dim i As Integer, n As Integer
n = UBound(arr)
Call Sort(arr)
x = Application.Max(Application.Min(Int(k * n), n), 1)
u_percentile = arr(x)
End Function
Sub Sort(ByRef arr() As Single)
Dim Temp As Single
Dim i As Long
Dim j As Long
For j = 2 To UBound(arr)
Temp = arr(j)
For i = j - 1 To 1 Step -1
If (arr(i) <= Temp) Then GoTo 10
arr(i + 1) = arr(i)
Next i
i = 0
arr(i + 1) = Temp
If j Mod 100 = 0 Then
Cells(26, 5) = j
End If
Next j
End Sub
