Offset ?

gtd526

Active Member
Joined
Jul 30, 2013
Messages
329
Office Version
  1. 2019
Platform
  1. Windows
Hello,
I want to bring the Avg Pts for the Team listed in (AE). Thank you.
Here is the sheet that determines the Team name. Sheet name (Favs)
NBA.xlsm
AEAFAG
2Avg
3TeamsInjuriesPts
4Tie6
5Tie8
6TOR2
7 7
8Tie1
9Tie3
10 2
11UTA3
12BOS5
13 3
14 4
15WAS4
Favs
Cell Formulas
RangeFormula
AE4AE4=IFS($AD4>$AD5,$A4,$AD4<$AD5,"",AND($AD4=0,$AD5=0),"",$AD4=$AD5,"Tie")
AF4:AF15AF4=IFERROR(INDEX(Injuries!$2:$2,MATCH($A$4:$A$29,Injuries!$1:$1,0)),"")
AE5AE5=IFS($AD5>$AD4,$A5,$AD5<$AD4,"",AND($AD5=0,$AD4=0),"",$AD5=$AD4,"Tie")
AE6,AE14,AE12,AE10,AE8AE6=IFS($AD6>$AD7,$A6,OR($AD6<$AD7,AND($AD6=0,$AD7=0)),"",$AD6=$AD7,"Tie")
AE7,AE15,AE13,AE11,AE9AE7=IFS($AD7>$AD6,$A7,OR($AD7<$AD6,AND($AD7=0,$AD6=0)),"",$AD7=$AD6,"Tie")


Here is the 2nd sheet that has the Avg Pts for each team. Sheet name (Injuries). The Range goes from B:BV.
Cell Formulas
RangeFormula
B1,E1B1=LOOKUP(2, 1/((COUNTIF($B$1:B1,Favs!$A$4:$A$29)=0)*(Favs!$A$4:$A$29<>"")),Favs!$A$4:$A$29)
B2,E2B2=COUNTIF(InjuryList!$A:$A,B1)
B3,E3B3=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)),"")
F3:F15,C3:C15C3=IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[FG%]],10,0),"")
B4,E4B4=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,E5B5=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,E6B6=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,E7B7=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,E8B8=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,E9B9=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,E10B10=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,E11B11=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,E12B12=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)),"")
B13,E13B13=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),11)),"")
B14,E14B14=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),12)),"")
B15,E15B15=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),13)),"")
G3:G15,D3:D15D3=IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[PTS]],29,0),"")
D16,G16D16=IFERROR(AVERAGEIFS(D3:D15,D3:D15,"<>0"),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,067
Office Version
  1. 2019
Platform
  1. Windows
A couple of comments...
1. You might consider changing your INDEX/SMALL construction to
=IFERROR(INDEX(InjuryList!$B$3:$B$202,AGGREGATE(15,6,(ROW(InjuryList!$A$3:$A$202)-ROW(InjuryList!$A$2))/(B$1=InjuryList!$A$3:$A$202),ROWS(B$3:B3))),"")
entered in B3 and copied down and then copied into each subsequent name column (E3 and down, etc.)
That will eliminate the need for using an array formula and the ROWS function at the end of the formula will update as the formula is pulled down to occupy the 13 placeholder cells for player names, giving 1 for the 1st instance, 2 for the 2nd, and so on. AGGREGATE(15 means to return the smallest nth value.

2. OFFSET would be one way of pulling over the average, but I would favor INDEX/MATCH here in AG4 and down on the Favs sheet:
=INDEX(Injuries!$B$16:$BV$16,MATCH(AE4,Injuries!$B$1:$BV$1,0)+2)

One question though...this will give an error if a match is not found, so what is meant by TIE and blanks?
You'll probably want to wrap this with some type of check or trap to avoid the problem. Perhaps...
=IF((AE4<>"")*(AE4<>"TIE"),INDEX(Injuries!$B$16:$BV$16,MATCH(AE4,Injuries!$B$1:$BV$1,0)+2),"")
 
Solution

gtd526

Active Member
Joined
Jul 30, 2013
Messages
329
Office Version
  1. 2019
Platform
  1. Windows
A couple of comments...
1. You might consider changing your INDEX/SMALL construction to
=IFERROR(INDEX(InjuryList!$B$3:$B$202,AGGREGATE(15,6,(ROW(InjuryList!$A$3:$A$202)-ROW(InjuryList!$A$2))/(B$1=InjuryList!$A$3:$A$202),ROWS(B$3:B3))),"")
entered in B3 and copied down and then copied into each subsequent name column (E3 and down, etc.)
That will eliminate the need for using an array formula and the ROWS function at the end of the formula will update as the formula is pulled down to occupy the 13 placeholder cells for player names, giving 1 for the 1st instance, 2 for the 2nd, and so on. AGGREGATE(15 means to return the smallest nth value.

2. OFFSET would be one way of pulling over the average, but I would favor INDEX/MATCH here in AG4 and down on the Favs sheet:
=INDEX(Injuries!$B$16:$BV$16,MATCH(AE4,Injuries!$B$1:$BV$1,0)+2)

One question though...this will give an error if a match is not found, so what is meant by TIE and blanks?
You'll probably want to wrap this with some type of check or trap to avoid the problem. Perhaps...
=IF((AE4<>"")*(AE4<>"TIE"),INDEX(Injuries!$B$16:$BV$16,MATCH(AE4,Injuries!$B$1:$BV$1,0)+2),"")
Thank you for the reply. Your formula works great for "offset". I implemented the other changes you mentioned. Thank you.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,067
Office Version
  1. 2019
Platform
  1. Windows
You're welcome...I'm happy to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

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