482707_en-US

liliconcom

JMP 16.2 Equivalent to Excel PERCENTILERANK Function?

Would like to find the percentile of a number within a data set. What percentage of values are above/below a certain number? Other than eyeballing a CDF or Prob Dist chart is there an existing path to get this number or is this a JSL task?

OtherRe: JMP 16.2 Equivalent to Excel PERCENTILERANK Function?

You can use the Quantiles statistic within the Tabulate platform (Analyze>Tabulate).

Re: JMP 16.2 Equivalent to Excel PERCENTILERANK Function?

You can do this by a formula or JSL snippet as you like, see screenshot and script below.

The first part of the formula generates the table for values and percentiles, the second looks up the percentile for the current row.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\Big Class.jmp" );

dt << New Column( "PercentileRank",
 Formula(
  cdf_arr = CDF( :weight << get values );
  cdf_arr[2][Loc( cdf_arr[1], :weight )];
 )
);

Re: JMP 16.2 Equivalent to Excel PERCENTILERANK Function?

David:

Thank you for the response. I was familiar with the Quantile statistic. With this I am specifying the Quantile and JMP is generating the value that matches that Quantile. I want to specify the value and generate the Quantile. My initial explanation was insufficient.

Greg

Re: JMP 16.2 Equivalent to Excel PERCENTILERANK Function?

Georg:

Thank you for your input. I adjusted the JSL to match my table and column name but I am guessing this is a very processor intensive calculation. It has been over 20 minutes and the wheels are still turning. I have about 240K rows of data to deal with. It looks like I'll stick with the Excel method I set up until I can find time to investigate this issue in more depth. Thanks again.

Greg

Re: JMP 16.2 Equivalent to Excel PERCENTILERANK Function?

Here is a JMP Function using the exact methodology used in the Excel function, PERCENTILERANK as described in the MicroSoft Excel help page

https://support.microsoft.com/en-us/office/percentrank-function-f1b5836c-9619-4847-9fc9-080ec9024442 

Below I have provided the example table from the help page, along with the JMP function I created

Names Default To Here( 1 );

New Table( "Excel Example",
 Add Rows( 10 ),
 New Column( "Data",
  Numeric,
  "Nominal",
  Format( "Best", 12 ),
  Set Values( [13, 12, 11, 8, 4, 3, 2, 1, 1, 1] )
 )
);

dt = Current Data Table();

percentRank = Function( {col, value},{default local},
 mydt = Current Data Table();
 theValue = .;
 theRows = mydt << get rows where( As Column( mydt, col ) == value );
 If( N Rows( theRows ) > 0,
  gt = Col Number( If( As Column( mydt, col )[Row()] > value, Column( mydt, col )[Row()], . ) );
  lt = Col Number(
   If( As Column( mydt, col )[Row()] < value & Is Missing( As Column( mydt, col )[Row()] ) == 0,
    As Column( mydt, col )[Row()],
    .
   )
  );
  theValue = lt / (lt + gt);
 ,
  lower = upper = .;
  lower = Col Max( If( As Column( mydt, col ) < value & Is Missing( As Column( mydt, col )[Row()] ) == 0, Column( mydt, col )[Row()], . ) );
  upper = Col Min( If( As Column( mydt, col ) > value, Column( mydt, col )[Row()], . ) );
  
  // get the percentileRank for upper and lower
  upperPR = percentRank(col,upper);
  lowerPR = percentRank(col,lower);
  intervalPCT=(value-lower)/(upper-lower);
  theValue = lowerPR+(intervalPCT*(upperPR-lowerPR));

 );
 Return( theValue );
);

x = percentRank( "data", 8 );  // a data point found in the data
show("found in table",x);
x = percentRank( "data", 5 );  // a data point not found in the data
show("Not found in table",x);