I have a very unusual situation on my NHL standings file. This situation is from last season just before the end of the regular season. I've been waiting to get Office 2024 to see if I can better rectify my problem, which I now have (YAY!).
In the "Standings" sheet, lines 14 and 15 are considered within playoff contention (wildcards). I have it set up that (with conditional formatting) when the background color (on the team's name cells - in column Y) turns green, the team has clinched a playoff berth. If it turns red, the team is out of playoff contention.
Also in the "Standings" sheet, the "Pts" column is the teams total points. Under "PPts", this is the possible total points a team can get if they win the rest of their games. Under "EN", this is a column I made up called the "Elimination number" where if this number gets to less than zero, the team is out of playoff contention and the background color in the team name's cell would turn red.
Now here's the problem I'm having. In the example I presented below (according to NHL.com), in this situation, Tampa Bay had officially clinched a playoff spot because Washington and Detroit play against each other later on on April 9 (see the "Schedule" sheet at the bottom). Therefore, either Detroit or Washington will lose, hence, their PPts will go from 94 to 92. Even if it went to overtime and the losing team gets 1 point, there are not enough wins or points under the NHL's tie-breaking rules (listed below) to qualify for a playoff berth. So in this case, the background color for Tampa Bay would have to turn green.
Would there be a way to search the upcoming games in the "schedule" sheet and match them with the teams that are outside the playoff contention area in the "Standings" sheet (lines 16 - 23, where if there were 2 teams in the bottom 8 that were going to face off against each other)?
My sincere apologies in advance if there is any confusion regarding my question.
In the "Standings" sheet, lines 14 and 15 are considered within playoff contention (wildcards). I have it set up that (with conditional formatting) when the background color (on the team's name cells - in column Y) turns green, the team has clinched a playoff berth. If it turns red, the team is out of playoff contention.
Also in the "Standings" sheet, the "Pts" column is the teams total points. Under "PPts", this is the possible total points a team can get if they win the rest of their games. Under "EN", this is a column I made up called the "Elimination number" where if this number gets to less than zero, the team is out of playoff contention and the background color in the team name's cell would turn red.
Now here's the problem I'm having. In the example I presented below (according to NHL.com), in this situation, Tampa Bay had officially clinched a playoff spot because Washington and Detroit play against each other later on on April 9 (see the "Schedule" sheet at the bottom). Therefore, either Detroit or Washington will lose, hence, their PPts will go from 94 to 92. Even if it went to overtime and the losing team gets 1 point, there are not enough wins or points under the NHL's tie-breaking rules (listed below) to qualify for a playoff berth. So in this case, the background color for Tampa Bay would have to turn green.
Would there be a way to search the upcoming games in the "schedule" sheet and match them with the teams that are outside the playoff contention area in the "Standings" sheet (lines 16 - 23, where if there were 2 teams in the bottom 8 that were going to face off against each other)?
My sincere apologies in advance if there is any confusion regarding my question.
| 2023-24 NHL Season edit.xlsx | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | |||
| 13 | Team | GP | W | L | OT | RW | ROW | GF | GA | Diff | P% | S/O | Last 10 | Streak | Pts | GR | PPts | EN | ||
| 14 | Tampa Bay Lightning | 77 | 43 | 27 | 7 | 35 | 40 | 274 | 251 | +23 | .604 | 3-0 | 7-2-1 | L1 | 93 | 5 | 103 | - | ||
| 15 | Pittsburgh Penguins | 77 | 36 | 30 | 11 | 31 | 34 | 235 | 230 | +5 | .539 | 2-3 | 6-2-2 | W4 | 83 | 5 | 93 | - | ||
| 16 | Philadelphia Flyers | 78 | 36 | 31 | 11 | 28 | 32 | 226 | 249 | -23 | .532 | 4-3 | 2-5-3 | L2 | 83 | 4 | 91 | 8 | ||
| 17 | Washington Capitals | 76 | 36 | 30 | 10 | 28 | 32 | 206 | 246 | -40 | .539 | 4-5 | 4-5-1 | L3 | 82 | 6 | 94 | 11 | ||
| 18 | Detroit Red Wings | 76 | 37 | 31 | 8 | 26 | 35 | 254 | 253 | +1 | .539 | 2-1 | 4-4-2 | L1 | 82 | 6 | 94 | 11 | ||
| 19 | Buffalo Sabres | 77 | 37 | 35 | 5 | 31 | 35 | 233 | 231 | +2 | .513 | 2-1 | 5-5-0 | W2 | 79 | 5 | 89 | 6 | ||
| 20 | New Jersey Devils | 77 | 37 | 36 | 4 | 32 | 37 | 253 | 265 | -12 | .506 | 0-1 | 5-5-0 | W1 | 78 | 5 | 88 | 5 | ||
| 21 | Ottawa Senators | 76 | 33 | 39 | 4 | 24 | 30 | 241 | 266 | -25 | .461 | 3-0 | 5-5-0 | L3 | 70 | 6 | 82 | -1 | ||
| 22 | Montréal Canadiens | 76 | 29 | 35 | 12 | 19 | 25 | 211 | 263 | -52 | .461 | 4-6 | 4-5-1 | L2 | 70 | 6 | 82 | -1 | ||
| 23 | Columbus Blue Jackets | 77 | 26 | 39 | 12 | 20 | 23 | 225 | 279 | -54 | .416 | 3-2 | 3-6-1 | W1 | 64 | 5 | 74 | -9 | ||
Standings | ||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| Y14:Y23 | Y14 | =INDEX(Calculations!$BM$4:$BM$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| Z14:Z23 | Z14 | =INDEX(Calculations!$BN$4:$BN$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AA14:AA23 | AA14 | =INDEX(Calculations!$BO$4:$BO$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AB14:AB23 | AB14 | =INDEX(Calculations!$BP$4:$BP$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AC14:AC23 | AC14 | =INDEX(Calculations!$BQ$4:$BQ$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AD14:AD23 | AD14 | =INDEX(Calculations!$BR$4:$BR$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AE14:AE23 | AE14 | =INDEX(Calculations!$BS$4:$BS$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AF14:AF23 | AF14 | =INDEX(Calculations!$BV$4:$BV$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AG14:AG23 | AG14 | =INDEX(Calculations!$BW$4:$BW$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AH14:AH23 | AH14 | =INDEX(Calculations!$BX$4:$BX$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AI14:AI23 | AI14 | =INDEX(Calculations!$BY$4:$BY$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AJ14:AJ23 | AJ14 | =INDEX(Calculations!$BZ$4:$BZ$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AK14:AK23 | AK14 | =INDEX(Calculations!$CA$4:$CA$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AL14:AL23 | AL14 | =INDEX(Calculations!$CB$4:$CB$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AM14:AM23 | AM14 | =INDEX(Calculations!$CC$4:$CC$13,MATCH(SMALL(Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,$W14),Calculations!$BL$4:$BL$13+ROW(Calculations!$BL$4:$BL$13)/10^7,0)) |
| AN14:AN23 | AN14 | =82-Z14 |
| AO14:AO23 | AO14 | =164-((AB14*2)+AC14) |
| AP16:AP23 | AP16 | =IF(AND(AO16=$AM$15,OR($Z$15>(Z16+2),$AA$15>(AA16+2),$AD$15>(AD16+2),$AE$15>(AE16+2),)),AO16-$AM$15-1,AO16-$AM$15) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| Y15 | Expression | =AND(AM15>=MAX(AO16:AO23),OR(AD15>(INDEX(AD16:AD23,MATCH(MAX(AO16:AO23),AO16:AO23,0))+INDEX(AN16:AN23,MATCH(MAX(AO16:AO23),AO16:AO23,0))),AE15>INDEX(AE16:AE23,MATCH(MAX(AO16:AO23),AO16:AO23,0))+INDEX(AN16:AN23,MATCH(MAX(AO16:AO23),AO16:AO23,0)))) | text | NO |
| BC4:BC19,AH14:AH23,AH28:AH30,BC28:BC43,AH33:AH35,AH38:AH47,AH51:AH82 | Cell Value | <0 | text | NO |
| BC4:BC19,AH14:AH23,AH28:AH30,BC28:BC43,AH33:AH35,AH38:AH47,AH51:AH82 | Cell Value | >0 | text | NO |
| Y16 | Expression | =$AP$16<0 | text | NO |
| Y17 | Expression | =$AP$17<0 | text | NO |
| Y18 | Expression | =$AP$18<0 | text | NO |
| Y19 | Expression | =$AP$19<0 | text | NO |
| Y20 | Expression | =$AP$20<0 | text | NO |
| Y21 | Expression | =$AP$21<0 | text | NO |
| Y22 | Expression | =$AP$22<0 | text | NO |
| Y23 | Expression | =$AP$23<0 | text | NO |
| Y14 | Expression | =AND(AM14>=MAX(AO16:AO23),OR(AD14>(INDEX(AD16:AD23,MATCH(MAX(AO16:AO23),AO16:AO23,0))+INDEX(AN16:AN23,MATCH(MAX(AO16:AO23),AO16:AO23,0))),AE14>INDEX(AE16:AE23,MATCH(MAX(AO16:AO23),AO16:AO23,0))+INDEX(AN16:AN23,MATCH(MAX(AO16:AO23),AO16:AO23,0)))) | text | NO |
| 2023-24 NHL Season edit.xlsx | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | Home | Visitor | |||||||||||||||
| 2 | Date | Team | Score | OT | RW | ROW | W/L/OT | SO(W/L) | Score | OT | RW | ROW | W/L/OT | SO(W/L) | Team | ||
Schedule | |||||||||||||||||
| 2023-24 NHL Season edit.xlsx | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1219 | Saturday, April 6, 2024 | Pittsburgh Penguins | 5 | x | x | W | 4 | L | Tampa Bay Lightning | ||||||||
| 1220 | Boston Bruins | 3 | x | W | 2 | x | OT | Florida Panthers | |||||||||
| 1221 | Minnesota Wild | 2 | L | 4 | x | x | W | Winnipeg Jets | |||||||||
| 1222 | Chicago Blackhawks | 3 | x | x | W | 2 | L | Dallas Stars | |||||||||
| 1223 | San Jose Sharks | 3 | x | W | 2 | x | OT | St. Louis Blues | |||||||||
| 1224 | Columbus Blue Jackets | 6 | x | x | W | 2 | L | Philadelphia Flyers | |||||||||
| 1225 | Montréal Canadiens | 2 | L | 4 | x | x | W | Toronto Maple Leafs | |||||||||
| 1226 | Ottawa Senators | 3 | L | 4 | x | x | W | New Jersey Devils | |||||||||
| 1227 | New York Islanders | 2 | x | x | W | 0 | L | Nashville Predators | |||||||||
| 1228 | Calgary Flames | 2 | L | 4 | x | x | W | Edmonton Oilers | |||||||||
| 1229 | Los Angeles Kings | 6 | x | x | W | 3 | L | Vancouver Canucks | |||||||||
| 1230 | Sunday, April 7, 2024 | Detroit Red Wings | Buffalo Sabres | ||||||||||||||
| 1231 | Chicago Blackhawks | Minnesota Wild | |||||||||||||||
| 1232 | Carolina Hurricanes | Columbus Blue Jackets | |||||||||||||||
| 1233 | San Jose Sharks | Arizona Coyotes | |||||||||||||||
| 1234 | Washington Capitals | Ottawa Senators | |||||||||||||||
| 1235 | New Jersey Devils | Nashville Predators | |||||||||||||||
| 1236 | New York Rangers | Montréal Canadiens | |||||||||||||||
| 1237 | Anaheim Ducks | St. Louis Blues | |||||||||||||||
| 1238 | Colorado Avalanche | Dallas Stars | |||||||||||||||
| 1239 | Monday, April 8, 2024 | Toronto Maple Leafs | Pittsburgh Penguins | ||||||||||||||
| 1240 | Vancouver Canucks | Vegas Golden Knights | |||||||||||||||
| 1241 | Tuesday, April 9, 2024 | Boston Bruins | Carolina Hurricanes | ||||||||||||||
| 1242 | Detroit Red Wings | Washington Capitals | |||||||||||||||
| 1243 | Florida Panthers | Ottawa Senators | |||||||||||||||
Schedule | |||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G1219:G1243 | G1219 | =IF(C1219>I1219,"W",IF(D1219="x","OT",IF(C1219<I1219,"L",""))) |
| H1219:H1243 | H1219 | =IF(AND(E1219="",F1219="",J1219="x")=TRUE,"SOW",IF(AND(K1219="",L1219="",D1219="x")=TRUE,"SOL","")) |
| M1219:M1243 | M1219 | =IF(C1219<I1219,"W",IF(J1219="x","OT",IF(C1219>I1219,"L",""))) |
| N1219:N1243 | N1219 | =IF(AND(K1219="",L1219="",D1219="x")=TRUE,"SOW",IF(AND(E1219="",F1219="",J1219="x")=TRUE,"SOL","")) |
| 2023-24 NHL Season edit.xlsx | |||
|---|---|---|---|
| BM | |||
| 8 | GP = Games Played | ||
| 9 | W = Total wins | ||
| 10 | L = Total losses | ||
| 11 | OT = Overtime/Shootout Losses | ||
| 12 | RW = Regulation Win (excluding overtime or shootout win) | ||
| 13 | ROW = Regulation/Overtime Win (excluding shootout win) | ||
| 14 | GF = Goals For | ||
| 15 | GA = Goals Against | ||
| 16 | Diff = Goal Differential (GF - GA) | ||
| 17 | P% = Point Percentage (Points earned / (Games Played x 2)) | ||
| 18 | S/O = Record in games decided by shoot-out | ||
| 19 | Pts = Total Points | ||
| 20 | GR = Games Remaining | ||
| 21 | PPts = Possible Total Points | ||
| 22 | EN = Elimination Number (<0 = Eliminated from Playoff Race) | ||
Standings | |||
| 2023-24 NHL Season edit.xlsx | |||
|---|---|---|---|
| BM | |||
| 27 | Tie-Breaker Rules | ||
| 28 | 1. Fewer number of games played. | ||
| 29 | 2. Greater number of Regulation Wins (RW). | ||
| 30 | 3. Greater number of Regulation/Overtime Wins (ROW). | ||
| 31 | 4. Greater number of Wins (W). | ||
| 32 | 5. Greater number of points earned in games against each other among 2 or more clubs. | ||
| 33 | 6. Greater goals differential (Diff). | ||
| 34 | 7. Greater number of Goals scored (GF). | ||
Standings | |||
