DPercentile

[jimmylui]

I am looking for a VBA function that can calculate the percentile in a database with certain criteria. The ideal one will be replicating "DAverage" but using Percentile.

Can anyone help me?

[Bob Phillips]

[vba]

Public Function DPercentile(ByVal Database As Range, ByVal Criteria As Range)
Dim col As Long

On Error Resume Next
col = Application.Match(Criteria.Cells(1, 1), Database.Rows(1), 0)
On Error GoTo 0
If col = 0 Then

DPercentile = CVErr(xlErrRef)
Else

DPercentile = Application.Percentile(Database.Columns(col), Criteria.Cells(2, 1))
End If
End Function
[/vba]

[jimmylui]

Thanks xld. However, I didn't manage to get it work. Where can I input the percentile that I want to calculate?

I would expect some like DPercentile(DataBase, Field, Criteria, Percentile).

Basically, I would like to do the following (This is only a simplified version, my database is much bigger)

Column Company Revenue Industry Sales
Company A 1,000 Services 230
Company B 1,500 Financial 250
Company C 3,000 Services 600
Company D 6,500 Utility 1,000
Company E 4,500 Financial 500
Company F 5,000 Financial 450
Company F 5,000 Financial 450
I have the following set of criteria

Revenue Industry
AND(B2>=1000,B2<=5000) Financial

[jimmylui]

Thanks xld. However, I didn't manage to get it work. Where can I input the percentile that I want to calculate?

I would expect some like DPercentile(DataBase, Field, Criteria, Percentile).

Basically, I would like to do the following (This is only a simplified version, my database is much bigger)

Column Company Revenue Industry Sales
Company A 1,000 Services 230
Company B 1,500 Financial 250
Company C 3,000 Services 600
Company D 6,500 Utility 1,000
Company E 4,500 Financial 500
Company F 5,000 Financial 450
Company F 5,100 Financial 650
Company G 5,200 Financial 750
Company H 5,300 Financial 350
Company I 5,400 Financial 560
Company J 5,000 Financial 450

I have the following set of criteria

Revenue Industry
AND(B2>=1000,B2<=5000) Financial

And would like to get 10th 25th 50th 75th and 90th percentile of "sales" based on the the above criteria.

You have developed a code in this thread before
/forum/showthread.php?t=13635. However, I could not get it work

[Bob Phillips]

Assuming the database is in A1:C12, you would add

Industry Sales and n% in say E1 and E2, then use

=dpercentile(A1:C12,E1:E2)

[jimmylui]

Many thanks.

I still don't understand how to write the criteria. In my example, it is a 2 x 3 (E1:G2) criteria

So these are what I put in the cells.
Cell E1: Industry
Cell E2: Financial
Cell F1: Sales
Cell F2: >=1000
Cell G1: "Percentile"
Cell G2: 25%

However, it doesn't work. Do I miss anything? Do I need to name the cell "percentile" in G1?

[Bob Phillips]

I only allowed the percentile criteria, why are you wanting all 3?

[jimmylui]

Thanks xld. I would like to calculate the precentile based on the criteria. In my example, I want them to calculate the percentiles for the comapny B, E, F and J (based on my percentile)

[Bob Phillips]

Jimmy,

Just to make sure that I understand what you are saying.

You want specify a list of customers and a percentile, and calculate that percentile for just those companies numbers?

If this is so, please confirm, I think I would need to develop that previous DPercentile UDF I gave. Should be doable, but let me know.

[jimmylui]

Let me provide you a sample workbook with some data

Basically, I have used array to calculate the 25th, 50th and 75th Percentiles and use DAVERAGE and DCOUNT to calculate the rest.

However, the arrays will get very complicated when there are more criteria and more columns to calculate. So I would like to see if there is a VBA code to calculate the Percentile by selecting "Database", "Field" and "Criteria" and the Percentile I am calculating and will give out the same results as array does

Basically, would expect something like this
DPercentile(DataBase,Field,Criteria,Percentile)