Percentile calculation in Access

[riaane]

Hey clever people, Excel has the =PERCENTILE function. This calulates a percentile value from an array of values. Is there any equivalent in Access?

[ADezii]

There is no equivalent in Access, but if I get a chance I'll see if I can create an Access Version for you, or use Automation Code to obtain a Percentile for Access data using the actual Excel Function.

[ADezii]

Back again, decided to let Excel do all the work:

'Make sure to 1st set a Reference to the
'Microsoft Excel XX.X Object Library
Dim intNumbers(3) As Integer
Dim intCounter As Integer
Dim objExcel As Excel.Application
Const conPercentile As Single = 0.25       'must be >=0 And <=1

Set objExcel = CreateObject("Excel.Application")

'Assign values to the Array
intNumbers(0) = 4
intNumbers(1) = 2
intNumbers(2) = 1
intNumbers(3) = 3

'Pass the Array and Percentile to Excel's Percentile() Function
Debug.Print "And the Percentile is: " & objExcel.Application.Percentile(intNumbers(), conPercentile)
 
objExcel.Quit
Set objExcel = Nothing

OUTPUT:

And the Percentile is: 1.75

[riaane]

Whoa ADezii, you are way above my skill level here *sheepish* I am not a programmer in any shape or form and use Access 2007 only as a tool to help me consolidate statistics.

1. I assume the code you list is VB that has to be included in a function of the Data control of the field I require the calculation on?

2. My array source is a CSV file generated by a system of my client and can consist of anything between 20 and 2000 records, each record containing 8 fields, of which I need the 85th Percentile of only 1 of those fields. Therefore your step "10. 'Assign values to the Array" is actually already done by importing this file into my Access DB and running queries and reports to end up with a consolidated fiew of the original data and is therefore not a manual process. I assume this step will be replaced by referring to my table and exporting that table (or maybe just the relevant field I need the Percentile calculation on) to Excel?

Thank you for taking the time to reply to my request :-)

[ADezii]

[QUOTE=riaane]Whoa ADezii, you are way above my skill level here *sheepish* I am not a programmer in any shape or form and use Access 2007 only as a tool to help me consolidate statistics.

1. I assume the code you list is VB that has to be included in a function of the Data control of the field I require the calculation on?

2. My array source is a CSV file generated by a system of my client and can consist of anything between 20 and 2000 records, each record containing 8 fields, of which I need the 85th Percentile of only 1 of those fields. Therefore your step "10. 'Assign values to the Array" is actually already done by importing this file into my Access DB and running queries and reports to end up with a consolidated fiew of the original data and is therefore not a manual process. I assume this step will be replaced by referring to my table and exporting that table (or maybe just the relevant field I need the Percentile calculation on) to Excel?

Thank you for taking the time to reply to my request :-)

I need to see what the 'finalized' data looks like in your Table, specifically the one Field on which you would like to calculate the 85th Percentile. I'm a little confused as to how this Array is stored in your Table (Complex Data Type?). The Table need not be Exported to Excel. You can basically create a Recordset based on this Table, and for each Record calculate the 85th Percentile for the specific Field in question. This calculation can easily be done via a Calculate Field in a Query which calls the actual Function that performs the calculation. The Field containing the Array Data is passed as an Argument, something similar to:

[Percentile]:fCalculatePercentile([<Array Field>])

I do not have Access 2007, but when I get a chance I'll post soome parallel code.

[riaane]

[QUOTE=ADezii]

I need to see what the 'finalized' data looks like in your Table, specifically the one Field on which you would like to calculate the 85th Percentile. I'm a little confused as to how this Array is stored in your Table (Complex Data Type?).

Hi ADezii, here is a sample if the data in the field I need the 85th Percentile calculated from:

107.5
94.6
73.2
73.2
73.4
87.7
79.3
73.1
73.3
73.3
104.9
86.7
73.2
85.8
73.4
99.7
74
85.9
73.2
73.5
73.2

The 85th Percentile value for this array btw = 94.6

Hope this helps

[ADezii]

[QUOTE=riaane]

Hi ADezii, here is a sample if the data in the field I need the 85th Percentile calculated from:

107.5
94.6
73.2
73.2
73.4
87.7
79.3
73.1
73.3
73.3
104.9
86.7
73.2
85.8
73.4
99.7
74
85.9
73.2
73.5
73.2

The 85th Percentile value for this array btw = 94.6

Hope this helps

I ran your Data set through my Algorithm, and the result is as posted below. I'll also post the revised code for your convenience. I still need to see the physical layout of these Values in the Field in your Table, so I can design a Method to extract them, and run them through Excel's Percentile Function:

