Offset or Index, Match

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
657
Office Version
  1. 2019
Platform
  1. Windows
Hello,
Im currently using this array to bring back the 2nd largest value in a range (works). I want to Offset -2,-4 using this array or any other formula.
=AGGREGATE(14,6,AR7:BL61/(MOD(ROW(AR7:BL61)-ROW(AR7),9)=0)/(MOD(COLUMN(AR7:BL61)-COLUMN(AR7),5)=0),2)
Here is a portion, due to the size, of the worksheet. I want to replace BZ7.
thank you.

NBA.xlsm
ANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBWBXBYBZ
5001- MOV>ATS (R:R > 0)008- Asst/Tovr Ratio (V:V)015- (S,T,U,V,W) S:W022-Max Range
6CurrentTotalCurrentTotalCurrentTotalCurrentTotal180.0%015- (S,T,U,V,W) S:W
7W:0W:562.5%W:0W:86.549.7%W:0W:1280.0%W:W:0 266.7%66.7%
8L:0L:3L:0L:87.5L:0L:3L:L:0365.8%
9Psh:0Psh:0Psh:0Psh:3Psh:0Psh:0Psh:Psh:0462.5%
10Previous8Previous174Previous15Previous0561.9%
11W:5$ 22.75W:86.5$ 393.58W:12$ 54.60W:0$ -661.2%
12L:3$ 15.00$ 7.75L:87.5$ 437.50$ (43.93)L:3$ 15.00$ 39.60L:0$ -$ -759.4%
13Psh:0Psh:3Psh:0Psh:0857.7%
14002- Ct of 9009- Asst/FGM (W:W)016- (S,T,U) S:U023-957.5%
15CurrentTotalCurrentTotalCurrentTotalCurrentTotal1057.3%
16W:0W:1147.8%W:0W:96.555.1%W:0W:4161.2%W:W:0 
17L:0L:12L:0L:78.5L:0L:26L:L:0
18Psh:0Psh:0Psh:0Psh:3Psh:0Psh:0Psh:Psh:0
19Previous23Previous175Previous67Previous0
20W:11$ 50.05W:96.5$ 439.08W:41$ 186.55W:0$ -
21L:12$ 60.00$ (9.95)L:78.5$ 392.50$ 46.58L:26$ 130.00$ 56.55L:0$ -$ -
22Psh:0Psh:3Psh:0Psh:0
Favs
Cell Formulas
RangeFormula
AR7,BG16,BB16,AW16,AR16,BG7,BB7,AW7AR7=IFERROR((AQ7/AQ10),"")
BZ6BZ6=OFFSET(INDIRECT(INDEX(NBAMaxRange,MATCH(MAX(N(INDIRECT(NBAMaxRange))),N(INDIRECT(NBAMaxRange)),0))),-2,-4)
BZ7BZ7=AGGREGATE(14,6,AR7:BL61/(MOD(ROW(AR7:BL61)-ROW(AR7),9)=0)/(MOD(COLUMN(AR7:BL61)-COLUMN(AR7),5)=0),2)
AO7AO7=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"W")+(AO9*0.5)
AO8AO8=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"L")+(AO9*0.5)
AO9AO9=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"Psh")
AT7AT7=SUMPRODUCT(($D$4:$D$29="W")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AT9*0.5)
AT8AT8=SUMPRODUCT(($D$4:$D$29="L")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AT9*0.5)
AT9AT9=SUMPRODUCT(($D$4:$D$29="Psh")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))
AY7AY7=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AY9*0.5)
AY8AY8=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AY9*0.5)
AY9AY9=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
BF16:BF17,BA16:BA17,AV16:AV17,AQ16:AQ17,BF7:BF8,BA7:BA8,AV7:AV8,AQ7:AQ8AQ7=AO11+AO7
AQ9,BF18,BA18,AV18,AQ18,BF9,BA9,AV9AQ9=AO9+AO13
AQ10,BF19,BA19,AV19,AQ19,BF10,BA10,AV10AQ10=AQ7+AQ8
AP11,BE20,AZ20,AU20,AP20,BE11,AZ11,AU11AP11=AO11*4.55
AP12,BE21,AZ21,AU21,AP21,BE12,AZ12,AU12AP12=AO12*5
AQ12,BF21,BA21,AV21,AQ21,BF12,BA12,AV12AQ12=AP11-AP12
BY6BY6=MAX($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25)
BY7BY7=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX7)
BY8BY8=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX8)
BY9BY9=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX9)
BY10BY10=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX10)
BY11BY11=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX11)
BY12BY12=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX12)
BY13BY13=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX13)
BY14BY14=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX14)
BY15BY15=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX15)
AO16AO16=COUNTIFS(AD4:AD29,"9",D4:D29,"W")+(AO18*0.5)
AO17AO17=COUNTIFS(AD4:AD29,"9",D4:D29,"L")+(AO18*0.5)
AO18AO18=COUNTIFS(AD4:AD29,"9",D4:D29,"Psh")
AT16AT16=SUMPRODUCT(($D$4:$D$29="W")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AT18*0.5)
AT17AT17=SUMPRODUCT(($D$4:$D$29="L")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AT18*0.5)
AT18AT18=SUMPRODUCT(($D$4:$D$29="psh")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
AY16AY16=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AY18*0.5)
AY17AY17=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AY18*0.5)
AY18AY18=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
gtd526 said:
I want to Offset -2,-4 using this array or any other formula.
....
I want to replace BZ7.
Click to expand...
I can see what your current formula in BZ7 is doing.

