Computing Percentiles in MS Access

[eHigh]

How could I compute the 25th, 50th, and 75th percentiles for the values below in MS Access?

17.2068311195446
46.2558502340094
4.99548328816621
15.6774955699941
9.82629107981221
40.3058419243986
18.5503711558855
69.0640279394645
5.09231685527099
19.3273542600897
11.422159887798
29.8877968877969
28.6897918731417
14.8788059701493
5.63025210084034
84.8173469387755
34.5892691951896
2.9539641943734
19.0945757997218
14.5243770314193
10.3452527743527
1.10918367346939
36.7235890014472
18.7926895799236
51.1657303370786
22.5509977827051
62.9413131900058
35.7509433962264
18.3997933884298
20.0107003891051
6.27498588368154
14.9576453697057
27.4663930220626
31.1417322834646
25.8351477449456
43.7604290822408
6.50208333333333
5.58674803836094
41.9925925925926
13.6378035902851
15.0180831826401
12.2258248009101
2.82793709528215
1.39558232931727
60.3935483870968
28.2236763831053
7.69189670525378
29.4040404040404
5.29809586342745
14.4749631811487
5.54162162162162
6.45857142857143
27.8659611992945
32.888665325285
5.14160839160839
400.337662337662

 

[MichaelRed]

search these (Tek-Tips) fora re 'basPercentile'





MichaelRed
mlred@verizon.net

 

[eHigh]

Michael,

Thanks for the feedback.

When I call the 'basPercentile' function I get an error.
To get the 25th percentile I use the call below:

basPercentile(0.25, FieldName)

Is there something wrong with my call statement?

Also, what if I want to set a reference to the Excel Library and use the Percentile function in Excel?

My function call still gives me an error. Am I not calling the function correctly?

Public Function calPercentile(MyAray() As Variant, Pcntl As Single) As Single

Dim Xcel As Excel.Application
calPercentile = Xcel.WorksheetFunctions.Percentile(MyAray(), Pcntl)

End Function

calPercentile(MyAray(), Pcntl)

Thanks in advance for any assistance.

 

[PHV]

Any chance you could post the error messages ?
Anyway, the Percentile excel function expects an array of Double (not variant).

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eHigh]

When I used the following SQL statement I get the error below:

SELECT Count([2002perCaps].ID) AS NumberOfLibraries, Min([2002perCaps].percap) AS LOW, Max([2002perCaps].percap) AS HIGH, Avg([2002perCaps].percap) AS AVERAGE,
basPercentile(0.25, [2002perCaps].percap) AS 25th_Percentile
FROM 2002perCaps
HAVING ((([2002perCaps].PopSrvd)<2000));

You tried to execute a query that does not include the specified expression 'basPercentile(0.25, [2002perCaps].percap)' as part of the aggregate function.

 

[MichaelRed]

If I recall correctly, the routine expects an ARRAY of values, unlike the Excel Spreadshhe version. Further, it is placed in an Sggregate ("Summation") query. Aggregate queries require every "Column" to be specified as a member of the 'aggregation' (Group By; Sum; Min; Max; Expression; etc.) I think it would be easier to generate the Percentile seperatly, based on the criteria, but that also requires some additional understanding of the overall process.

The referenced thread (e.g. Routine) should provide neough 'insight' to the process to adapt to uyou needs.

MichaelRed
mlred@verizon.net