75 percentile value

  • Thread starter Thread starter Charles Deng
  • Start date Start date
C

Charles Deng

Hi All:

Is there any formula I can use to calculate value? I know
can calculate average, sum, and total count. There should
be a formula to calculate 75 percentile value. Am I right?
Thanks for any advice.


Charles
 
Charles,

To my knowledge there is no formula to calculate the 75th
percentile. Could you just multiply the number by .75?

M. Wilson
 
Hi Wilson:

I asked this question to Excel News group and got a
formula like

=Percentile (A1:A1000, 0.75)

If in code:

? application.Percentile(Range("A1:A1000"), 0.75)

to show use of an array, we can pick up the values from
A1:A1000, but the array could be populated other ways.
varr = Range("a1:a1000").value
?application.Percentile (varr, 0.75)
292.7

Since this formula and code for this calculation can be
found in excel, I guess they should be in Access. Just my
guess.

Charles
 
Charles said:
Is there any formula I can use to calculate value? I know
can calculate average, sum, and total count. There should
be a formula to calculate 75 percentile value. Am I right?
Click to expand...


No such aggregate (or built-in function) function in Access.

You can use an SQL statment to find the value. This should
be close to what you want:

SELECT TOP 1 field
FROM [SELECT TOP 25 PERCENT field
FROM table
ORDER BY field DESC]. AS T
ORDER BY field

If you also want a function to encapsulate it then just Open
a recordset on the query and return the value.
 
Hi Charles

The mathematical formula that you use will only work if the data is a normal
distribution. Very few (if any) data sets form a normal distribution. If
you use this formula you will get at best an estimate for the 75th
percentile, at worst an incorect value. Personally I would use the SQL
example given as it will always work.

Ian

[email protected] said:
Hi Marsh:

I very appreciate your SQL statement but I do not know how
to use SQL language. Now I found the percentile formula
but I am not sure if this formula works in Access. The
provider said:"You can calculate a percentile using a
standardized statistical Z-table value using Access' built
Avg and StDev functions to calculate the mean and the
standard deviation.

The formula is:

Percentile = Mean (Avg) Value + (Standard Deviation* Z-
table value)

For common percentiles, the z-table values are:

75th percentile = .674
25th percentile = -.674

Do you think this formula should work? Furthermore, I am
not sure if this formula works after I entered it into a
control box. I know the syntax for Standard Deviation is

DStDev(expression, domain, [criteria])

But I am afraid that if it is too long when we use this
code in the above formula.

Thanks for your response and for your any suggestions and
instructions.

Charles


-----Original Message-----
Charles said:
Is there any formula I can use to calculate value? I know
can calculate average, sum, and total count. There should
be a formula to calculate 75 percentile value. Am I
Click to expand...
right?


No such aggregate (or built-in function) function in Access.

You can use an SQL statment to find the value. This should
be close to what you want:

SELECT TOP 1 field
FROM [SELECT TOP 25 PERCENT field
FROM table
ORDER BY field DESC]. AS T
ORDER BY field

If you also want a function to encapsulate it then just Open
a recordset on the query and return the value.
Click to expand...
Click to expand...
 
Back
Top