'Make sure to 1st set a Reference to the
'Microsoft Excel XX.X Object Library
Dim sngNumbers(21) As Single
Dim objExcel As Excel.Application
Const conPERCENTILE As Single = 0.85       'must be >=0 And <=1
 
Set objExcel = CreateObject("Excel.Application")
 
'Assign values to the Array
sngNumbers(0) = 107.5
sngNumbers(1) = 94.6
sngNumbers(2) = 73.2
sngNumbers(3) = 73.2
sngNumbers(4) = 73.4
sngNumbers(5) = 87.7
sngNumbers(6) = 79.3
sngNumbers(7) = 73.1
sngNumbers(8) = 73.3
sngNumbers(9) = 73.3
sngNumbers(10) = 104.9
sngNumbers(11) = 86.7
sngNumbers(12) = 73.2
sngNumbers(13) = 85.8
sngNumbers(14) = 73.4
sngNumbers(15) = 99.7
sngNumbers(16) = 74
sngNumbers(17) = 85.9
sngNumbers(18) = 73.2
sngNumbers(19) = 73.5
sngNumbers(20) = 73.2
 
'Pass the Array and Percentile to Excel's Percentile() Function
Debug.Print "And the [" & conPERCENTILE & "] Percentile is: " & _
             objExcel.Application.Percentile(sngNumbers(), conPERCENTILE)
 
objExcel.Quit
Set objExcel = Nothing

OUTPUT:

And the [0.85] Percentile is: 93.5650016999252

[riaane]

Dear ADezii,

Please see a screen of the table:
Table Laayout

I only need the calculation on the OffenceSpeed field

I hope this answers your query "I still need to see the physical layout of these Values in the Field in your Table, so I can design a Method to extract them, and run them through Excel's Percentile Function"

[ADezii]

Dear ADezii,

Please see a screen of the table:
Table Laayout

I only need the calculation on the OffenceSpeed field

I hope this answers your query "I still need to see the physical layout of these Values in the Field in your Table, so I can design a Method to extract them, and run them through Excel's Percentile Function"

Just a couple more questions:

• How are the Grouping Levels defined, namely the 21 Values of 107.5 to 73.2?
• Is the .85 Percentile to be calculated for all Values in the OffenceSpeed Field in tblLogData?

[riaane]

Just a couple more questions:

• [1] How are the Grouping Levels defined, namely the 21 Values of 107.5 to 73.2?
  
  [2] Is the .85 Percentile to be calculated for all Values in the OffenceSpeed Field in tblLogData?

1. The Grouping levels are for Max, Count and Avg
2. Yes, the calculation should be for all Values in the OffenceSpeed field only. Take note that this field can consist of anything between 200 and 2000 record entries as the different files imported into this table has different number of records every time. So the 21 values shown in the screen is only a sample of the 258 records in the table for that import. Therefore there should not be a hard coded definition of the number of records to be used in the Percentile calculation. Obviously the values differ every time as well so the values has to come from the field ([OffenceSpeed])
Please see the form I need the final calculation to apear on.

[ADezii]

1. The Grouping levels are for Max, Count and Avg
2. Yes, the calculation should be for all Values in the OffenceSpeed field only. Take note that this field can consist of anything between 200 and 2000 record entries as the different files imported into this table has different number of records every time. So the 21 values shown in the screen is only a sample of the 258 records in the table for that import. Therefore there should not be a hard coded definition of the number of records to be used in the Percentile calculation. Obviously the values differ every time as well so the values has to come from the field ([OffenceSpeed])
Please see the form I need the final calculation to apear on.

The following code will calculate the .85 Percentile for all values entered into the [OffenseSpeed] Field, rounded to 2 Decimal places. The actual number of entries should make no difference, since the code is flexible enough to adapt for varying Record Counts. I'll post the code and a sample Call below, but first some very importatnt points to mention:

