Offset

[gtd526]

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.

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.

 

[jasonb75]

The 2 cells below A13 is showing the MAX range. I just want to Offset from the cell location (AH5)

The 2 cells below A13 don't show a range, they show a number. Given that the formula refers to BX3:BX15, there is nothing whatsoever to link that to AH5 other than the fact that they contain the same number but that doesn't tell us anything.

 

[gtd526]

The 2 cells below A13 don't show a range, they show a number. Given that the formula refers to BX3:BX15, there is nothing whatsoever to link that to AH5 other than the fact that they contain the same number but that doesn't tell us anything.

I cant post the entire range, its too large. Its B3:BX15. A14 & A15 are showing that I can get the MAX using 2 diff formulas. I simply what to Offset from the cell location representing the MAX number (AH5), not knowing how many (-)rows to get the team initials (POR), because the row number will differ, the MAX will change. I get the correct offset in A17.Can you Offset an unknown number of rows up (Offset(?,5), not knowing the number?

 

[jasonb75]

There will need to be a known of some kind, but the number can be set by an additional formula.

Forget formulas for a minute, how would you decide what the number of rows to offset should be by looking at the sheet?

 

[gtd526]

There will need to be a known of some kind, but the number can be set by an additional formula.

Forget formulas for a minute, how would you decide what the number of rows to offset should be by looking at the sheet?

I don't know the number of rows to offset, I just want it to go to the Top row every time.
Im just looking for an offset formula to do that. I included the formulas I use to find the max, then offset from there (?,-2). Whatever that might be. the max will change all the time, so the rows will change. Go to top row, if possible. maybe there no way to offset to top row.

 

[jasonb75]

Ok, so winding the question back a bit, you want to find the MAX number of points (Pts) in the table then retrieve what appears to be an abbreviated team name from row 1, 2 columns to the left of the MAX Pts?

Just wanted to be sure that I'm on the right track before attempting anything.

edit:- a quick attempt at a formula based on what I think is correct.

=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($B$1:$BV$1)/($D$3:$BX$15=$A$14)/($D$2:$BX$2="Pts"),1)),"")

It would be much easier to answer your questions if you told us what you want to do, not how you are trying to do it. As you can see, there is no OFFSET anywhere in the formula.

 

[gtd526]

Ok, so winding the question back a bit, you want to find the MAX number of points (Pts) in the table then retrieve what appears to be an abbreviated team name from row 1, 2 columns to the left of the MAX Pts?

Just wanted to be sure that I'm on the right track before attempting anything.

edit:- a quick attempt at a formula based on what I think is correct.

=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($B$1:$BV$1)/($D$3:$BX$15=$A$14)/($D$2:$BX$2="Pts"),1)),"")

It would be much easier to answer your questions if you told us what you want to do, not how you are trying to do it. As you can see, there is no OFFSET anywhere in the formula.

The formula is working. I will try to be more accurate or 'what I want to do, not how' in future posts.
Thank you for the replies.