Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Using Excel's percentile function

Posted on 2004-08-13
Medium Priority
2,753 Views
I have a function that allows me to call Excel's percentile function. I can use it in queries and in modules as well. It's set up like this: Percentile(Array,%).

I can use it easily in Modules by building arrays of values I want certain percentile ranks for, but I'm confused as to how it might be used in Access's query builder. What would go in as the Array argument if I wanted to calculate a certain percentile rank for a collection of records?

I doubt I've provided enough information to be informative, but I'm not sure what other information to provide.

Thanks for the help.
0
Question by:Feyo
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2
• +1

LVL 17

Expert Comment

ID: 11795764
So the real question, then is how do you pass the array to the Percentile function.  Can you show us how you build the array?

Walt
0

LVL 4

Author Comment

ID: 11795930
I haven't finished with this code, but here it is:

Sub percentile()

Dim intArray() As Double
Dim i As Integer
Dim strSQL As String
Dim JobNum As Integer
Dim P25 As Integer
Dim P50 As Integer
Dim P75 As Integer
Set cnn = CurrentProject.Connection

For JobNum = 1 To 7

strSQL = "SELECT tblJobInformation.SurveyTitleId, tblJobInformation.MinPay " & _
"FROM tblJobInformation " & _
"WHERE (((tblJobInformation.SurveyTitleId)= " & JobNum & ") and (tbljobinformation.minpay is not null)"

ReDim intArray(rst.RecordCount - 1)

For i = 1 To rst.RecordCount
intArray(i - 1) = rst()
rst.MoveNext
Next i

P25 = Excel.Application.WorksheetFunction.percentile(intArray, 0.25)
P50 = Excel.Application.WorksheetFunction.percentile(intArray, 0.5)
P75 = Excel.Application.WorksheetFunction.percentile(intArray, 0.75)

Next JobNum

End Sub

What I have to do next is write the different percentiles to a table so that I have the 25th, 50th, and 75 percentiles for minpay. I just thought if I could use this function in the query builder that it would be easier.

Thanks

0

LVL 17

Assisted Solution

walterecook earned 400 total points
ID: 11796100
To return a number to a query, you'll need to convert it to a Function and not a sub.  Then return a value to it.
if you put something like this:
somevalue: percentile()

It'll call that function and display the return.  BE CAREFUL because that will make the code run for every line in your recordset.
You could have that in it's own query, might be one idea.

Walt
0

LVL 44

Expert Comment

ID: 11796472
In:

For i = 1 To rst.RecordCount
intArray(i - 1) = rst()
rst.MoveNext
Next i

I believe rst() should be rst(i)
0

LVL 4

Author Comment

ID: 11797509
GRayL-Yeah I screwed that up. Thanks.

Walt - I did write a function called Percentile. I can call that function from the query builder, but I don't know how to fill the array argument for the function in the query builder.

Percentile(Array,.25) - what would serve as the Array argument in the query builder? Can I use an SQL statement?

Thanks
0

LVL 34

Expert Comment

ID: 11798035
damn, i have an exampple of this at work!  Ill see if i can find it
0

LVL 34

Accepted Solution

flavo earned 600 total points
ID: 11798040
found it, its a little old i think, but i tmay get you going at least

>> argument for the function in the query builder.

I dont think you can do this in Access, if you can i'd love to know how to, saves my temp table method.

heres my code: - bulit for A97 so it uses DAO.

