offset from 2nd Largest value in range

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
684
Office Version
  1. 2019
Platform
  1. Windows
Hello,
I have a formula that finds the 2nd largest value in a range (A42).
How can I offset from that value to get the heading (A44)? ie "MOV > ATS" (I39)
Thank you.


NBA.xlsm
ABCGHIJKLMNOPQRSTUVWXYZAAABAC
36Max %not FAV
37100%(ATS)
38Criteriaas FAVCount Diff >= 5 (K:K)not FAVnot FAVnot FAVAH:AK
39AH:AK > 70%MOV > ATSATS > 0 as FavAH:AK > 60%AH:AK > 70%CurrentIndicator >=70%>=70%
40W:0W:0W:0W:0W0W0
412nd Max %L:0L:0L:0L:0L0L0
4268%Psh:0Psh:0Psh:0Psh:0Psh0Psh0
432nd CriteriaPrevious67%Previous68%Previous57%Previous100%Previous63%Previous100%
44$O$43W:14.0$28.70W:21.0$45.55W:4.0$3.20W:3.0$13.65W27$42.85W1$4.55
45L:7.0CountL:10.0CountL:3.0> 60%L:0.0> 70%L16> 70%L0> 70%
46testTotal0Total0Total0Total0Total0Total0
47$O$43W:14.0W:21.0W:4.0W:3.0W27W1
48L:7.0L:10.0L:3.0L:0.0L16L0
49
50not FAVnot FAV, Inj < 70%Indicator > 70%, +10 CountIndicator > 70%, +10 Count
51(MOV > ATS) (AI:AK >70%)AI:AK > 70%TeamInd%$Count
52W:0W:0     W0
53L:0L:0     L0
54Psh:0Psh:0     Psh0
55Previous100%Previous0%     Previous58%
56W:1.0$4.55W:0.0-$5.00(LARGE($AJ$4:$AJ$33,ROWS(Z$52:Z52)))W11$10.05
57L:0.0CountL:1.0> 70%L8Count
58Total0Total0Total0
59W:1.0W:0.0W11
60L:0.0L:1.0L8
Favs
Cell Formulas
RangeFormula
A37:B37A37=INDIRECT(INDEX(NBA_Best_Percent,MATCH(MAX(N(INDIRECT(NBA_Best_Percent))),N(INDIRECT(NBA_Best_Percent)),0)))
A39:B39A39=OFFSET(INDIRECT(INDEX(NBA_Best_Percent,MATCH(MAX(N(INDIRECT(NBA_Best_Percent))),N(INDIRECT(NBA_Best_Percent)),0))),-4,-2)
A42A42=AGGREGATE(14,6,K43:AA55/(MOD(ROW(K43:AA55)-ROW(K43),11)=0)/(MOD(COLUMN(K43:AA55)-COLUMN(K43),4)=0),2)
J40J40=SUMPRODUCT(($A$4:$A$33=$AG$4:$AG$33)*($S$4:$S$33>0)*ISNUMBER($S$4:$S$33)*($AN$4:$AN$33<$AN$1)*($C$4:$C$33="W"))+J42*0.5
J41J41=SUMPRODUCT(($A$4:$A$33=$AG$4:$AG$33)*($S$4:$S$33>0)*ISNUMBER($S$4:$S$33)*($AN$4:$AN$33<$AN$1)*($C$4:$C$33="L"))+J42*0.5
J42J42=SUMPRODUCT(($A$4:$A$33=$AG$4:$AG$33)*($S$4:$S$33>0)*ISNUMBER($S$4:$S$33)*($AN$4:$AN$33<$AN$1)*($C$4:$C$33="Psh"))
N40N40=SUMPRODUCT(($A$4:$A$33=$AG$4:$AG$33)*($B$4:$B$33>0)*($K$4:$K$33>=5)*($C$4:$C$33="W")*($AN$4:$AN$33<$AN$1))+N42*0.5
N41N41=SUMPRODUCT(($A$4:$A$33=$AG$4:$AG$33)*($B$4:$B$33>0)*($K$4:$K$33>=5)*($C$4:$C$33="L")*($AN$4:$AN$33<$AN$1))+N42*0.5
N42N42=SUMPRODUCT(($A$4:$A$33=$AG$4:$AG$33)*($B$4:$B$33>0)*($K$4:$K$33>=5)*($C$4:$C$33="Psh")*($AN$4:$AN$33<$AN$1))
Q40Q40=COUNTIFS($C$4:$C$33,"W",$AH$4:$AH$33,">=0.6",$AH$4:$AH$33,"<0.7",$AI$4:$AI$33,">=0.6",$AI$4:$AI$33,"<0.7",$AJ$4:$AJ$33,">=0.6",$AJ$4:$AJ$33,"<0.7",$AK$4:$AK$33,">=0.6",$AK$4:$AK$33,"<0.7",$AN$4:$AN$33,"<"&$AN$1)+Q42*0.5
Q41Q41=COUNTIFS($C$4:$C$33,"L",$AH$4:$AH$33,">=0.6",$AH$4:$AH$33,"<0.7",$AI$4:$AI$33,">=0.6",$AI$4:$AI$33,"<0.7",$AJ$4:$AJ$33,">=0.6",$AJ$4:$AJ$33,"<0.7",$AK$4:$AK$33,">=0.6",$AK$4:$AK$33,"<0.7",$AN$4:$AN$33,"<"&$AN$1)+Q42*0.5
Q42Q42=COUNTIFS($C$4:$C$33,"Psh",$AH$4:$AH$33,">=0.6",$AH$4:$AH$33,"<0.7",$AI$4:$AI$33,">=0.6",$AI$4:$AI$33,"<0.7",$AJ$4:$AJ$33,">=0.6",$AJ$4:$AJ$33,"<0.7",$AK$4:$AK$33,">=0.6",$AK$4:$AK$33,"<0.7",$AN$4:$AN$33,"<"&$AN$1)
T40,Z40T40=COUNTIFS($C$4:$C$33,"W",$AH$4:$AH$33,">=.7",$AI$4:$AI$33,">=0.7",$AJ$4:$AJ$33,">=.7",$AK$4:$AK$33,">=.7",$AN$4:$AN$33,"<"&$AN$1)+T42*0.5
T41,Z41T41=COUNTIFS($C$4:$C$33,"L",$AH$4:$AH$33,">=.7",$AI$4:$AI$33,">=0.7",$AJ$4:$AJ$33,">=.7",$AK$4:$AK$33,">=.7",$AN$4:$AN$33,"<"&$AN$1)+T42*0.5
T42T42=COUNTIFS($C$4:$C$33,"Psh",$AH$4:$AH$33,">=.6",$AI$4:$AI$33,">=0.6",$AJ$4:$AJ$33,">=.6",$AK$4:$AK$33,">=.6",$AN$4:$AN$33,"<"&$AN$1)
W40W40=COUNTIFS(AH4:AH33,">=.7",C4:C33,"W",$AN$4:$AN$33,"<"&$AN$1)+(W42*0.5)
W41W41=COUNTIFS(AH4:AH33,">=.7",C4:C33,"L",$AN$4:$AN$33,"<"&$AN$1)+(W42*0.5)
W42W42=COUNTIFS(AH4:AH33,">=.7",C4:C33,"Psh",$AN$4:$AN$33,"<"&$AN$1)
Z42Z42=COUNTIFS($C$4:$C$33,"Psh",$AH$4:$AH$33,">=.7",$AI$4:$AI$33,">=0.7",$AJ$4:$AJ$33,">=.7",$AK$4:$AK$33,">=.7",$AN$4:$AN$33,"<"&$AN$1)
A44A44=ADDRESS(AGGREGATE(15,6,ROW(K43:AA55)/(K43:AA55=$A42),1),AGGREGATE(15,6,COLUMN(K43:AA55)/(K43:AA55=A42),1))
K43,AC55,O55,K55,AA43,X43,U43,R43,O43K43=J44/(J44+J45)
K44,O56,K56,U44,R44,O44K44=(J44*4.55-(J45*5))
X44,AC56,AA44X44=(W44*4.55)-(W45*5)
K46K46=SUMPRODUCT(($A$4:$A$33=$AG$4:$AG$33)*($S$4:$S$33>0)*ISNUMBER($S$4:$S$33)*($AN$4:$AN$33<$AN$1))
O46O46=SUMPRODUCT(($A$4:$A$33=$AG$4:$AG$33)*($B$4:$B$33>0)*($K$4:$K$33>=5)*($AN$4:$AN$33<$AN$1))
R46R46=COUNTIFS($AH$4:$AH$33,">=0.6",$AH$4:$AH$33,"<0.7",$AI$4:$AI$33,">=0.6",$AI$4:$AI$33,"<0.7",$AJ$4:$AJ$33,">=0.6",$AJ$4:$AJ$33,"<0.7",$AK$4:$AK$33,">=0.6",$AK$4:$AK$33,"<0.7",$AN$4:$AN$33,"<"&$AN$1)
U46U46=COUNTIFS($AH$4:$AH$33,">=.7",$AI$4:$AI$33,">=0.7",$AJ$4:$AJ$33,">=.7",$AK$4:$AK$33,">=.7",$AN$4:$AN$33,"<"&$AN$1)
X46X46=COUNTIFS(AH4:AH33,">0.7",$AN$4:$AN$33,"<"&$AN$1)
AA46AA46=COUNTIFS($AO$4:$AO$33,">=.7",$AI$4:$AI$33,">=0.7",$AJ$4:$AJ$33,">=.7",$AK$4:$AK$33,">=.7",$AN$4:$AN$33,"<"&$AN$1)
A47A47=ADDRESS(AGGREGATE(15,6,ROW(K43:AA55)/(K43:AA55=$A42),1),AGGREGATE(15,6,COLUMN(K43:AA55)/(K43:AA55=A42),1))
AB59:AB60,N59:N60,J59:J60,Z47:Z48,W47:W48,T47:T48,Q47:Q48,N47:N48,J47:J48J47=J40+J44
U52:U55U52=IFERROR(INDEX($A$4:$A$33,MATCH($V52,$G$4:$G$33,0),MATCH($Z52,$AF$4:$AF$33,0)),"")
V52:V55V52=IFERROR(LOOKUP(2, 1/((COUNTIF($V$50:$V51,$G$4:$G$33)=0)*($AH$4:$AH$33>=0.7)*($AF$4:$AF$33>=10)),$G$4:$G$33),"")
X52:X55X52=INDEX($AH$4:$AH$33,MATCH($V52,$G$4:$G$33,0))
Y52:Y55Y52=IFERROR(VLOOKUP($V52,'Criteria Ranks'!$A$3:$C$82,2,0),"")
Z52:Z55Z52=IFERROR(VLOOKUP($V52,'Criteria Ranks'!$A$3:$D$82,4,0),"")
J52J52=SUMPRODUCT((C4:C33="W")*(S4:S33<>"")*(S4:S33>0)*(AI4:AI33>=0.7)*(AJ4:AJ33>=0.7)*(AK4:AK33>=0.7)*(AN4:AN33<$AN$1))+$J$54*0.5
J53J53=SUMPRODUCT((C4:C33="L")*(S4:S33<>"")*(S4:S33>0)*(AI4:AI33>=0.7)*(AJ4:AJ33>=0.7)*(AK4:AK33>=0.7)*(AN4:AN33<$AN$1))+$J$54*0.5
J54J54=SUMPRODUCT((C4:C33="Psh")*(S4:S33<>"")*(S4:S33>0)*(AI4:AI33>=0.7)*(AJ4:AJ33>=0.7)*(AK4:AK33>=0.7)*(AN4:AN33<$AN$1))
N52N52=COUNTIFS($C$4:$C$33,"W",$AI$4:$AI$33,">=0.7",$AJ$4:$AJ$33,">=.7",$AK$4:$AK$33,">=.7",$AN$4:$AN$33,"<"&$AN$1)+N54*0.5
N53N53=COUNTIFS($C$4:$C$33,"L",$AI$4:$AI$33,">=0.7",$AJ$4:$AJ$33,">=0.7",$AK$4:$AK$33,">=0.7",$AN$4:$AN$33,"<"&$AN$1)+N54*0.5
N54N54=COUNTIFS($C$4:$C$33,"Psh",$AI$4:$AI$33,">=0.7",$AJ$4:$AJ$33,">=.7",$AK$4:$AK$33,">=.7",$AN$4:$AN$33,"<"&$AN$1)
AB52AB52=COUNTIFS(AH4:AH33,">=.7",$AF$4:$AF$33,">=10",$AF$4:$AF$33,"<>""",$C$4:$C$33,"W")+(AB54*0.5)
AB53AB53=COUNTIFS(AH4:AH33,">=.7",$AF$4:$AF$33,">=10",$AF$4:$AF$33,"<>""",$C$4:$C$33,"L")+(AB54*0.5)
AB54AB54=COUNTIFS(AH4:AH33,">=.7",$AF$4:$AF$33,">=10",$AF$4:$AF$33,"<>""",$C$4:$C$33,"Psh")
K58K58=SUMPRODUCT((S4:S33<>"")*(S4:S33>0)*(AI4:AI33>=0.7)*(AJ4:AJ33>=0.7)*(AK4:AK33>=0.7)*(AN4:AN33<$AN$1))
O58O58=COUNTIFS($AI$4:$AI$33,">=.7",$AJ$4:$AJ$33,">=0.7",$AK$4:$AK$33,">=.7")
AC58AC58=COUNTIFS(AH4:AH33,">=.7",$AF$4:$AF$33,">=10",$AF$4:$AF$33,"<>""")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
I've reconfigured the layout.
I'm looking for the header for the 2nd largest % value. (63%, Indicator >=70%)
Percentage values = D6, G6, J6, M6, P6, D18, G18, J18, M18, P18
Header values = B2, E2, H2, K2, N2, B14, E14, H14, K14
thank you.