Beyond that, it's totally unclear:
- Did you mean replace the formula in BZ6?
- What does NBAMaxRange refer to?
- What do you mean by offsetting -2,-4?
- What results would you like your formula(e) to return?
 
Upvote 0
I'm assuming you want something like this, where NBAMaxRange is a named range that can be used to reference multiple noncontiguous ranges...in this case, using the Name Manager:
NBAMaxRange = favs!$AR$5:$AR$22,favs!$AW$5:$AW$22,favs!$BB$5:$BB$22,favs!$BG$5:$BG$22
This will fail however if the same value occurs more than one in the named range because it will find the label corresponding to the calculation block associated with the first instance.
MrExcel20210209.xlsx
ANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBWBXBYBZCACB
5001- MOV>ATS (R:R > 0)008- Asst/Tovr Ratio (V:V)015- (S,T,U,V,W) S:W022- Max RangeHelper CoordinateHelper Coordinate
6CurrentTotalCurrentTotalCurrentTotalCurrentTotal10.8015- (S,T,U,V,W) S:W7.00054$BB$7
7W:0W:50.625W:0W:86.50.497126W:0W:120.8W:W:0 20.625001- MOV>ATS (R:R > 0)7.00044$AR$7
8L:0L:3L:0L:87.5L:0L:3L:L:030.61194016- (S,T,U) S:U16.00054$BB$16
9Psh:0Psh:0Psh:0Psh:3Psh:0Psh:0Psh:Psh:040.551429009- Asst/FGM (W:W)16.00049$AW$16
10Previous8Previous174Previous15Previous050.497126008- Asst/Tovr Ratio (V:V)7.00049$AW$7
11W:522.75W:86.5393.575W:1254.6W:0060.478261002- Ct of 916.00044$AR$16
12L:3157.75L:87.5437.5-43.925L:31539.6L:0007    
13Psh:0Psh:3Psh:0Psh:08    
14002- Ct of 9009- Asst/FGM (W:W)016- (S,T,U) S:U023- 9    
15CurrentTotalCurrentTotalCurrentTotalCurrentTotal10    
16W:0W:110.478261W:0W:96.50.551429W:0W:410.61194W:W:0 
17L:0L:12L:0L:78.5L:0L:26L:L:0
18Psh:0Psh:0Psh:0Psh:3Psh:0Psh:0Psh:Psh:0
19Previous23Previous175Previous67Previous0
20W:1150.05W:96.5439.075W:41186.55W:00
21L:1260-9.95L:78.5392.546.575L:2613056.55L:000
22Psh:0Psh:3Psh:0Psh:0
favs
Cell Formulas
RangeFormula
BY6:BY15BY6=IFERROR(LARGE(NBAMaxRange,ROWS(BY$6:BY6)),"")
BZ6:BZ15BZ6=IFERROR(OFFSET(INDIRECT(ADDRESS(INT(CA6),ROUND(MOD(CA6,1)*100000,0))),-2,-4),"")
CA6:CA15CA6=IF(ISNUMBER(BY6),MIN(IF(($AN$5:$BG$22=BY6)*(COUNT(1/(ERROR.TYPE(NBAMaxRange)=1))=0),ROW($AN$5:$BG$22)+(COLUMN($AN$5:$BG$5)/(100000)))),"")
CB6:CB15CB6=IFERROR(ADDRESS(INT(CA6),ROUND(MOD(CA6,1)*100000,0)),"")
AR7,BG16,BB16,AW16,AR16,BG7,BB7,AW7AR7=IFERROR((AQ7/AQ10),"")
AO7AO7=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"W")+(AO9*0.5)
AO8AO8=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"L")+(AO9*0.5)
AO9AO9=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"Psh")
AT7AT7=SUMPRODUCT(($D$4:$D$29="W")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AT9*0.5)
AT8AT8=SUMPRODUCT(($D$4:$D$29="L")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AT9*0.5)
AT9AT9=SUMPRODUCT(($D$4:$D$29="Psh")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))
AY7AY7=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AY9*0.5)
AY8AY8=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AY9*0.5)
AY9AY9=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
BF16:BF17,BA16:BA17,AV16:AV17,AQ16:AQ17,BF7:BF8,BA7:BA8,AV7:AV8,AQ7:AQ8AQ7=AO11+AO7
AQ9,BF18,BA18,AV18,AQ18,BF9,BA9,AV9AQ9=AO9+AO13
AQ10,BF19,BA19,AV19,AQ19,BF10,BA10,AV10AQ10=AQ7+AQ8
AP11,BE20,AZ20,AU20,AP20,BE11,AZ11,AU11AP11=AO11*4.55
AP12,BE21,AZ21,AU21,AP21,BE12,AZ12,AU12AP12=AO12*5
AQ12,BF21,BA21,AV21,AQ21,BF12,BA12,AV12AQ12=AP11-AP12
AO16AO16=COUNTIFS(AD4:AD29,"9",D4:D29,"W")+(AO18*0.5)
AO17AO17=COUNTIFS(AD4:AD29,"9",D4:D29,"L")+(AO18*0.5)
AO18AO18=COUNTIFS(AD4:AD29,"9",D4:D29,"Psh")
AT16AT16=SUMPRODUCT(($D$4:$D$29="W")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AT18*0.5)
AT17AT17=SUMPRODUCT(($D$4:$D$29="L")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AT18*0.5)
AT18AT18=SUMPRODUCT(($D$4:$D$29="psh")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
AY16AY16=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AY18*0.5)
AY17AY17=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AY18*0.5)
AY18AY18=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0
Here is a version that addresses the issue I mentioned. Note that I inserted numbers that match others in the bright green cells. The formulas in BY6:CB15 correctly list the values found in range NBAMaxRange in order from greatest to smallest and find the matching mini-table heading, even if there are duplicate values in NBAMaxRange.
MrExcel20210209.xlsx
ANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBWBXBYBZCACB
5001- MOV>ATS (R:R > 0)008- Asst/Tovr Ratio (V:V)015- (S,T,U,V,W) S:W022- Max RangeHelper CoordinateHelper Coordinate
6CurrentTotalCurrentTotalCurrentTotalCurrentTotal10.8015- (S,T,U,V,W) S:W7.00054$BB$7
7W:0W:50.625W:0W:86.50.497126W:0W:120.8W:W:0 20.8016- (S,T,U) S:U16.00054$BB$16
8L:0L:3L:0L:87.5L:0L:3L:L:030.625001- MOV>ATS (R:R > 0)7.00044$AR$7
9Psh:0Psh:0Psh:0Psh:3Psh:0Psh:0Psh:Psh:040.625023- 16.00059$BG$16
10Previous8Previous174Previous15Previous050.551429009- Asst/FGM (W:W)16.00049$AW$16
11W:522.75W:86.5393.575W:1254.6W:0060.497126008- Asst/Tovr Ratio (V:V)7.00049$AW$7
12L:3157.75L:87.5437.5-43.925L:31539.6L:00070.478261002- Ct of 916.00044$AR$16
13Psh:0Psh:3Psh:0Psh:08    
14002- Ct of 9009- Asst/FGM (W:W)016- (S,T,U) S:U023- 9    
15CurrentTotalCurrentTotalCurrentTotalCurrentTotal10    
16W:0W:110.478261W:0W:96.50.551429W:0W:410.8W:W:00.625
17L:0L:12L:0L:78.5L:0L:26IFERROR((BA16/BA19),"")L:L:0
18Psh:0Psh:0Psh:0Psh:3Psh:0Psh:0Psh:Psh:0
19Previous23Previous175Previous67Previous0
20W:1150.05W:96.5439.075W:41186.55W:00
21L:1260-9.95L:78.5392.546.575L:2613056.55L:000
22Psh:0Psh:3Psh:0Psh:0
favs
Cell Formulas
RangeFormula
BY6:BY15BY6=IFERROR(LARGE(NBAMaxRange,ROWS(BY$6:BY6)),"")
BZ6:BZ15BZ6=IFERROR(OFFSET(INDIRECT(ADDRESS(INT(CA6),ROUND(MOD(CA6,1)*100000,0))),-2,-4),"")
CA6:CA15CA6=IF(ISNUMBER(BY6),AGGREGATE(15,6,IF(($AN$5:$BG$22=BY6)*(COUNT(1/(ERROR.TYPE(NBAMaxRange)=1))=0),ROW($AN$5:$BG$22)+(COLUMN($AN$5:$BG$5)/(100000))),COUNTIF(BY$6:BY6,BY6)),"")
CB6:CB15CB6=IFERROR(ADDRESS(INT(CA6),ROUND(MOD(CA6,1)*100000,0)),"")
AR7,AW16,AR16,BG7,BB7,AW7AR7=IFERROR((AQ7/AQ10),"")
AO7AO7=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"W")+(AO9*0.5)
AO8AO8=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"L")+(AO9*0.5)
AO9AO9=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"Psh")
AT7AT7=SUMPRODUCT(($D$4:$D$29="W")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AT9*0.5)
AT8AT8=SUMPRODUCT(($D$4:$D$29="L")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AT9*0.5)
AT9AT9=SUMPRODUCT(($D$4:$D$29="Psh")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))
AY7AY7=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AY9*0.5)
AY8AY8=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AY9*0.5)
AY9AY9=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
BF16:BF17,BA16:BA17,AV16:AV17,AQ16:AQ17,BF7:BF8,BA7:BA8,AV7:AV8,AQ7:AQ8AQ7=AO11+AO7
AQ9,BF18,BA18,AV18,AQ18,BF9,BA9,AV9AQ9=AO9+AO13
AQ10,BF19,BA19,AV19,AQ19,BF10,BA10,AV10AQ10=AQ7+AQ8
AP11,BE20,AZ20,AU20,AP20,BE11,AZ11,AU11AP11=AO11*4.55
AP12,BE21,AZ21,AU21,AP21,BE12,AZ12,AU12AP12=AO12*5
AQ12,BF21,BA21,AV21,AQ21,BF12,BA12,AV12AQ12=AP11-AP12
AO16AO16=COUNTIFS(AD4:AD29,"9",D4:D29,"W")+(AO18*0.5)
AO17AO17=COUNTIFS(AD4:AD29,"9",D4:D29,"L")+(AO18*0.5)
AO18AO18=COUNTIFS(AD4:AD29,"9",D4:D29,"Psh")
AT16AT16=SUMPRODUCT(($D$4:$D$29="W")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AT18*0.5)
AT17AT17=SUMPRODUCT(($D$4:$D$29="L")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AT18*0.5)
AT18AT18=SUMPRODUCT(($D$4:$D$29="psh")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
AY16AY16=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AY18*0.5)
AY17AY17=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AY18*0.5)
AY18AY18=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0
KRice said:
Here is a version that addresses the issue I mentioned. Note that I inserted numbers that match others in the bright green cells. The formulas in BY6:CB15 correctly list the values found in range NBAMaxRange in order from greatest to smallest and find the matching mini-table heading, even if there are duplicate values in NBAMaxRange.
MrExcel20210209.xlsx
ANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBWBXBYBZCACB
5001- MOV>ATS (R:R > 0)008- Asst/Tovr Ratio (V:V)015- (S,T,U,V,W) S:W022- Max RangeHelper CoordinateHelper Coordinate
6CurrentTotalCurrentTotalCurrentTotalCurrentTotal10.8015- (S,T,U,V,W) S:W7.00054$BB$7
7W:0W:50.625W:0W:86.50.497126W:0W:120.8W:W:0 20.8016- (S,T,U) S:U16.00054$BB$16
8L:0L:3L:0L:87.5L:0L:3L:L:030.625001- MOV>ATS (R:R > 0)7.00044$AR$7
9Psh:0Psh:0Psh:0Psh:3Psh:0Psh:0Psh:Psh:040.625023- 16.00059$BG$16
10Previous8Previous174Previous15Previous050.551429009- Asst/FGM (W:W)16.00049$AW$16
11W:522.75W:86.5393.575W:1254.6W:0060.497126008- Asst/Tovr Ratio (V:V)7.00049$AW$7
12L:3157.75L:87.5437.5-43.925L:31539.6L:00070.478261002- Ct of 916.00044$AR$16
13Psh:0Psh:3Psh:0Psh:08    
14002- Ct of 9009- Asst/FGM (W:W)016- (S,T,U) S:U023- 9    
15CurrentTotalCurrentTotalCurrentTotalCurrentTotal10    
16W:0W:110.478261W:0W:96.50.551429W:0W:410.8W:W:00.625
17L:0L:12L:0L:78.5L:0L:26IFERROR((BA16/BA19),"")L:L:0
18Psh:0Psh:0Psh:0Psh:3Psh:0Psh:0Psh:Psh:0
19Previous23Previous175Previous67Previous0
20W:1150.05W:96.5439.075W:41186.55W:00
21L:1260-9.95L:78.5392.546.575L:2613056.55L:000
22Psh:0Psh:3Psh:0Psh:0
favs
Cell Formulas
RangeFormula
BY6:BY15BY6=IFERROR(LARGE(NBAMaxRange,ROWS(BY$6:BY6)),"")
BZ6:BZ15BZ6=IFERROR(OFFSET(INDIRECT(ADDRESS(INT(CA6),ROUND(MOD(CA6,1)*100000,0))),-2,-4),"")
CA6:CA15CA6=IF(ISNUMBER(BY6),AGGREGATE(15,6,IF(($AN$5:$BG$22=BY6)*(COUNT(1/(ERROR.TYPE(NBAMaxRange)=1))=0),ROW($AN$5:$BG$22)+(COLUMN($AN$5:$BG$5)/(100000))),COUNTIF(BY$6:BY6,BY6)),"")
CB6:CB15CB6=IFERROR(ADDRESS(INT(CA6),ROUND(MOD(CA6,1)*100000,0)),"")
AR7,AW16,AR16,BG7,BB7,AW7AR7=IFERROR((AQ7/AQ10),"")
AO7AO7=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"W")+(AO9*0.5)
AO8AO8=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"L")+(AO9*0.5)
AO9AO9=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"Psh")
AT7AT7=SUMPRODUCT(($D$4:$D$29="W")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AT9*0.5)
AT8AT8=SUMPRODUCT(($D$4:$D$29="L")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AT9*0.5)
AT9AT9=SUMPRODUCT(($D$4:$D$29="Psh")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))
AY7AY7=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AY9*0.5)
AY8AY8=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AY9*0.5)
AY9AY9=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
BF16:BF17,BA16:BA17,AV16:AV17,AQ16:AQ17,BF7:BF8,BA7:BA8,AV7:AV8,AQ7:AQ8AQ7=AO11+AO7
AQ9,BF18,BA18,AV18,AQ18,BF9,BA9,AV9AQ9=AO9+AO13
AQ10,BF19,BA19,AV19,AQ19,BF10,BA10,AV10AQ10=AQ7+AQ8
AP11,BE20,AZ20,AU20,AP20,BE11,AZ11,AU11AP11=AO11*4.55
AP12,BE21,AZ21,AU21,AP21,BE12,AZ12,AU12AP12=AO12*5
AQ12,BF21,BA21,AV21,AQ21,BF12,BA12,AV12AQ12=AP11-AP12
AO16AO16=COUNTIFS(AD4:AD29,"9",D4:D29,"W")+(AO18*0.5)
AO17AO17=COUNTIFS(AD4:AD29,"9",D4:D29,"L")+(AO18*0.5)
AO18AO18=COUNTIFS(AD4:AD29,"9",D4:D29,"Psh")
AT16AT16=SUMPRODUCT(($D$4:$D$29="W")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AT18*0.5)
AT17AT17=SUMPRODUCT(($D$4:$D$29="L")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AT18*0.5)
AT18AT18=SUMPRODUCT(($D$4:$D$29="psh")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
AY16AY16=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AY18*0.5)
AY17AY17=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AY18*0.5)
AY18AY18=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
Click to expand...
Is there a way to offset this array? w/ offset in the array.
=OFFSET(AGGREGATE(14,6,$AR$7:$BL$61/(MOD(ROW($AR$7:$BL$61)-ROW($AR$7),9)=0)/(MOD(COLUMN($AR$7:$BL$61)-COLUMN($AR$7),5)=0),BX7),5,-1
 
Upvote 0
gtd526 said:
Hello,
Im currently using this array to bring back the 2nd largest value in a range (works). I want to Offset -2,-4 using this array or any other formula.
=AGGREGATE(14,6,AR7:BL61/(MOD(ROW(AR7:BL61)-ROW(AR7),9)=0)/(MOD(COLUMN(AR7:BL61)-COLUMN(AR7),5)=0),2)
Here is a portion, due to the size, of the worksheet. I want to replace BZ7.
thank you.

NBA.xlsm
ANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBWBXBYBZ
5001- MOV>ATS (R:R > 0)008- Asst/Tovr Ratio (V:V)015- (S,T,U,V,W) S:W022-Max Range
6CurrentTotalCurrentTotalCurrentTotalCurrentTotal180.0%015- (S,T,U,V,W) S:W
7W:0W:562.5%W:0W:86.549.7%W:0W:1280.0%W:W:0 266.7%66.7%
8L:0L:3L:0L:87.5L:0L:3L:L:0365.8%
9Psh:0Psh:0Psh:0Psh:3Psh:0Psh:0Psh:Psh:0462.5%
10Previous8Previous174Previous15Previous0561.9%
11W:5$ 22.75W:86.5$ 393.58W:12$ 54.60W:0$ -661.2%
12L:3$ 15.00$ 7.75L:87.5$ 437.50$ (43.93)L:3$ 15.00$ 39.60L:0$ -$ -759.4%
13Psh:0Psh:3Psh:0Psh:0857.7%
14002- Ct of 9009- Asst/FGM (W:W)016- (S,T,U) S:U023-957.5%
15CurrentTotalCurrentTotalCurrentTotalCurrentTotal1057.3%
16W:0W:1147.8%W:0W:96.555.1%W:0W:4161.2%W:W:0 
17L:0L:12L:0L:78.5L:0L:26L:L:0
18Psh:0Psh:0Psh:0Psh:3Psh:0Psh:0Psh:Psh:0
19Previous23Previous175Previous67Previous0
20W:11$ 50.05W:96.5$ 439.08W:41$ 186.55W:0$ -
21L:12$ 60.00$ (9.95)L:78.5$ 392.50$ 46.58L:26$ 130.00$ 56.55L:0$ -$ -
22Psh:0Psh:3Psh:0Psh:0
Favs
Cell Formulas
RangeFormula
AR7,BG16,BB16,AW16,AR16,BG7,BB7,AW7AR7=IFERROR((AQ7/AQ10),"")
BZ6BZ6=OFFSET(INDIRECT(INDEX(NBAMaxRange,MATCH(MAX(N(INDIRECT(NBAMaxRange))),N(INDIRECT(NBAMaxRange)),0))),-2,-4)
BZ7BZ7=AGGREGATE(14,6,AR7:BL61/(MOD(ROW(AR7:BL61)-ROW(AR7),9)=0)/(MOD(COLUMN(AR7:BL61)-COLUMN(AR7),5)=0),2)
AO7AO7=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"W")+(AO9*0.5)
AO8AO8=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"L")+(AO9*0.5)
AO9AO9=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"Psh")
AT7AT7=SUMPRODUCT(($D$4:$D$29="W")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AT9*0.5)
AT8AT8=SUMPRODUCT(($D$4:$D$29="L")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AT9*0.5)
AT9AT9=SUMPRODUCT(($D$4:$D$29="Psh")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))
AY7AY7=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AY9*0.5)
AY8AY8=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AY9*0.5)
AY9AY9=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
BF16:BF17,BA16:BA17,AV16:AV17,AQ16:AQ17,BF7:BF8,BA7:BA8,AV7:AV8,AQ7:AQ8AQ7=AO11+AO7
AQ9,BF18,BA18,AV18,AQ18,BF9,BA9,AV9AQ9=AO9+AO13
AQ10,BF19,BA19,AV19,AQ19,BF10,BA10,AV10AQ10=AQ7+AQ8
AP11,BE20,AZ20,AU20,AP20,BE11,AZ11,AU11AP11=AO11*4.55
AP12,BE21,AZ21,AU21,AP21,BE12,AZ12,AU12AP12=AO12*5
AQ12,BF21,BA21,AV21,AQ21,BF12,BA12,AV12AQ12=AP11-AP12
BY6BY6=MAX($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25)
BY7BY7=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX7)
BY8BY8=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX8)
BY9BY9=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX9)
BY10BY10=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX10)
BY11BY11=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX11)
BY12BY12=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX12)
BY13BY13=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX13)
BY14BY14=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX14)
BY15BY15=LARGE(($AR7,$AR16,$AR25,$AR34,$AR43,$AR52,AR61,BG61,BL61,BL52,$AW7,$AW16,$AW25,$AW34,$AW43,$AW52,$AW61,$BB7,$BB16,$BB25,$BB34,$BB43,$BB52,$BB61,$BG7,$BG16,$BG34,$BG43,$BG52,$BL25,$BL34,$BL43,BG25,$BQ25),BX15)
AO16AO16=COUNTIFS(AD4:AD29,"9",D4:D29,"W")+(AO18*0.5)
AO17AO17=COUNTIFS(AD4:AD29,"9",D4:D29,"L")+(AO18*0.5)
AO18AO18=COUNTIFS(AD4:AD29,"9",D4:D29,"Psh")
AT16AT16=SUMPRODUCT(($D$4:$D$29="W")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AT18*0.5)
AT17AT17=SUMPRODUCT(($D$4:$D$29="L")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AT18*0.5)
AT18AT18=SUMPRODUCT(($D$4:$D$29="psh")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
AY16AY16=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AY18*0.5)
AY17AY17=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AY18*0.5)
AY18AY18=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...
Is there a way to offset this array? the offset is in the array but doesn't work.
=OFFSET(AGGREGATE(14,6,$AR$7:$BL$61/(MOD(ROW($AR$7:$BL$61)-ROW($AR$7),9)=0)/(MOD(COLUMN($AR$7:$BL$61)-COLUMN($AR$7),5)=0),BX7),5,-1
I want to bring the heading for each section. AN5, AN14, etc. Highlighted Dark Red.
 
Upvote 0
Please see my post #4...that solution does what you are asking. See my post #3 for a comment about the named range NBAMaxRange. You will need to create the named range (if you haven't already) and then incorporate the helper cells shown in my post #4 in cells CA6:CB15. The formula in BY6:BY15 sorts the sub-table summary quotients from largest to smallest (the top 10), and then the formula in BZ6:BZ15 brings the corresponding sub-table heading over.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
149
kerm007
K
E
  • Solved
Get a 2D spilled formula to fulfill a distribution table matching the ranges of both dimensions
Replies
3
Views
56
Fluff
Fluff
gtd526
  • Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
113
gtd526
gtd526
J
  • Question
sumifs formula using date ranges as criteria
Replies
1
Views
96
Phuoc
P
R
  • Solved
Failed in order to derive Row number or Value in cell of particular Row Using Vlookup and Index... Match
2
Replies
10
Views
342
RapchikM
R

Forum statistics

Threads
1,214,518
Messages
6,119,988
Members
448,935
Latest member
ijat

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