Hard one: Percentrank ifs??

[Reboshua]

I have some fantasy basketball data that is organized in a very particular way (see below). That data is listed in the 10 left-hand-side columns (A-J) in the data below. On a second "calculations" sheet, I have the individual player names, as you can see in columns L, M, and N.

The data in column M represents an estimate of how many minutes I believe the player will play today. I want to know exactly how today's projected minutes compare to the player's actual minutes over the last "x" games, where x is defined by me. The number of "X" games will be defined by the value in column J. So, if I'm considering the last 10 games of data, I'd do some sort of conditional statement where the value in J is less than 11, etc.

For example, what is the percentrank of Aaron Gordon's projected minutes today, relative to the minutes he's actually played in his past 10 games? What formula would go in N3 to yield the 15% number that I calculated manually?

Thanks in advance for helping me figure this out.

Unknown

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​
1​	DATE2	PLAYER FULL NAME	POSITION	OWN TEAM	OPP TEAM	VENUE (R/H)	MIN	TOTGames	Game#	GamesBack		Sheet2 - Column A	ProjMins	PercentRank10
2​	11/24/2017	Aaron Brooks	PG	Minnesota	Miami	H	13.4​	5.0​	1.0​	5.0​		Aaron Brooks	18​
3​	12/31/2017	Aaron Brooks	PG	Minnesota	Indiana	R	21.0​	5.0​	2.0​	4.0​		Aaron Gordon	32​	15%​
4​	01/01/2018	Aaron Brooks	PG	Minnesota	LA Lakers	H	14.5​	5.0​	3.0​	3.0​		Abdel Nader	7​
5​	01/05/2018	Aaron Brooks	PG	Minnesota	Boston	R	15.2​	5.0​	4.0​	2.0​
6​	01/06/2018	Aaron Brooks	PG	Minnesota	New Orleans	H	15.2​	5.0​	5.0​	1.0​
7​	10/18/2017	Aaron Gordon	SF	Orlando	Miami	H	34.4​	37.0​	1.0​	37.0​
8​	10/24/2017	Aaron Gordon	SF	Orlando	Brooklyn	H	36.7​	37.0​	2.0​	36.0​
9​	10/27/2017	Aaron Gordon	SF	Orlando	San Antonio	H	25.9​	37.0​	3.0​	35.0​
10​	10/29/2017	Aaron Gordon	SF	Orlando	Charlotte	R	36.2​	37.0​	4.0​	34.0​
11​	10/30/2017	Aaron Gordon	SF	Orlando	New Orleans	R	18.3​	37.0​	5.0​	33.0​
12​	11/01/2017	Aaron Gordon	SF	Orlando	Memphis	R	34.4​	37.0​	6.0​	32.0​
13​	11/03/2017	Aaron Gordon	SF	Orlando	Chicago	H	29.3​	37.0​	7.0​	31.0​
14​	11/05/2017	Aaron Gordon	SF	Orlando	Boston	H	34.3​	37.0​	8.0​	30.0​
15​	11/08/2017	Aaron Gordon	SF	Orlando	New York	H	36.5​	37.0​	9.0​	29.0​
16​	11/10/2017	Aaron Gordon	SF	Orlando	Phoenix	R	30.7​	37.0​	10.0​	28.0​
17​	11/11/2017	Aaron Gordon	SF	Orlando	Denver	R	30.0​	37.0​	11.0​	27.0​
18​	11/13/2017	Aaron Gordon	SF	Orlando	Golden State	R	33.2​	37.0​	12.0​	26.0​
19​	11/15/2017	Aaron Gordon	SF	Orlando	Portland	R	31.6​	37.0​	13.0​	25.0​
20​	11/18/2017	Aaron Gordon	SF	Orlando	Utah	H	29.7​	37.0​	14.0​	24.0​
21​	11/20/2017	Aaron Gordon	SF	Orlando	Indiana	H	35.3​	37.0​	15.0​	23.0​
22​	11/22/2017	Aaron Gordon	SF	Orlando	Minnesota	R	36.7​	37.0​	16.0​	22.0​
23​	11/24/2017	Aaron Gordon	SF	Orlando	Boston	R	25.7​	37.0​	17.0​	21.0​
24​	11/25/2017	Aaron Gordon	SF	Orlando	Philadelphia	R	35.3​	37.0​	18.0​	20.0​
25​	11/27/2017	Aaron Gordon	SF	Orlando	Indiana	R	39.1​	37.0​	19.0​	19.0​
26​	11/29/2017	Aaron Gordon	SF	Orlando	Oklahoma City	H	44.0​	37.0​	20.0​	18.0​
27​	12/01/2017	Aaron Gordon	SF	Orlando	Golden State	H	36.5​	37.0​	21.0​	17.0​
28​	12/03/2017	Aaron Gordon	SF	Orlando	New York	R	38.6​	37.0​	22.0​	16.0​
29​	12/04/2017	Aaron Gordon	SF	Orlando	Charlotte	R	35.5​	37.0​	23.0​	15.0​
30​	12/06/2017	Aaron Gordon	SF	Orlando	Atlanta	H	45.2​	37.0​	24.0​	14.0​
31​	12/08/2017	Aaron Gordon	SF	Orlando	Denver	H	28.6​	37.0​	25.0​	13.0​
32​	12/15/2017	Aaron Gordon	SF	Orlando	Portland	H	24.8​	37.0​	26.0​	12.0​
33​	12/28/2017	Aaron Gordon	SF	Orlando	Detroit	H	30.0​	37.0​	27.0​	11.0​
34​	12/30/2017	Aaron Gordon	SF	Orlando	Miami	H	35.9​	37.0​	28.0​	10.0​
35​	01/01/2018	Aaron Gordon	SF	Orlando	Brooklyn	R	40.6​	37.0​	29.0​	9.0​
36​	01/03/2018	Aaron Gordon	SF	Orlando	Houston	H	32.9​	37.0​	30.0​	8.0​
37​	01/06/2018	Aaron Gordon	SF	Orlando	Cleveland	H	31.6​	37.0​	31.0​	7.0​
38​	01/09/2018	Aaron Gordon	SF	Orlando	Dallas	R	32.7​	37.0​	32.0​	6.0​
39​	01/10/2018	Aaron Gordon	SF	Orlando	Milwaukee	R	29.6​	37.0​	33.0​	5.0​
40​	01/12/2018	Aaron Gordon	SF	Orlando	Washington	R	38.4​	37.0​	34.0​	4.0​
41​	01/16/2018	Aaron Gordon	SF	Orlando	Minnesota	H	38.7​	37.0​	35.0​	3.0​
42​	01/18/2018	Aaron Gordon	SF	Orlando	Cleveland	R	35.8​	37.0​	36.0​	2.0​
43​	01/21/2018	Aaron Gordon	SF	Orlando	Boston	R	37.7​	37.0​	37.0​	1.0​
44​	10/18/2017	Abdel Nader	SF	Boston	Milwaukee	H	11.4​	8.0​	1.0​	8.0​
45​	11/01/2017	Abdel Nader	SF	Boston	Sacramento	H	13.1​	8.0​	2.0​	7.0​
46​	11/25/2017	Abdel Nader	SF	Boston	Indiana	R	13.3​	8.0​	3.0​	6.0​
47​	12/11/2017	Abdel Nader	SF	Boston	Chicago	R	16.2​	8.0​	4.0​	5.0​
48​	12/21/2017	Abdel Nader	SF	Boston	New York	R	11.6​	8.0​	5.0​	4.0​
49​	12/23/2017	Abdel Nader	SF	Boston	Chicago	H	14.5​	8.0​	6.0​	3.0​
50​	12/28/2017	Abdel Nader	SF	Boston	Houston	H	20.9​	8.0​	7.0​	2.0​
51​	01/18/2018	Abdel Nader	SF	Boston	Philadelphia	H	3.3​	8.0​	8.0​	1.0​

Sheet: Sheet1

 

[barry houdini]

Try this array formula in N3

=PERCENTRANK(IF(J$2:J$100<=10,IF(B$2:B$100=L3,G$2:G$100)),M3)

confirm with CTRL+SHIFT+ENTER