Clinching NHL Playoff Spot

[dotcanada]

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.

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

 

[Rijnsent]

Hi dotcanada,
that's a tough question to solve with a simple formula... As in: you basically want do so some scenarios, not a simple calculation. If you'd e.g. calculate how many points/win a team could theoretically get, that's easy. But you want to know: given the matches that will come, is there a chance (if so, how big) that all teams below team X will jump over team X in the ranking. The only way I can see that happening is with scenario's (and VBA for that matter), not a simple calculation.
If I'm being creative you could calculate the number of matches being played where both teams are below team X in the ranking to give you some kind of proxy/indicator, but that's quite a niche-end-of-season indicator.
Good luck,
Koen

 

[dotcanada]

Hi Koen.

Uhg, I was afraid of that (going the VBA way). I'm currently still trying the formula route. I understand that it won't be a simple formula (I have a lot of complex formulae in this file).

Thanks for the info.

Derek