gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 660
- Office Version
- 2019
- Platform
- 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)
Here is the 2nd sheet that has the Avg Pts for each team. Sheet name (Injuries). The Range goes from B:BV.
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)
Cell Formulas | ||
---|---|---|
Range | Formula | |
AE4 | AE4 | =IFS($AD4>$AD5,$A4,$AD4<$AD5,"",AND($AD4=0,$AD5=0),"",$AD4=$AD5,"Tie") |
AF4:AF15 | AF4 | =IFERROR(INDEX(Injuries!$2:$2,MATCH($A$4:$A$29,Injuries!$1:$1,0)),"") |
AE5 | AE5 | =IFS($AD5>$AD4,$A5,$AD5<$AD4,"",AND($AD5=0,$AD4=0),"",$AD5=$AD4,"Tie") |
AE6,AE14,AE12,AE10,AE8 | AE6 | =IFS($AD6>$AD7,$A6,OR($AD6<$AD7,AND($AD6=0,$AD7=0)),"",$AD6=$AD7,"Tie") |
AE7,AE15,AE13,AE11,AE9 | AE7 | =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.
NBA.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | |||
1 | WAS | POR | ||||||
2 | 4 | FG% | Pts | 4 | FG% | Pts | ||
3 | Thomas Bryant | 65% | 143 | Zach Collins | ||||
4 | Rui Hachimura | 46% | 113 | CJ Elleby | 54% | 19 | ||
5 | Moritz Wagner | 72% | 49 | CJ McCollum | 47% | 347 | ||
6 | Russell Westbrook | 41% | 230 | Jusuf Nurkić | 49% | 118 | ||
7 | ||||||||
8 | ||||||||
9 | ||||||||
10 | ||||||||
11 | ||||||||
12 | ||||||||
13 | ||||||||
14 | ||||||||
15 | ||||||||
16 | Avg: | 134 | Avg: | 161 | ||||
Injuries |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1,E1 | 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,E2 | B2 | =COUNTIF(InjuryList!$A:$A,B1) |
B3,E3 | 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)),"") |
F3:F15,C3:C15 | C3 | =IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[FG%]],10,0),"") |
B4,E4 | 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,E5 | 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,E6 | 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,E7 | 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,E8 | 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,E9 | 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,E10 | 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,E11 | 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,E12 | 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)),"") |
B13,E13 | B13 | =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,E14 | B14 | =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,E15 | B15 | =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:D15 | D3 | =IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[PTS]],29,0),"") |
D16,G16 | D16 | =IFERROR(AVERAGEIFS(D3:D15,D3:D15,"<>0"),"") |
Press CTRL+SHIFT+ENTER to enter array formulas. |