Calculating Percentiles in Access

[maliotta]

Hi,



I need some help.

I have two columns.

Column A (Codes) has all of the products I'm analyzing.
There can be anywhere from 100s to 1000s of lines (rows) for each specific product in Column A.
There are 100s of different products. So there's like 300,000 lines of data.

Column B has all of the prices for each product in Column A. (Prices can vary for the same product.)
I'm trying to find the price differences using Percentiles (25% percentile price, 75% percentile price, etc.).

In Excel, I tried to get the output in Column C for the 25th percentile using the following formula:

=PERCENTILE(IF($B$2:$B414489=$B2,$C$2:$C$414489,"" ),0.25)

It won't work because it's too much data.


I have these two columns in Microsoft Access in the same order.
How can calculate the percentiles for the 25th, 50th, and 75th in 3 columns to the right?




Please help me!
I can't figure this out!


Thanks.


P.S. I'm new to Access. I need all steps.

[orange]

Can you post some data?

From FMS site:

How Percentiles are Calculated

Percentiles are calculated by sorting the data from smallest to largest. The middle value is the median (50^th percentile). Dividing into 4 groups gives quartiles (25^th, 50^th, and 75^th percentiles), and 10 groups give deciles. This is the formula used to determine which record is selected for any percentile value:

where N is the number of items, and Percentile is a number between 0 and 100. For instance, for a sample size of 13, the quartile records for the 25^th, 50^th, and 75^th percentiles are 4, 7, 11. By definition, the 0^th and 100^th percentiles are the lowest and highest values.
If the percentile cut-offs do not coincide with a particular data point, a linear interpolation of the two closest points is used. If a weighting field is assigned, values are considered continuous and no interpolation is performed.


If you put your values into ascending sequence, you should be able to get the X percentile from

SELECT Max([Id]) AS MyPercentile
FROM (SELECT TOP 93 PERCENT [id] FROM tblPercentileTest ORDER BY [id])  AS [malias];

Substitute the percentile you want for the 93 in my example. Adjust your field names as needed.

[maliotta]

Thanks for the reply.

The issue is that the Code must differentiate between products in the 1st Column. The 2nd Column contain the prices associated with the products in the 1st Column. For instance:

Column 1--Column 2
A---------$1.01
A---------$1.43
A---------$1.30
A---------$1.50
B--------$300.43
B--------$295.43
B--------$287.35
B--------$305.78
C---------$89.23
C---------$85.68
C---------$86.43
C---------$91.45
C---------$80.56


Above is an extremely simplified version of what I'm dealing with.
I have 400,000 lines for Column 1 and Column 2. There are 100 of different products with thousands of associated prices in Column 2.

So, I need the code to understand this and pick it up. My array formula that I posted does this. Excel just is not strong enough to calculate percentiles in this way -- even with only 5,000 lines of data with 50 products and associated prices.


Can your Code do this?

[orange]

Can you show an example of what you expect as output using say one of the products in your sample?

[June7]

Might find these threads of interest

https://www.accessforums.net/access/...ess-40274.html

https://www.accessforums.net/access/...ile-23607.html