you can an excel function like this.  Youll need to refrence the Excel library (In VBA window, "Tools", "Refrences", then select "Microsoft Excel....." - same goes for Microsoft DAO Object Library 3.??

Function myPercentile(myArray, myValue As Double) As Double
'use excel percentile funciton mayhems
myPercentile = Excel.WorksheetFunction.Percentile(myArray, myValue)
End Function

' ********** And here's how i use it

Function PercentileInAccess() As Double
'dont forget to refrence Excel and DAO
Dim intArray() As Double
Dim i As Integer
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database
'open connection
Set dbs = CurrentDb

strSQL = "SELECT tblValues.Value FROM tblValues;" 'select all values in "fake" table to test
'set the recordset we want (all values)
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
'need to populate the record set so we can get the right count
rst.MoveFirst
rst.MoveLast
'make sure we are at the first record
rst.MoveFirst
'initalise the array with the number of entries in the recordset
ReDim intArray(rst.RecordCount - 1)
'looping mayhems - add the record data into the array
For i = 1 To rst.RecordCount
intArray(i - 1) = rst(0)
rst.MoveNext
Next

'MsgBox myPercentile(intArray, 0.85) 'change 0.85 to relfect other percentiles you wish to calculate

End Function

Dave
0

LVL 4

Author Comment

ID: 11821885
Well, I opted to do things another way. I fill some tables with the percentiles I want, then use them from there. It's simple and straightforward. I do appreciate your help, so I'm gonna divide points between those who responded with solutions. Thanks.

If you are interested, here's the code I'm using

Sub percentile()

Dim intArray() As Double
Dim i As Integer
Dim strSQL As String
Dim JobNum As Integer
Dim P25 As Integer
Dim P50 As Integer
Dim P75 As Integer
Set cnn = CurrentProject.Connection

For JobNum = 1 To 7

strSQL = "SELECT tblJobInformation.SurveyTitleId, tblJobInformation.MinPay " & _
"FROM tblJobInformation " & _
"WHERE (((tblJobInformation.SurveyTitleId)= " & JobNum & ") and (tbljobinformation.minpay is not null))"

Debug.Print rst.RecordCount
ReDim intArray(rst.RecordCount - 1)

For i = 1 To rst.RecordCount
intArray(i - 1) = rst(1)
rst.MoveNext
Next i

rst.Close

rst2.Fields(0) = JobNum
rst2.Fields(1) = Excel.Application.WorksheetFunction.percentile(intArray, 0.25)
rst2.Fields(2) = Excel.Application.WorksheetFunction.percentile(intArray, 0.5)
rst2.Fields(3) = Excel.Application.WorksheetFunction.percentile(intArray, 0.75)
rst2.Update

rst2.Close

Next JobNum

For JobNum = 1 To 7

strSQL = "SELECT qryReportData.SurveyTitleId, qryReportData.MidPay " & _
"FROM qryReportData " & _
"WHERE (((qryReportData.SurveyTitleId)= " & JobNum & ") and (qryReportData.midpay is not null))"

ReDim intArray(rst.RecordCount - 1)

For i = 1 To rst.RecordCount
intArray(i - 1) = rst(1)
rst.MoveNext
Next i

rst.Close

rst2.Fields(0) = JobNum
rst2.Fields(1) = Excel.Application.WorksheetFunction.percentile(intArray, 0.25)
rst2.Fields(2) = Excel.Application.WorksheetFunction.percentile(intArray, 0.5)
rst2.Fields(3) = Excel.Application.WorksheetFunction.percentile(intArray, 0.75)
rst2.Update

rst2.Close

Next JobNum

For JobNum = 1 To 7

strSQL = "SELECT tblJobInformation.SurveyTitleId, tblJobInformation.MaxPay " & _
"FROM tblJobInformation " & _
"WHERE (((tblJobInformation.SurveyTitleId)= " & JobNum & ") and (tbljobinformation.Maxpay is not null))"

Debug.Print rst.RecordCount
ReDim intArray(rst.RecordCount - 1)

For i = 1 To rst.RecordCount
intArray(i - 1) = rst(1)
rst.MoveNext
Next i

rst.Close

rst2.Fields(0) = JobNum
rst2.Fields(1) = Excel.Application.WorksheetFunction.percentile(intArray, 0.25)
rst2.Fields(2) = Excel.Application.WorksheetFunction.percentile(intArray, 0.5)
rst2.Fields(3) = Excel.Application.WorksheetFunction.percentile(intArray, 0.75)
rst2.Update

rst2.Close

Next JobNum
End Sub
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
###### Suggested Courses
Course of the Month5 days, 19 hours left to enroll