Calculating Percentile Values

[sirskeezy]

Hi,

I've been searching up how to calculate percentile values all day and I still do not seem to be able to get the values that I want. Can someone please help me?

Heres the overview of my situation:

I have a table called Raw Data and it holds fields including 'operating costs' and 'property size'

The percentile value that I want is the 95th and 5th percentile value of 'operating costs/property size'

First step I did was to create a query called 'STEP 1:FACTOR VARIABLES' that calculates 'operating costs/property size'

Second step I made another query that would use my query 'STEP 1:FACTOR VARIABLES' and tried calculating my 5th percentile with the sql code
SELECT Max([Operating Costs/Property Size]) AS [5th percentile Operating Costs/Property]
FROM (SELECT TOP 95 PERCENT [Operating Costs/Property Size] FROM [STEP 1: FACTOR VARIABLES])
ORDER BY Max([Operating Costs/Property Size]);


I thought that this would get the value that I need but all it does is grab the largest 95% of values and then get the max of it, which is basically just the max value itself....

Would I be able to get what I want using SQL or do I have to do some VBA coding?

 

[sirskeezy]

I changed all my names to have no spacing and no special characters. My SQL is now working but is there a way to exclude null values in my SQL code? Right now I have my SQL looking like

SELECT Max(STEP2_FactorVariables.MaintenanceCostsPerSF) AS MaxOfMaintenanceCostsPerSF, FROM STEP2_FactorVariables
WHERE (((STEP2_FactorVariables.OperatingCostsPerSF) In (SELECT TOP 5 PERCENT [OperatingCostsPerSF] FROM [STEP2_FactorVariables] ORDER BY [OperatingCostsPerSF] ASC));

 

[MajP]

That is not a correct formulation for a percentile. If N is even you will have to weight n and n+1. I have shown two correct formulations.

 

[MajP]

Any chance you can post a table with data. It could be an excel file or text file. Does not have to be a database. Just post the necessary fields. At a minimum operating costs and property size.

 

[sirskeezy]

I think I gave up on the SQL route..

I am trying to calculate percentile values by using your VBA code:

Public Function DPercentileExcel(expr As String, domain As String, Percentile As Double) As Variant
Dim strSQL As String
Dim N As Integer
Dim nSubk As Double
Dim vSubk As Variant
Dim vSubkPlus1 As Variant
Dim rs As DAO.Recordset

strSQL = "SELECT " & expr & " FROM " & domain
strSQL = strSQL & " WHERE NOT " & expr & " IS NULL ORDER BY " & expr
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If Not (rs.EOF And rs.BOF) Then
rs.MoveLast
rs.MoveFirst
Else
Exit Function
End If

N = rs.RecordCount

nSubk = Percentile / 100 * (N - 1) + 1
'using NIST nSubK = Percentile / 100 * (N+1)

If nSubk = 1 Then
DPercentileExcel = rs.Fields(expr)
ElseIf nSubk = N Then
rs.MoveLast
DPercentileExcel = rs.Fields(expr)
Else
rs.AbsolutePosition = nSubk - 1 '0 based
vSubk = rs.Fields(expr)
rs.AbsolutePosition = nSubk '0 based
vSubkPlus1 = rs.Fields(expr)
'Debug.Print nSubk
'Debug.Print vSubk
'Debug.Print vSubkPlus1
DPercentileExcel = vSubk + (nSubk - Int(nSubk)) * (vSubkPlus1 - vSubk)
End If
End Function

Public Sub testPercentileExcel()
Debug.Print DPercentileExcel("operatingcostspersf", "step1_operatingcostspersf", 0)
End Sub


When I try to use this function in my query, it says that DPercentileExcel is an undefined function in the expression...



My SQL looks like this:



SELECT Percentiles.percentile, DPercentileExcel("operatingcostsperSF","step1_operatingcostspersf",[percentile]) AS percentileoperatingcostspersf
FROM Percentiles;

O and btw when I try to save the module name from the 'Module 5' that it automatically gave to a new name, it says file not found. Does this mean anything??

 

[MajP]

Is the function in a standard module? It cannot be in a form's for report's module.