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