NBA.xlsm
BCDEFGHIJKLMNOP
2MOV > ATSATS > 0 as FavAJ:AL > 60%AJ:AL > 70%CurrentIndicator >=70%
3W:0W:0W:0W:0W0
4L:0L:0L:0L:0L0
5Psh:0Psh:0Psh:0Psh:0Psh0
6Previous58%Previous70%Previous50%Previous57%Previous63%
7W:15.0$13.25W:23.0$54.65W:5.0-$2.25W:4.0$3.20W27$42.85
8L:11.0CountL:10.0CountL:5.0CountL:3.0CountL16Count
9Total0Total0Total0Total0Total0
10W:15.0W:23.0W:5.0W:4.0W27
11L:11.0L:10.0L:5.0L:3.0L16
12
13not FAVnot FAV, Inj < 70%as FAVas FAV
14(MOV > ATS) (AJ:AL >70%)AJ:AL > 70%MOV2 > ATSMOV2, MOV > ATS
15W:0W:0W:0W:0
16L:0L:0L:0L:0
17Psh:0Psh:0Psh:0Psh:0
18Previous25%Previous20%Previous21%Previous21%
19W:1.0-$10.45W:1.0-$15.45W:1.5-$20.68W:1.5-$20.68
20L:3.0CountL:4.0CountL:5.5CountL:5.5Count
21Total0Total0Total0Total0
22W:1.0W:1.0W:1.5W:1.5
23L:3.0L:4.0L:5.5L:5.5
Sheet1
 
