topic Re: JMP 16.2 Equivalent to Excel PERCENTILERANK Function? in Discussions

JMP 16.2 Equivalent to Excel PERCENTILERANK Function?

WoHNY — Sat, 10 Jun 2023 20:50:13 GMT

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?

Re: JMP 16.2 Equivalent to Excel PERCENTILERANK Function?

David_Burnham — Thu, 28 Apr 2022 12:15:24 GMT

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

Re: JMP 16.2 Equivalent to Excel PERCENTILERANK Function?

Georg — Thu, 28 Apr 2022 13:51:08 GMT

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?

WoHNY — Thu, 28 Apr 2022 15:29:43 GMT

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?

WoHNY — Thu, 28 Apr 2022 16:05:05 GMT

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?

txnelson — Thu, 28 Apr 2022 19:58:46 GMT

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);

Re: JMP 16.2 Equivalent to Excel PERCENTILERANK Function?

ron_horne — Thu, 28 Apr 2022 21:30:58 GMT

Hi @WoHNY ,

while others have provided fantastic solutions let me offer a more basic one. try the following script it is fast enough for me on a mediocre computer.

Names Default To Here( 1 );

dt = New Table( "rank percentile - example",
	New Column( "X", Numeric, "Continuous",  Formula( Random Normal( 20, 5 ) ), Set Selected ),
	New Column( "rank percentile", Numeric, "Continuous", Format( "Percent", 12, 0 ), Formula( Col Rank( :X ) / Col Number( :X ) ) )
);
dt << Add Rows( 1000000000 );

Ron

Re: JMP 16.2 Equivalent to Excel PERCENTILERANK Function?

WoHNY — Fri, 29 Apr 2022 19:10:01 GMT

Ron:

Many thanks. It works great.

Greg

Re: JMP 16.2 Equivalent to Excel PERCENTILERANK Function?

WoHNY — Fri, 29 Apr 2022 19:12:19 GMT

Jim:

Thank you for coding example provided. As I am trying to improve my JSL skills these types of examples are great tutorials. Once again I appreciate your input.

Greg