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.


Charles

Look in Excel help at the percentile worksheet function.

--
Regards,
Tom Ogilvy


"Charles Deng" wrote in message
...
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.


Charles

Hi Tom:

Thanks a lot. I did find that instruction but I still
have a question.

The example given in the instrction is

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

It seems that I have to enter all number of array. But I
would like to use the array of numbers in the data set.
Suppose I have a variable which contains numbers from 1.00
(min) to 9.25 (max) and there are 1000 numbers there. Can
I just type in min and max rather than all different
numbers in the array? Thanks.

Charles



-----Original Message-----
Look in Excel help at the percentile worksheet function.

--
Regards,
Tom Ogilvy


"Charles Deng"
wrote in message
...
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.


Charles





.

no.

If a range/worksheet formula

=Percentile(A1:A1000,0.3)


If we are talking code: (from the immediate window)

? application.Percentile(Range("A1:A1000"),0.3)
292.7

to show use of an array, I will pick up the values from A1:A1000, but the
array could be populated other ways.
varr = Range("a1:a1000").Value
? APPLICATION.Percentile(varr,0.3)
292.7

--
Regards,
Tom Ogilvy



"Charles Deng" wrote in message
...
Hi Tom:

Thanks a lot. I did find that instruction but I still
have a question.

The example given in the instrction is

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

It seems that I have to enter all number of array. But I
would like to use the array of numbers in the data set.
Suppose I have a variable which contains numbers from 1.00
(min) to 9.25 (max) and there are 1000 numbers there. Can
I just type in min and max rather than all different
numbers in the array? Thanks.

Charles



-----Original Message-----
Look in Excel help at the percentile worksheet function.

--
Regards,
Tom Ogilvy


"Charles Deng"
wrote in message
...
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.


Charles





.

Hi Tom:

Thank you so much. I know how to do this on Excel now.
But could I ask you if I can use these code for Access
report? Or if we cannot, could you tell me how we can
export this results to Acess report? Thank you very much.

Charles



-----Original Message-----
no.

If a range/worksheet formula

=Percentile(A1:A1000,0.3)


If we are talking code: (from the immediate window)

? application.Percentile(Range("A1:A1000"),0.3)
292.7

to show use of an array, I will pick up the values from
A1:A1000, but the
array could be populated other ways.
varr = Range("a1:a1000").Value
? APPLICATION.Percentile(varr,0.3)
292.7

--
Regards,
Tom Ogilvy



"Charles Deng"
wrote in message
...
Hi Tom:

Thanks a lot. I did find that instruction but I still
have a question.

The example given in the instrction is

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

It seems that I have to enter all number of array. But I
would like to use the array of numbers in the data set.
Suppose I have a variable which contains numbers from
1.00
(min) to 9.25 (max) and there are 1000 numbers there.
Can
I just type in min and max rather than all different
numbers in the array? Thanks.

Charles



-----Original Message-----
Look in Excel help at the percentile worksheet
function.

--
Regards,
Tom Ogilvy


"Charles Deng"
wrote in message
...
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.


Charles





.



.

Hi
I would ask this question in the Access newsgroup :-)

--
Regards
Frank Kabel
Frankfurt, Germany

schrieb im Newsbeitrag
...
Hi Tom:

Thank you so much. I know how to do this on Excel now.
But could I ask you if I can use these code for Access
report? Or if we cannot, could you tell me how we can
export this results to Acess report? Thank you very much.

Charles



-----Original Message-----
no.

If a range/worksheet formula

=Percentile(A1:A1000,0.3)


If we are talking code: (from the immediate window)

? application.Percentile(Range("A1:A1000"),0.3)
292.7

to show use of an array, I will pick up the values from
A1:A1000, but the
array could be populated other ways.
varr = Range("a1:a1000").Value
? APPLICATION.Percentile(varr,0.3)
292.7

--
Regards,
Tom Ogilvy



"Charles Deng"
wrote in message
...
Hi Tom:

Thanks a lot. I did find that instruction but I still
have a question.

The example given in the instrction is

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

It seems that I have to enter all number of array. But I
would like to use the array of numbers in the data set.
Suppose I have a variable which contains numbers from
1.00
(min) to 9.25 (max) and there are 1000 numbers there.
Can
I just type in min and max rather than all different
numbers in the array? Thanks.

Charles



-----Original Message-----
Look in Excel help at the percentile worksheet
function.

--
Regards,
Tom Ogilvy


"Charles Deng"
wrote in message
...
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.


Charles





.



.

On Tue, 5 Oct 2004 12:04:52 -0700, "Charles Deng"
wrote:

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
-----



klaus