528892_en-US

liliconcom

What is the function to calculate the column percentile of a specific number?

Hi,

As an example, I have a data table with column "X" with values "1,2,3,....13, 14, 15". 

I understand it is easy to calculate the 20% quantile using function: Col Quantile(:X, 0.2) = 3.2.

Is there a function to calculate the percentile of a number in this column "X", for example 3?

If so, is there a way to calculate it in the "Table Summary" for multiple columns?

Thank you!

AnalyticsRe: What is the function to calculate the column percentile of a specific number?

Here is the entry from the Scripting Index for the Normal Distribution function which returns the probability for a given value, with a given mu and sigma.

So if I am understanding your question correctly, you would specify

p=normal distribution(3, col mean(:x), col stddev(:x) );

Re: What is the function to calculate the column percentile of a specific number?

Hi, txnelson:

Thanks for your reply!

I am not a statistician, but wonder if there is way to calculate it for data which is not limited to normal distribution.

What I was really looking for is a function doing the reverse calculation of "Col Quantile (:X, 0.2)--> 3.2", meaning f(:X, 3.2)--> 20%. Or mathematically N rows ( where (:X<0.32) ) / N rows(:X).     

Re: What is the function to calculate the column percentile of a specific number?

Maybe

Col Rank(:X) / Col Number(:X)

or maybe you could use CDF()

Re: What is the function to calculate the column percentile of a specific number?

Expanding on @jthi idea, here is a function that works.  Remember, the value returned is partially made up from a linear interpolation between the adjacent cumulative probabilities.

Names Default To Here( 1 );
dt = 
// Open Data Table: big class.jmp
// → Data Table( "big class" )
Open( "$SAMPLE_DATA/big class.jmp" );

quantile Distribution = Function( {col, value},
 {defaultlocal}, 
 {quant, cumprob} = CDF( Column( col ) << get values );
 result = Interpolate( value, quant, cumprob );
 Return( result );
);

res = quantile distribution( "height", 53 );