gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 657
- Office Version
- 2019
- Platform
- Windows
Hello,
Looking to Offset with Unknown number of Rows Up.
A13 is formula location. The 2 cells below A13 is showing the MAX range. I just want to Offset from the cell location (AH5). The number of rows Up will differ.
thank you.
Looking to Offset with Unknown number of Rows Up.
A13 is formula location. The 2 cells below A13 is showing the MAX range. I just want to Offset from the cell location (AH5). The number of rows Up will differ.
thank you.
NBA.xlsm | |||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | AF | AG | AH | AI | AJ | AK | ||||||||||||||||||||||||||||||
1 | Team: | LAL | POR | SAN | |||||||||||||||||||||||||||||||||||
2 | Injuries: | 2 | FG% | Pts | 4 | FG% | Pts | 5 | FG% | Pts | |||||||||||||||||||||||||||||
3 | Who: | Kostas Antetokounmpo | 0% | 0 | Zach Collins | Keita Bates-Diop | 0% | 0 | |||||||||||||||||||||||||||||||
4 | Max FG%: | Jared Dudley | 100% | 3 | CJ Elleby | 63% | 14 | Drew Eubanks | 23% | 9 | |||||||||||||||||||||||||||||
5 | 100% | CJ McCollum | 47% | 347 | Luka Šamanić | 20% | 4 | ||||||||||||||||||||||||||||||||
6 | Max Pts: | Jusuf Nurkić | 49% | 118 | Quinndary Weatherspoon | ||||||||||||||||||||||||||||||||||
7 | 347 | Derrick White | 33% | 9 | |||||||||||||||||||||||||||||||||||
8 | Max Avg Pts: | ||||||||||||||||||||||||||||||||||||||
9 | 160 | ||||||||||||||||||||||||||||||||||||||
10 | Offset MAX Avg Pts: | ||||||||||||||||||||||||||||||||||||||
11 | SAN | ||||||||||||||||||||||||||||||||||||||
12 | Team Max Pts: | ||||||||||||||||||||||||||||||||||||||
13 | #NAME? | ||||||||||||||||||||||||||||||||||||||
14 | 347 | ||||||||||||||||||||||||||||||||||||||
15 | 347 | ||||||||||||||||||||||||||||||||||||||
16 | Offset MAX Avg Pts: | Avg: | 3 | Avg: | 160 | Avg: | 7.33 | ||||||||||||||||||||||||||||||||
17 | POR | ||||||||||||||||||||||||||||||||||||||
18 | correct result, not A11?? | ||||||||||||||||||||||||||||||||||||||
Injuries |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1,AI1,AF1 | B1 | =LOOKUP(2, 1/((COUNTIF($B$1:B1,Favs!$A$4:$A$29)=0)*(Favs!$A$4:$A$29<>"")),Favs!$A$4:$A$29) |
B2,AI2,AF2 | B2 | =COUNTIF(InjuryList!$A:$A,B1) |
B3,AI3,AF3 | B3 | =IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),1)),"") |
AJ3:AJ15,AG3:AG15,C3:C15 | C3 | =IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[FG%]],10,0),"") |
B4,AI4,AF4 | B4 | =IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),2)),"") |
B5,AI5,AF5 | B5 | =IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),3)),"") |
B6,AI6,AF6 | B6 | =IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),4)),"") |
B7,AI7,AF7 | B7 | =IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),5)),"") |
B8,AI8,AF8 | B8 | =IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),6)),"") |
B9,AI9,AF9 | B9 | =IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),7)),"") |
B10,AI10,AF10 | B10 | =IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),8)),"") |
B11,AI11,AF11 | B11 | =IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),9)),"") |
B12,AI12,AF12 | B12 | =IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),10)),"") |
AF13,B13,AI13 | AF13 | =IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(AF$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),11)),"") |
AF14,B14,AI14 | AF14 | =IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(AF$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),12)),"") |
AF15,B15,AI15 | AF15 | =IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(AF$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),13)),"") |
A5 | A5 | =MAX(C:C,F:F,I:I,L:L,O:O,R:R,U:U,X:X,AA:AA,AD:AD,AG:AG,AJ:AJ,AM:AM,AP:AP,AS:AS,AV:AV,AY:AY,BB:BB) |
A7 | A7 | =MAXIFS(B3:BX15,B3:BX15,"<>0",B3:BX15,"<>""") |
A9 | A9 | =MAXIFS(D16:BX16,D16:BX16,"<>0",D16:BX16,"<>""") |
A11 | A11 | =OFFSET(INDEX(D16:BX16,MATCH(MAX(D16:BX16),D16:BX16,0)),-15,-2) |
A13 | A13 | =Offest(MAXIFS(B3:BX15,B3:BX15,"<>0",B3:BX15,"<>"""),-4,-2) |
A14 | A14 | =MAXIFS(B3:BX15,B3:BX15,"<>0",B3:BX15,"<>""") |
A15 | A15 | =MAX(B3:BX15) |
AK3:AK15,AH3:AH15,D3:D15 | D3 | =IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[PTS]],29,0),"") |
D16,AK16,AH16 | D16 | =IFERROR(AVERAGEIFS(D3:D15,D3:D15,"<>0"),"") |
A17 | A17 | =OFFSET(INDEX(D16:BX16,MATCH(MAX(D16:BX16),D16:BX16,0)),-15,-2) |
Press CTRL+SHIFT+ENTER to enter array formulas. |