gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 657
- Office Version
- 2019
- Platform
- 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.
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 | |||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | AX | AY | AZ | BA | BB | BC | BD | BE | BF | BG | BW | BX | BY | BZ | ||||||||||||||||||
5 | 001- MOV>ATS (R:R > 0) | 008- Asst/Tovr Ratio (V:V) | 015- (S,T,U,V,W) S:W | 022- | Max Range | ||||||||||||||||||||||||||||||||||||
6 | Current | Total | Current | Total | Current | Total | Current | Total | 1 | 80.0% | 015- (S,T,U,V,W) S:W | ||||||||||||||||||||||||||||||
7 | W: | 0 | W: | 5 | 62.5% | W: | 0 | W: | 86.5 | 49.7% | W: | 0 | W: | 12 | 80.0% | W: | W: | 0 | 2 | 66.7% | 66.7% | ||||||||||||||||||||
8 | L: | 0 | L: | 3 | L: | 0 | L: | 87.5 | L: | 0 | L: | 3 | L: | L: | 0 | 3 | 65.8% | ||||||||||||||||||||||||
9 | Psh: | 0 | Psh: | 0 | Psh: | 0 | Psh: | 3 | Psh: | 0 | Psh: | 0 | Psh: | Psh: | 0 | 4 | 62.5% | ||||||||||||||||||||||||
10 | Previous | 8 | Previous | 174 | Previous | 15 | Previous | 0 | 5 | 61.9% | |||||||||||||||||||||||||||||||
11 | W: | 5 | $ 22.75 | W: | 86.5 | $ 393.58 | W: | 12 | $ 54.60 | W: | 0 | $ - | 6 | 61.2% | |||||||||||||||||||||||||||
12 | L: | 3 | $ 15.00 | $ 7.75 | L: | 87.5 | $ 437.50 | $ (43.93) | L: | 3 | $ 15.00 | $ 39.60 | L: | 0 | $ - | $ - | 7 | 59.4% | |||||||||||||||||||||||
13 | Psh: | 0 | Psh: | 3 | Psh: | 0 | Psh: | 0 | 8 | 57.7% | |||||||||||||||||||||||||||||||
14 | 002- Ct of 9 | 009- Asst/FGM (W:W) | 016- (S,T,U) S:U | 023- | 9 | 57.5% | |||||||||||||||||||||||||||||||||||
15 | Current | Total | Current | Total | Current | Total | Current | Total | 10 | 57.3% | |||||||||||||||||||||||||||||||
16 | W: | 0 | W: | 11 | 47.8% | W: | 0 | W: | 96.5 | 55.1% | W: | 0 | W: | 41 | 61.2% | W: | W: | 0 | |||||||||||||||||||||||
17 | L: | 0 | L: | 12 | L: | 0 | L: | 78.5 | L: | 0 | L: | 26 | L: | L: | 0 | ||||||||||||||||||||||||||
18 | Psh: | 0 | Psh: | 0 | Psh: | 0 | Psh: | 3 | Psh: | 0 | Psh: | 0 | Psh: | Psh: | 0 | ||||||||||||||||||||||||||
19 | Previous | 23 | Previous | 175 | Previous | 67 | Previous | 0 | |||||||||||||||||||||||||||||||||
20 | W: | 11 | $ 50.05 | W: | 96.5 | $ 439.08 | W: | 41 | $ 186.55 | W: | 0 | $ - | |||||||||||||||||||||||||||||
21 | L: | 12 | $ 60.00 | $ (9.95) | L: | 78.5 | $ 392.50 | $ 46.58 | L: | 26 | $ 130.00 | $ 56.55 | L: | 0 | $ - | $ - | |||||||||||||||||||||||||
22 | Psh: | 0 | Psh: | 3 | Psh: | 0 | Psh: | 0 | |||||||||||||||||||||||||||||||||
Favs |
Cell Formulas | ||
---|---|---|
Range | Formula | |
AR7,BG16,BB16,AW16,AR16,BG7,BB7,AW7 | AR7 | =IFERROR((AQ7/AQ10),"") |
BZ6 | BZ6 | =OFFSET(INDIRECT(INDEX(NBAMaxRange,MATCH(MAX(N(INDIRECT(NBAMaxRange))),N(INDIRECT(NBAMaxRange)),0))),-2,-4) |
BZ7 | BZ7 | =AGGREGATE(14,6,AR7:BL61/(MOD(ROW(AR7:BL61)-ROW(AR7),9)=0)/(MOD(COLUMN(AR7:BL61)-COLUMN(AR7),5)=0),2) |
AO7 | AO7 | =COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"W")+(AO9*0.5) |
AO8 | AO8 | =COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"L")+(AO9*0.5) |
AO9 | AO9 | =COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"Psh") |
AT7 | AT7 | =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) |
AT8 | AT8 | =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) |
AT9 | AT9 | =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)))) |
AY7 | AY7 | =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) |
AY8 | AY8 | =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) |
AY9 | AY9 | =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:AQ8 | AQ7 | =AO11+AO7 |
AQ9,BF18,BA18,AV18,AQ18,BF9,BA9,AV9 | AQ9 | =AO9+AO13 |
AQ10,BF19,BA19,AV19,AQ19,BF10,BA10,AV10 | AQ10 | =AQ7+AQ8 |
AP11,BE20,AZ20,AU20,AP20,BE11,AZ11,AU11 | AP11 | =AO11*4.55 |
AP12,BE21,AZ21,AU21,AP21,BE12,AZ12,AU12 | AP12 | =AO12*5 |
AQ12,BF21,BA21,AV21,AQ21,BF12,BA12,AV12 | AQ12 | =AP11-AP12 |
BY6 | BY6 | =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) |
BY7 | BY7 | =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) |
BY8 | BY8 | =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) |
BY9 | BY9 | =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) |
BY10 | BY10 | =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) |
BY11 | BY11 | =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) |
BY12 | BY12 | =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) |
BY13 | BY13 | =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) |
BY14 | BY14 | =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) |
BY15 | BY15 | =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) |
AO16 | AO16 | =COUNTIFS(AD4:AD29,"9",D4:D29,"W")+(AO18*0.5) |
AO17 | AO17 | =COUNTIFS(AD4:AD29,"9",D4:D29,"L")+(AO18*0.5) |
AO18 | AO18 | =COUNTIFS(AD4:AD29,"9",D4:D29,"Psh") |
AT16 | AT16 | =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) |
AT17 | AT17 | =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) |
AT18 | AT18 | =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)))) |
AY16 | AY16 | =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) |
AY17 | AY17 | =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) |
AY18 | AY18 | =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. |