Upvote 0
I found the correct array. I used a Named Range (NBA_Best_Percent) for the cells with % amounts and used the following array:
=OFFSET(INDIRECT(INDEX(NBA_Best_Percent,MATCH(MAX(N(INDIRECT(NBA_Best_Percent))),N(INDIRECT(NBA_Best_Percent)),1))),-4,-2)
The "1" represents Less Than Max. I was looking for the 2nd largest %, so this works.
 
Upvote 0
gtd526 said:
I found the correct array. I used a Named Range (NBA_Best_Percent) for the cells with % amounts and used the following array:
=OFFSET(INDIRECT(INDEX(NBA_Best_Percent,MATCH(MAX(N(INDIRECT(NBA_Best_Percent))),N(INDIRECT(NBA_Best_Percent)),1))),-4,-2)
The "1" represents Less Than Max. I was looking for the 2nd largest %, so this works.
Click to expand...
Its not working correctly. Still looking for Offset 2nd highest value.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Ramballah
  • Solved
Finding the first certain value with conditions
2
Replies
18
Views
932
mumps
mumps
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
349
Tetra201
Tetra201
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
361
gtd526
gtd526
gtd526
  • Question Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
357
gtd526
gtd526
M
  • Question Question
What am I doing Wrong?
Replies
6
Views
257
MBarrett
M

Forum statistics

Threads
1,223,749
Messages
6,174,280
Members
452,554
Latest member
Louis1225

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top