AYouQueTai
New Member
- Joined
- Sep 14, 2019
- Messages
- 34
- Office Version
- 365
- Platform
- Windows
Can anyone figure out why I am getting a #Value error.
I've tried entering the following formula's in cell E16
=SUMPRODUCT((SeasonSeries[Visitor]=A8)*(SeasonSeries[Vgoal]))
=SUMPRODUCT((SeasonSeries[Visitor]=A8)*(--(SeasonSeries[Vgoal])))
I've checked the formatting in Season Series table. xl2bb says the worksheet is too big to post so i am copying the 2 essential parts of it.
I've tried entering the following formula's in cell E16
=SUMPRODUCT((SeasonSeries[Visitor]=A8)*(SeasonSeries[Vgoal]))
=SUMPRODUCT((SeasonSeries[Visitor]=A8)*(--(SeasonSeries[Vgoal])))
I've checked the formatting in Season Series table. xl2bb says the worksheet is too big to post so i am copying the 2 essential parts of it.
NHL 2021.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
11 | Team | Stat | Matchup | GP | G/GA | GPG | ||
12 | VEG | Overall | Offense | 14 | 42 | 3.00 | ||
13 | COL | Overall | Defense | 13 | 26 | 2.00 | ||
14 | VEG | Last 6 | Offense | 6 | 15 | 2.50 | ||
15 | COL | Last 6 | Defense | 6 | 8 | 1.33 | ||
16 | VEG | Series | Offense | 2 | #VALUE! | 0.00 | ||
17 | COL | Series | Defense | 2 | 0.00 | |||
Matchup NEW |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E12 | E12 | =SUM(SUMPRODUCT((Goals[Date]>=Dropdowns!$F$2)*(Goals[Date]<$B$2)*(Goals[GF Team]=$A$8)*(Goals[Period]=1)),SUMPRODUCT((Goals[Date]>=Dropdowns!$F$2)*(Goals[Date]<$B$2)*(Goals[GF Team]=$A$8)*(Goals[Period]=2)),SUMPRODUCT((Goals[Date]>=Dropdowns!$F$2)*(Goals[Date]<$B$2)*(Goals[GF Team]=$A$8)*(Goals[Period]=3))) |
E13 | E13 | =SUM(SUMPRODUCT((Goals[Date]>=Dropdowns!$F$2)*(Goals[Date]<$B$2)*(Goals[GA Team]=$E$8)*(Goals[Period]=1)),SUMPRODUCT((Goals[Date]>=Dropdowns!$F$2)*(Goals[Date]<$B$2)*(Goals[GA Team]=$E$8)*(Goals[Period]=2)),SUMPRODUCT((Goals[Date]>=Dropdowns!$F$2)*(Goals[Date]<$B$2)*(Goals[GA Team]=$E$8)*(Goals[Period]=3))) |
E14 | E14 | =SUM(SUMPRODUCT((VLast6[Visitor]=$A8)*(VLast6[Vgoal])),SUMPRODUCT((VLast6[Home]=$A8)*(VLast6[Hgoal]))) |
E15 | E15 | =SUM(SUMPRODUCT((HLast6[Visitor]=$E8)*(HLast6[Hgoal])),SUMPRODUCT((HLast6[Home]=$E8)*(HLast6[Vgoal]))) |
E16 | E16 | =SUMPRODUCT((SeasonSeries[Visitor]=A8)*(--(SeasonSeries[Vgoal]))) |
A12,A16,A14 | A12 | =VLOOKUP($A$8,Teams[#All],3,FALSE) |
A13,A17,A15 | A13 | =VLOOKUP($E$8,Teams[#All],3,FALSE) |
D12 | D12 | =SUM(SUMPRODUCT((Results[Date]>=Dropdowns!$F$2)*(Results[Date]<$B$2)*(Results[Status]="Completed")*(Results[Visitor]=$A$8)),SUMPRODUCT((Results[Date]>=Dropdowns!$F$2)*(Results[Date]<$B$2)*(Results[Status]="Completed")*(Results[Home]=$A$8))) |
D13 | D13 | =SUM(SUMPRODUCT((Results[Date]>=Dropdowns!$F$2)*(Results[Date]<$B$2)*(Results[Status]="Completed")*(Results[Visitor]=$E$8)),SUMPRODUCT((Results[Date]>=Dropdowns!$F$2)*(Results[Date]<$B$2)*(Results[Status]="Completed")*(Results[Home]=$E$8))) |
D14 | D14 | =SUM(SUMPRODUCT((VLast6[Date]>=Dropdowns!$F$2)*(VLast6[Date]<$B$2)*(VLast6[Visitor]=$A$8)),SUMPRODUCT((VLast6[Date]>=Dropdowns!$F$2)*(VLast6[Date]<$B$2)*(VLast6[Home]=$A$8))) |
D15 | D15 | =SUM(SUMPRODUCT((HLast6[Date]>=Dropdowns!$F$2)*(HLast6[Date]<$B$2)*(HLast6[Visitor]=$E$8)),SUMPRODUCT((HLast6[Date]>=Dropdowns!$F$2)*(HLast6[Date]<$B$2)*(HLast6[Home]=$E$8))) |
D16 | D16 | =SUM(SUMPRODUCT((SeasonSeries[Date]>=Dropdowns!$F$2)*(SeasonSeries[Date]<$B$2)*(SeasonSeries[Visitor]=$A$8)),SUMPRODUCT((SeasonSeries[Date]>=Dropdowns!$F$2)*(SeasonSeries[Date]<$B$2)*(SeasonSeries[Home]=$A$8))) |
D17 | D17 | =SUM(SUMPRODUCT((SeasonSeries[Date]>=Dropdowns!$F$2)*(SeasonSeries[Date]<$B$2)*(SeasonSeries[Visitor]=$E$8)),SUMPRODUCT((SeasonSeries[Date]>=Dropdowns!$F$2)*(SeasonSeries[Date]<$B$2)*(SeasonSeries[Home]=$E$8))) |
F12:F15 | F12 | =E12/D12 |
F16:F17 | F16 | =IF(ISERROR(E16/D16),0,(E16/D16)) |
NHL 2021.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
45 | Season Series | |||||||
46 | GameID | Date | Visitor | Vgoal | Home | Hgoal | ||
47 | 020243 | Feb-14 | Colorado | 0 | Vegas | 1 | ||
48 | 020259 | Feb-16 | Colorado | 3 | Vegas | 2 | ||
49 | ||||||||
50 | ||||||||
51 | ||||||||
52 | ||||||||
53 | ||||||||
54 | ||||||||
55 | ||||||||
56 | ||||||||
Matchup NEW |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A47:A54 | A47 | =IFERROR(INDEX(Results[GameID],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"") |
B47:B54 | B47 | =IFERROR(INDEX(Results[Date],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"") |
C47:C54 | C47 | =IFERROR(INDEX(Results[Visitor],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"") |
D47:D54 | D47 | =IFERROR(INDEX(Results[VGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"") |
E47:E54 | E47 | =IFERROR(INDEX(Results[Home],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"") |
F47:F54 | F47 | =IFERROR(INDEX(Results[HGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"") |
A55 | A55 | =IFERROR(INDEX(Results[GameID],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"") |
B55 | B55 | =IFERROR(INDEX(Results[Date],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"") |
C55 | C55 | =IFERROR(INDEX(Results[Visitor],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"") |
D55 | D55 | =IFERROR(INDEX(Results[VGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"") |
E55 | E55 | =IFERROR(INDEX(Results[Home],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"") |
F55 | F55 | =IFERROR(INDEX(Results[HGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"") |
A56 | A56 | =IFERROR(INDEX(Results[GameID],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"") |
B56 | B56 | =IFERROR(INDEX(Results[Date],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"") |
C56 | C56 | =IFERROR(INDEX(Results[Visitor],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"") |
D56 | D56 | =IFERROR(INDEX(Results[VGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"") |
E56 | E56 | =IFERROR(INDEX(Results[Home],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"") |
F56 | F56 | =IFERROR(INDEX(Results[HGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"") |