Offset

gtd526

Active Member
Joined
Jul 30, 2013
Messages
338
Office Version
  1. 2019
Platform
  1. 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.

Cell Formulas
RangeFormula
B1,AI1,AF1B1=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,AF2B2=COUNTIF(InjuryList!$A:$A,B1)
B3,AI3,AF3B3=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:C15C3=IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[FG%]],10,0),"")
B4,AI4,AF4B4=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,AF5B5=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,AF6B6=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,AF7B7=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,AF8B8=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,AF9B9=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,AF10B10=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,AF11B11=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,AF12B12=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,AI13AF13=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,AI14AF14=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,AI15AF15=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)),"")
A5A5=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)
A7A7=MAXIFS(B3:BX15,B3:BX15,"<>0",B3:BX15,"<>""")
A9A9=MAXIFS(D16:BX16,D16:BX16,"<>0",D16:BX16,"<>""")
A11A11=OFFSET(INDEX(D16:BX16,MATCH(MAX(D16:BX16),D16:BX16,0)),-15,-2)
A13A13=Offest(MAXIFS(B3:BX15,B3:BX15,"<>0",B3:BX15,"<>"""),-4,-2)
A14A14=MAXIFS(B3:BX15,B3:BX15,"<>0",B3:BX15,"<>""")
A15A15=MAX(B3:BX15)
AK3:AK15,AH3:AH15,D3:D15D3=IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[PTS]],29,0),"")
D16,AK16,AH16D16=IFERROR(AVERAGEIFS(D3:D15,D3:D15,"<>0"),"")
A17A17=OFFSET(INDEX(D16:BX16,MATCH(MAX(D16:BX16),D16:BX16,0)),-15,-2)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jul 30, 2013
Messages
338
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows
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

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

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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.
 
Last edited:
Solution

gtd526

Active Member
Joined
Jul 30, 2013
Messages
338
Office Version
  1. 2019
Platform
  1. Windows
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.
Excel Formula:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,271
Messages
5,635,215
Members
416,847
Latest member
inaramos

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