Eliminate duplicate values for 2nd and 3rd highest value

[gtd526]

Hello,
I'm using MAX for the maximum value in a range using the following formula (array) using a Named Range (NBA_Best_Percent), which is working correctly:

=OFFSET(INDIRECT(INDEX(NBA_Best_Percent,MATCH(MAX(N(INDIRECT(NBA_Best_Percent))),N(INDIRECT(NBA_Best_Percent)),0))),-4,-2)

If there are duplicate values in the Range for 2nd and 3rd highest values it will repeat, showing the same value for both 2nd and 3rd highest values.
I'm using this formula for 2nd and 3rd highest (A44 & A47):

=AGGREGATE(14,6,$M$43:$AH$79/(MOD(ROW($M$43:$AH$79)-ROW(M43),12)=0)/(MOD(COLUMN($M$43:$AH$79)-COLUMN(M43),3)=0),2)

=AGGREGATE(14,6,$M$43:$AH$79/(MOD(ROW($M$43:$AH$79)-ROW(M43),12)=0)/(MOD(COLUMN($M$43:$AH$79)-COLUMN(M43),3)=0),3)

How can I eliminate duplicate values and go to the next highest value, re: 2nd and 3rd highest value?
Here is the current results:

NBA.xlsm
	A	B	C
39	Max %
40	M,N>0,Q,R>0,AL:AM>=60%		$T$75
41	63.8%
42	2nd Max %
43	Q, R, S, T>0, AL:AM>=60%		$K$39
44	60.9%
45	3rd Max %
46	Q, R, S, T>0, AL:AM>=60%		$K$39
47	60.9%
Favs

Cell Formulas
Range	Formula
A40	A40	=OFFSET(INDIRECT(INDEX(NBA_Best_Percent,MATCH(MAX(N(INDIRECT(NBA_Best_Percent))),N(INDIRECT(NBA_Best_Percent)),0))),-4,-2)
C40,C46,C43	C40	=ADDRESS(AGGREGATE(15,6,ROW($K$39:$AG$99)/($K$39:$AG$99=A40),1),AGGREGATE(15,6,COLUMN($K$39:$AG$99)/($K$39:$AG$99=A40),1))
A41	A41	=INDIRECT(INDEX(NBA_Best_Percent,MATCH(MAX(N(INDIRECT(NBA_Best_Percent))),N(INDIRECT(NBA_Best_Percent)),0)))
A43	A43	=OFFSET(INDIRECT(ADDRESS(AGGREGATE(15,6,ROW($M$43:$AH$80)/($M$43:$AH$80=$A$44),1),AGGREGATE(15,6,COLUMN($M$43:$AH$80)/($M$43:$AH$80=$A$44),1))),-4,-2)
A44	A44	=AGGREGATE(14,6,$M$43:$AH$79/(MOD(ROW($M$43:$AH$79)-ROW(M43),12)=0)/(MOD(COLUMN($M$43:$AH$79)-COLUMN(M43),3)=0),2)
A46	A46	=OFFSET(INDIRECT(ADDRESS(AGGREGATE(15,6,ROW($M$43:$AH$80)/($M$43:$AH$80=$A$47),1),AGGREGATE(15,6,COLUMN($M$43:$AH$80)/($M$43:$AH$80=$A$47),1))),-4,-2)
A47	A47	=AGGREGATE(14,6,$M$43:$AH$79/(MOD(ROW($M$43:$AH$79)-ROW(M43),12)=0)/(MOD(COLUMN($M$43:$AH$79)-COLUMN(M43),3)=0),3)
Press CTRL+SHIFT+ENTER to enter array formulas.

Its a large Range: $M$43:$AH$79
Thank you

 

[Sam_D_Ben]

Hey,

Formula for 2nd and 3rd highest (A44 & A47) could be,

AGGREGATE(14,6,1/(COUNTIF($M$43:$AH$79,$M$43:$AH$79)=1)*($M$43:$AH$79)/(MOD(ROW($M$43:$AH$79)-ROW(M43),12)=0)/(MOD(COLUMN($M$43:$AH$79)-COLUMN(M43),3)=0)),2)

Please check

 

[gtd526]

Hey,

Formula for 2nd and 3rd highest (A44 & A47) could be,

AGGREGATE(14,6,1/(COUNTIF($M$43:$AH$79,$M$43:$AH$79)=1)*($M$43:$AH$79)/(MOD(ROW($M$43:$AH$79)-ROW(M43),12)=0)/(MOD(COLUMN($M$43:$AH$79)-COLUMN(M43),3)=0)),2)

Please check

If I plug your formula to 2nd & 3rd highest, it skips the 2nd highest and shows the 3rd highest. And 2nd & 3rd are the same (3rd highest).
I had to remove one ")" from the end, else error.
Here are the results:

NBA.xlsm
	I
42	63.8%
43	60.3%
44	60.3%
Favs

Cell Formulas
Range	Formula
I42	I42	=INDIRECT(INDEX(NBA_Best_Percent,MATCH(MAX(N(INDIRECT(NBA_Best_Percent))),N(INDIRECT(NBA_Best_Percent)),0)))
I43	I43	=AGGREGATE(14,6,1/(COUNTIF($M$43:$AH$79,$M$43:$AH$79)=1)*($M$43:$AH$79)/(MOD(ROW($M$43:$AH$79)-ROW(M43),12)=0)/(MOD(COLUMN($M$43:$AH$79)-COLUMN(M43),3)=0),2)
I44	I44	=AGGREGATE(14,6,1/(COUNTIF($M$43:$AH$79,$M$43:$AH$79)=1)*($M$43:$AH$79)/(MOD(ROW($M$43:$AH$79)-ROW(M43),12)=0)/(MOD(COLUMN($M$43:$AH$79)-COLUMN(M43),3)=0),2)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[gtd526]

Hey,

Formula for 2nd and 3rd highest (A44 & A47) could be,

AGGREGATE(14,6,1/(COUNTIF($M$43:$AH$79,$M$43:$AH$79)=1)*($M$43:$AH$79)/(MOD(ROW($M$43:$AH$79)-ROW(M43),12)=0)/(MOD(COLUMN($M$43:$AH$79)-COLUMN(M43),3)=0)),2)

Please check

I added your formula only to the 3rd highest, and its not repeating or skipping but showing the 3rd highest. 59.1% would normally repeat ie 2nd and 3rd.

NBA.xlsm
	I
42	61.7%
43	59.1%
44	58.7%
Favs

Cell Formulas
Range	Formula
I42	I42	=INDIRECT(INDEX(NBA_Best_Percent,MATCH(MAX(N(INDIRECT(NBA_Best_Percent))),N(INDIRECT(NBA_Best_Percent)),0)))
I43	I43	=AGGREGATE(14,6,$M$43:$AH$79/(MOD(ROW($M$43:$AH$79)-ROW(M43),12)=0)/(MOD(COLUMN($M$43:$AH$79)-COLUMN(M43),3)=0),2)
I44	I44	=AGGREGATE(14,6,1/(COUNTIF($M$43:$AH$79,$M$43:$AH$79)=1)*($M$43:$AH$79)/(MOD(ROW($M$43:$AH$79)-ROW(M43),12)=0)/(MOD(COLUMN($M$43:$AH$79)-COLUMN(M43),3)=0),2)
Press CTRL+SHIFT+ENTER to enter array formulas.