1. The code is based on a Field named [OffenseSpeed] in a Table named tblLogData. If this is not accurate, change the Table and/or Field Name in the code.
2. You must set a Reference to the Microsoft Excel XX.X Object Library.
3. [OffenseSpeed] must be a Numeric Data Type, specifically {SINGLE}.
4. [OffenseSpeed] 'must' be a Required Field or Errors will result.
5. The code calculates the .85 Percentile, to modify the Percentile, change the Value of the Constant.
6. The Function (fCalculatePerc entile) must be Declared as Public in a Standard Code Module.
7. The result of the Function Call is rounded to 2 Decimal Places. To modify this, change the 2nd Argument of the Round() Function within the Function.
8. Any questions, feel free to ask, but I think everything was pretty much covered. Let me know how you make out.
9. Just for curiosity, what is the purpose of this Percentile calculation, if it can be revealed?
10. After a long drum roll, here is the Function (tested and operational):
    

    Public Function fCalculatePercentile() As Single
    'Make sure to 1st set a Reference to the
    'Microsoft Excel XX.X Object Library
    Dim sngNumbers() As Single
    Dim intNumberOfRecords As Integer
    Dim objExcel As Excel.Application
    Dim intCounter As Integer
    Dim MyDB As DAO.Database
    Dim rstPercentile As DAO.Recordset
    Const conPERCENTILE As Single = 0.85       'must be >=0 And <=1
    
    Set MyDB = CurrentDb()
    Set rstPercentile = MyDB.OpenRecordset("tblLogData", dbOpenSnapshot)
    rstPercentile.MoveLast: rstPercentile.MoveFirst     'accurate Record Count
    
    intNumberOfRecords = rstPercentile.RecordCount
    
    'Redimension the Array to proper values
    ReDim sngNumbers(1 To intNumberOfRecords)
     
    Set objExcel = CreateObject("Excel.Application")
    
    'Populate the Array with values from the Recordset
    For intCounter = 1 To intNumberOfRecords
      With rstPercentile
        sngNumbers(intCounter) = ![OffenseSpeed]
        .MoveNext
      End With
    Next
     
    'Pass the Array to Excel's Percentile Function and Round the result to 2 Decimal Places
    fCalculatePercentile = Round(objExcel.Application.Percentile(sngNumbers(), conPERCENTILE), 2)
     
    'Don't forget to clean up!!!
    rstPercentile.Close
    Set rstPercentile = Nothing
    objExcel.Quit
    Set objExcel = Nothing
    End Function

11. Sample Call to fCalculatePerce ntile():
    

    MsgBox "The .85 Percentile equals: " & fCalculatePercentile()

P.S. - To have the result appear in a Text Box on your Form, set the Control Source of the Text Box on the Form equal to the Function, namely: =fCalculatePerc entile()

[riaane]

Dear ADezii, thank you so much for all your patience and effort. You are a saint! :-)
I ran your code but get a Compile Error even though I have my Reference set correctly.

What did I break?

"Just for curiosity, what is the purpose of this Percentile calculation, if it can be revealed?"

The 85th Percentile calculation is an international benchmark used by Speed Law Enforcement Agencies to decide from what speed upwards speed law enforcement should happen. In simplified terms, it basically states that 85% of motorists drive at or below this speed on that particular road. That gives a statistical indication of the safe speed limit for that streach of road as the general assumption is that most motorists act as responsible and reasonable drivers for the prevailing road conditions, road surface and road type (mountain pass, sharp bends, streight sections, etc).

The manual way to calculate this value is to fill out a form of a sample of 125 vehicles trevelling past an observer and then filling in each vehicle speed in the relevent speed increment. More detail on the methodology can be found here .

See why I rather want my PC to calculate this? ;-)

[ADezii]

Dear ADezii, thank you so much for all your patience and effort. You are a saint! :-)
I ran your code but get a Compile Error even though I have my Reference set correctly.

What did I break?

"Just for curiosity, what is the purpose of this Percentile calculation, if it can be revealed?"

The 85th Percentile calculation is an international benchmark used by Speed Law Enforcement Agencies to decide from what speed upwards speed law enforcement should happen. In simplified terms, it basically states that 85% of motorists drive at or below this speed on that particular road. That gives a statistical indication of the safe speed limit for that streach of road as the general assumption is that most motorists act as responsible and reasonable drivers for the prevailing road conditions, road surface and road type (mountain pass, sharp bends, streight sections, etc).

The manual way to calculate this value is to fill out a form of a sample of 125 vehicles trevelling past an observer and then filling in each vehicle speed in the relevent speed increment. More detail on the methodology can be found here .

See why I rather want my PC to calculate this? ;-)

Code Line #12 is exactly as stated, don't change it, it will automatically set a Reference to the Current Database.

Set MyDB = CurrentDb()

[riaane]

Hmmm, thats what I thought but I had the code as you proposed originally and got the same error, so then tried to change the DB ref...

[NeoPa]

I'm coming to this without having read the full text of the thread so far, so forgive me if I'm off-base here.

I just thought I'd mention though, that the Excel functionality is available to you from within Access using :

Excel.WorksheetFunction.Percentile()

To enable this availability it is necessary to add a reference to Excel in your project. To do this got to the Access VBA Editor window (Alt-F11 from Access) then open the References window (Tools / References...) and check the item for the latest version of Excel you have installed.

If this is not helpful then please ignore.