Percentile Usage

[ADezii]

From Access :

1. Alt-F11 to switch to the VB Editor.
2. Ctrl-R for the Project Explorer pane.
3. Right-Click in there and select Insert / Module.
4. Enter your code in here and save it.

Hello NeoPa, just wanted to let you know that I managed to send the Attachment to the OP via his Private E-Mail Address and all seems to have worked out well. Thanks.

[NeoPa]

8-)

Thanks for posting to clarify :)

[FishVal]

Alternatively, percentile could be calculated using SQL facilities only.
The following example gives 90th percentile of [Value] field in [tbl]

SELECT Max([Value]) AS Percentile
FROM 
(SELECT TOP 90 PERCENT [Value]
FROM [tbl]
ORDER BY [Value] ASC);

[ADezii]

Alternatively, percentile could be calculated using SQL facilities only.
The following example gives 90th percentile of [Value] field in [tbl]

SELECT Max([Value]) AS Percentile
FROM 
(SELECT TOP 90 PERCENT [Value]
FROM [tbl]
ORDER BY [Value] ASC);

Hell FishVal, just thought that you would find the following interesting in that there is a slight discrepancy between the 2 Methods, we'll simply call it FishVal vs Excel!

Percentiles      95th        75th        50th        25th
FishVal          100          88          67          21
Excel            101.15       88.25       67          22.5

P.S. - Results compiled with Sample Data listed below (Excel results rounded to 2 Decimal Places):

21
7
34
2
67
33
100
89
67
23
88
100
77
16
44
77
11
77
89
123

[FishVal]

:)

I know that the results are different.
This happens because dataset is by definition a set of discrete values, so SQL rounds percentile down to existing value while Excel seems to make interpolation.
Wikipedia article says both methods are in use.

Regards,
Fish.

PS: FYI, Excel could operate with up to 8191 values while SQL is limited by RDBMS specifications for max records.