Percentile Calculation Problem in Access

[bchess1]

I’m trying to create an automated solution for our HR department for calculating salary percentiles and I’m completely lost. I have a dataset in an Access 2000 database that has a list of job titles and beside the title their salary. I need to produce a report that groups on title and calculates the salaries 25th, 50th, and 75th percentile as well as the average. I know how to do this in Excel, but would prefer to do this in Access where our data resides. Any thoughts on how this can be accomplished?

 

[mp9]

There is a function you can use called Percentile. The following is an extract from A2000 help:

PERCENTILE
Returns the k-th percentile of values in a range.

If this function returns the #NAME? error value, you may need to install msowcf.dll.

Syntax

PERCENTILE(array,k)

Array   is the range of data that defines relative standing, up to 8,191 data points.

K   is the percentile value that is between 0 through 1 inclusive.

HTH.

http://www22.brinkster.com/accessory/

 

[bchess1]

mp9

Thank you for the reply. I also pulled up that same help file, but it is listed under spreadsheet functions.

 

[mp9]

This isn't perfect (for the reasons explained at in the last paragrpah at

http://www22.brinkster.com/accessory/queries/004.shtml)

but this should return the 25th percentile salary value and can be easily modified for 50%, 75%, 100%

I haven't tested this though, so you might want to do some validation - anyway, this code assumes your table is called tblSalary and the salary field is called SalaryValue

SELECT Max(Temp.SalaryValue) AS Percent25
FROM (SELECT TOP 25 PERCENT tblSalary.SalaryValue
FROM tblSalary
ORDER BY tblSalary.SalaryValue ASC) AS Temp;

HTH

http://www22.brinkster.com/accessory/

 

[MichaelRed]

Public Function basPercentile(Pctl As Single, ParamArray MyAray() As Variant) As Single

    'Explination Courtsey of Rohit Pareek, Ratangarh, India
    'Tek-Tips; thread710-170007

    'You have a range of data sorted in acsending order....
    'e.g. 1567, 1634, 1700, 2100, 2230, 2400, 2550

    'and you want to calculate 75th percentile point... then
    'All you need to do is to reach to Nth element in the series,
    'where N is explained below.

    'N = (N-1)* p + 1 ,
    'n = number of elements in the series
    'and p in our case is .75

    'So in our case N=((7-1)*0.75)+1 which is 5.5.
    'That means in the above series 5.5th element is your answer
    '   ....which youcan reach in following way....
    '1. Reach to 5th element..Store it into a variable called "BasePercent".
    '2. Get the difference of 6th and 5th elements and multiply that by .5,
    'Store this into a variable called "ExtraPercent".
    '3. Now add BasePercent and ExtraPercent and store the result into
    'another cariable "RealPercent"

    '.....THIS IS YOUR ANSWER...... YOU CAN TEST YOUR ANSWER BY PUTTING THE SAME
    'RANGE IN EXCEL AND USING THE EXCEL FORMULA....

    'Code by Michael Red    7/26/2002
    'Sample Usage:
    '? baspercentile(0.75, 1567, 1634, 1700, 2100, 2330, 2400, 2550)
    ' 2365

    Dim NthEl As Single         '~ N
    Dim StrtPctle As Single     '~ BasePercent above
    Dim IncrPctle As Single     '~ Invremental Percentage ~~ 0.5
    Dim DeltPctle As Single     '~ Extra Percent

    NumElems = UBound(MyAray) + 1
    NthEl = (NumElems - 1) * (Pctl) + 1
    If (NthEl = Int(NthEl)) Then
        basPercentile = MyAray(NthEl)
     Else
        StrtPctle = MyAray(Int(NthEl - 1))
        IncrPctle = (NthEl - Int(NthEl))
        DeltPctle = (MyAray(Int(NthEl)) - MyAray(Int(NthEl - 1)))
        basPercentile = StrtPctle + (IncrPctle * DeltPctle)
    End If


End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places