Re: Calculating Percentiles

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi JMK

It seems that what you want then is not the percentile, but the percentile
rank (http://en.wikipedia.org/wiki/Percentile_rank)

The following function will calculate this for you. Below it is a rejigged
version of my Quantile function, a little better thought out than the hasty
version I posted the other night <g>

So for a one-off percentile rank you can say:

=QuantileRank( "YourTable", "FieldContainingScore", 80, 100 )

This will return, say, 78.5, meaning that 78.5% of the scores were below 80.

If you want to call it repeatedly on the same recordset, you could open the
recordset first, filtered and sorted, and then pass the recordset variable
and the AlreadySorted flag. Be sure to close the recordset again. For
example:

Dim rsPercentile as DAO.Recordset

Private Sub Report_Open(Cancel as Integer)
Set rsPercentile = CurrentDb.OpenRecordset( _
"Select [FieldContainingScore] from [YourTable] " _
& "where [FieldContainingScore] is not null " _
& "order by [FieldContainingScore]"
End Sub

Private Function GetPercentileRank( Score as Double ) as Double
GetPercentileRank = QuantileRank( _
rsPercentile, "[FieldContainingScore]", Score, 100, True )
End Function

Private Sub Report_Close()
If Not rsPercentile is Nothing Then
rsPercentile.Close
Set rsPercentile = Nothing
End If
End Sub

Then, in a textbox in your report's detail section you could have:
=GetPercentileRank( [FieldContainingScore] )

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

'============== Start Code =============================
Public Function QuantileRank( _
rs As Variant, _
FldName As String, _
V As Double, _
Optional Q As Integer = 100, _
Optional AlreadySorted As Boolean _
) As Double
'---------------------------------------------------------------------
' Ascertain the quantile rank for a given score from the numeric
' values in a recordset
' See: http://en.wikipedia.org/wiki/Percentile_rank
'
' © Graham Mandeno, 2008
' Use freely provided this comment block is preserved.
'
' ARGUMENTS:
' rs : either an already opened DAO.Recordset
' or a string to be passed to db.OpenRecordset
' FldName : the name of a numeric field in the recordset
' V : the score value to be ranked
' Q : the degree of the quantile to be used
' (optional, default=100 [percentile])
' AlreadySorted
' : if True then assume rs is a DAO.Recordset that is
' already sorted and filtered for Nulls
' (optional, default False)
'---------------------------------------------------------------------
Dim N As Long
Dim cFi As Long
Dim Fi As Long
Dim rsLocal As DAO.Recordset
Dim rsSorted As DAO.Recordset
Dim fCloseRS As Boolean
On Error GoTo ProcErr
Select Case VarType(rs)
Case vbString
Set rsLocal = CurrentDb.OpenRecordset(rs, dbOpenSnapshot)
fCloseRS = True
AlreadySorted = False ' just in case
Case vbObject
If TypeOf rs Is DAO.Recordset Then
If AlreadySorted Then
Set rsSorted = rs
Else
Set rsLocal = rs
End If
Else
Err.Raise 5, "Quantile", "Invalid recordset passed"
End If
Case Else
Err.Raise 5, "Quantile", "Invalid recordset passed"
End Select
If Not AlreadySorted Then
rsLocal.Filter = FldName & " is not Null"
rsLocal.Sort = FldName
Set rsSorted = rsLocal.OpenRecordset()
End If
With rsSorted
.MoveLast
N = .RecordCount
.FindFirst FldName & ">=" & V
If .NoMatch Then
cFi = N
Else
cFi = .AbsolutePosition
.FindFirst FldName & ">" & V
If .NoMatch Then
Fi = 0
Else
Fi = .AbsolutePosition - cFi
End If
End If
End With
' N is the total number of values in the sample
' cFi is the number of values less than V
' Fi is the number of values equal to V
QuantileRank = (cFi + 0.5 * Fi) / N * Q
ProcEnd:
On Error Resume Next
If Not rsSorted Is Nothing Then
If Not AlreadySorted Then rsSorted.Close
Set rsSorted = Nothing
End If
If Not rsLocal Is Nothing Then
If fCloseRS Then rsLocal.Close
Set rsLocal = Nothing
End If
Exit Function
ProcErr:
Dim msg As String
Select Case Err
Case 13
msg = "Invalid field type"
Case 3061, 3265
msg = "Unknown field"
Case Else
msg = Err.Description
End Select
MsgBox "Error calculating Quantile Rank" & vbCrLf & msg, _
vbExclamation, Err.Source
Resume ProcEnd
End Function

Public Function Quantile( _
rs As Variant, _
FldName As String, _
K As Double, _
Optional Q As Integer = 100, _
Optional AlreadySorted As Boolean, _
Optional WeightedAverage As Boolean = True _
) As Double
'---------------------------------------------------------------------
' Calculate a given quantile from the numeric values in a recordset
' See: http://en.wikipedia.org/wiki/Quantile
'
' © Graham Mandeno, 2008
' Use freely provided this comment block is preserved.
'
' ARGUMENTS:
' rs : either an already opened DAO.Recordset
' or a string to be passed to db.OpenRecordset
' FldName : the name of a numeric field in the recordset
' K : the number of the quantile to be ascertained
' Q : the degree of the quantile to be ascertained
' (optional, default=100 [percentile])
' AlreadySorted
' : if True then assume rs is a DAO.Recordset that is
' already sorted and filtered for Nulls
' (optional, default False)
' WeightedAverage
' : if False, take closer actual value
' if True then use the weighted average of the values
' [this is the method used by Excel]
' (optional, default True)
' For example:
' K = 3, Q = 4 gives upper quartile
' K = 3, Q = 10 gives 3rd decile
' K = 1, Q = 2 gives median
' K = 95, Q = 100 gives 95th percentile
'---------------------------------------------------------------------
Dim qVal As Double
Dim xRec As Double
Dim iRec As Long
Dim rsLocal As DAO.Recordset
Dim rsSorted As DAO.Recordset
Dim fCloseRS As Boolean
On Error GoTo ProcErr
If K < 0 Or K > Q Then
Err.Raise 5, "Quantile", _
"Quantile value must be in the range 0 to Q"
End If
Select Case VarType(rs)
Case vbString
Set rsLocal = CurrentDb.OpenRecordset(rs, dbOpenSnapshot)
fCloseRS = True
AlreadySorted = False ' just in case
Case vbObject
If TypeOf rs Is DAO.Recordset Then
If AlreadySorted Then
Set rsSorted = rs
Else
Set rsLocal = rs
End If
Else
Err.Raise 5, "Quantile", "Invalid recordset passed"
End If
Case Else
Err.Raise 5, "Quantile", "Invalid recordset passed"
End Select
If Not AlreadySorted Then
rsLocal.Filter = FldName & " is not Null"
rsLocal.Sort = FldName
Set rsSorted = rsLocal.OpenRecordset()
End If
With rsSorted
.MoveLast
.MoveFirst
xRec = (.RecordCount - 1) * K / Q + 1
'xRec now contains the record number we are looking for.
'(may not be a whole number)
iRec = Int(xRec)
xRec = xRec - iRec
If Not WeightedAverage And xRec >= 0.5 Then iRec = iRec + 1
'iRec now contains first record to look at and
'xRec contains interpolation to next record
.Move iRec - 1
qVal = .Fields(FldName)
If WeightedAverage And xRec > 0 Then
.MoveNext
qVal = ((.Fields(FldName) - qVal) * xRec) + qVal
End If
End With
Quantile = qVal
ProcEnd:
On Error Resume Next
If Not rsSorted Is Nothing Then
If Not AlreadySorted Then rsSorted.Close
Set rsSorted = Nothing
End If
If Not rsLocal Is Nothing Then
If fCloseRS Then rsLocal.Close
Set rsLocal = Nothing
End If
Exit Function
ProcErr:
Dim msg As String
Select Case Err
Case 13
msg = "Invalid field type"
Case 3061, 3265
msg = "Unknown field"
Case Else
msg = Err.Description
End Select
MsgBox "Error calculating Quantile" & vbCrLf & msg, _
vbExclamation, Err.Source
Resume ProcEnd
End Function
'============== Start Code =============================

"JMK" <JMK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D8AC938B-303A-4D4F-8EF7-4F66D267269D@xxxxxxxxxxxxxxxx
G'Day OssieMac and Graham,

Again, thank you for the responses.

To answer both your questions about the type of percentile I need to know
what percentile the value falls into, so the person achieved a mark say of
80% on an exam, and when compared to all others in the class they are in
the
79th percentile (if that makes any sense).

I'll be giving Grahams formula a go today, so will let you know how things
go (of course substituting those values where OssieMac pointed out).

Thanks again!

"Graham Mandeno" wrote:

Hi OssieMac

Thanks! And yes, you are quite right. It was after 11pm when I was
writing
the code and I changed a variable name at the last minute and didn't even
recompile <blush>

It was actually your link to the Wikipedia article that got me
interested.
I found this reference http://en.wikipedia.org/wiki/Quantile and based my
algorithm on that. The Interpolate argument (probably better named
"Weighted") switches between calculating the weighted average between the
two possible samples (the Excel algorithm) and simply taking the closer
of
the two.

It would also improve the performance of the function to pass it an
argument
to indicate that the recordset is already sorted and filtered for nulls.
Then it could be assigned directly to rsSorted.

Feel free to continue helping JMK with this. I'll monitor the thread
anyway. Looking back at JMK's posts, I wonder if he/she wants to
ascertain
which percentile a given value falls in, not ascertain the value at a
given
percentile. The approach would be somewhat different.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"OssieMac" <OssieMac@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BF0827E7-3233-4386-8245-BFFE5E50D9A8@xxxxxxxxxxxxxxxx
Very good Graham and it seems a shame being critical but I think you
might
need to substitute Q for V in both the following lines. Probably an
after
thought to change the variable but not to worry because now I don't
feel
so
bad about having done that sort of thing myself.

If V < 0 Or V > N Then

xRec = (.RecordCount - 1) * V / N + 1

I tested your function against the Excel Percentile function with 400
random
numbers and it works well.

Now to the OP. If you want to answer my previous questions then I can
put
Graham's function to good use (Unless of course Graham wants to
continue
with
his good work and finish it for you. If you do Graham then let me know
and
I'll bow out.)


--
Regards,

OssieMac


"Graham Mandeno" wrote:

Hi JMK

I haven't tested it thoroughly, but I think the following code will
return
the same result as the builtin function in Excel:

Public Function Quantile( _
rs As Variant, _
FldName As String, _
Q As Double, _
N As Integer, _
Optional Interpolate As Boolean = True _
) As Double
'-----------------------------------------------------------------
' Calculate a given quantile from the numeric values in a recordset
' Graham Mandeno, 2008
' Use freely provided this comment block is preserved.
'
' ARGUMENTS:
' rs : either an already opened DAO.Recordset
' or a string to be passed to db.OpenRecordset
' FldName : the name of a numeric field in the recordset
' Q : the degree of the quantile to be ascertained
' N : the number of the quantile to be ascertained
' Interpolate : if False, take closer actual value
' if True then interpolate between values
' (optional, default True)
' For example:
' Q = 75, N = 100 gives upper quartile
' Q = 3, N = 10 gives 3rd decile
' Q = 50, N = 100 gives median
'-----------------------------------------------------------------
Dim qVal As Double
Dim xRec As Double
Dim iRec As Long
Dim rsLocal As DAO.Recordset
Dim rsSorted As DAO.Recordset
Dim fCloseRS As Boolean
On Error GoTo ProcErr
If V < 0 Or V > N Then
Err.Raise 5, "Quantile", _
"Quantile value must be in the range 0 to N"
End If
Select Case VarType(rs)
Case vbString
Set rsLocal = CurrentDb.OpenRecordset(rs, dbOpenSnapshot)
fCloseRS = True
Case vbObject
If TypeOf rs Is DAO.Recordset Then
Set rsLocal = rs
Else
Err.Raise 5, "Quantile", "Invalid recordset passed"
End If
Case Else
Err.Raise 5, "Quantile", "Invalid recordset passed"
End Select
rsLocal.Filter = FldName & " is not Null"
rsLocal.Sort = FldName
Set rsSorted = rsLocal.OpenRecordset()
With rsSorted
.MoveLast
.MoveFirst
xRec = (.RecordCount - 1) * V / N + 1
'xRec now contains the record number we are looking for.
'(may not be a whole number)
iRec = Int(xRec)
xRec = xRec - iRec
If Not Interpolate And xRec >= 0.5 Then iRec = iRec + 1
'iRec now contains first record to look at and
'xRec contains interpolation to next record
.Move iRec - 1
qVal = .Fields(FldName)
If Interpolate And xRec > 0 Then
.MoveNext
qVal = ((.Fields(FldName) - qVal) * xRec) + qVal
End If
End With
Quantile = qVal
ProcEnd:
On Error Resume Next
If Not rsSorted Is Nothing Then
rsSorted.Close
Set rsSorted = Nothing
End If
If Not rsLocal Is Nothing Then
If fCloseRS Then rsLocal.Close
Set rsLocal = Nothing
End If
Exit Function
ProcErr:
MsgBox Err.Description, vbExclamation, "Error calculating Quantile"
Resume ProcEnd
End Function


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"JMK" <JMK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B10E7B01-93FB-4C1F-AF5C-6A33C3370D2E@xxxxxxxxxxxxxxxx
Hi All,

I've read at other sites on-line that to calculate Percentiles in
Access
you
need to write some VBA code or export the data to Excel.
While I know a little bit of how to write VBA code, I do not know
how
the
results are stored. And by little bit, I mean I know how to change a
buttons
name from one thing to another, or show a calendar, or not show a
calendar -
thats about it.

What I really want to do, is once the percentiles are calculated, I
would
like to export these results to a table for further analysis.

I was wondering if anyone could provide me with the details on how
to
code
this.

Thanks ahead of time!









.

Quantcast