Offset or Index, Match

gtd526

Active Member
Joined
Jul 30, 2013
Messages
361
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,140
Office Version
  1. 365
Platform
  1. Windows
I want to Offset -2,-4 using this array or any other formula.
....
I want to replace BZ7.
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?
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,134
Office Version
  1. 2019
Platform
  1. Windows
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.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,134
Office Version
  1. 2019
Platform
  1. Windows
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.
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
361
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
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
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
361
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.
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.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,134
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,706
Messages
5,654,845
Members
418,155
Latest member
demasisi

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
Top