Calculate Win/Loss Streak & Last x games

[ziggy1971]

I'm trying to figure out how to calculate the Win/Loss streaks as well as the W-L-OTL results of the last x games (variable by entering number in column)

I hope the spreadsheet will be able to show what I'm looking for as I'm unable to explain it very clearly.

For instance, I've highlighted a team and the areas that I need some help with the formulas.

 

[ziggy1971]

Just found out I can't add attachments...

 

[dUBBINS]

You can post a small snippet or two of what you want to show the forum.

 

[ziggy1971]

I uploaded the sheet here, see if it works:
https://docs.google.com/spreadsheets/d/1JItSYl1HdeS9zkA_eO57tZw_Zr15pY4uFWxU9pJZCO4/edit?usp=sharing

 

[ziggy1971]

Here is a short section of the table. There are 1230 games in the regular season, but I figured the first 50 games should do to show what I'd like to accomplish.
On the left, columns B through H, is the schedule with Home and Away teams along with the results of each game, score for each team and whether it was in regulation time, Overtime, or went to a shootout.
On the right is the summary of the division including the number of games played, wins, losses, OT losses, Points. ROW (Regulation and Overtime wins), Goals for and against, Home and Away records and Shootout records.

I have formulas to calculate the summary based on each games results, however, I cannot seem to figure out how to calculate the record for the last 10 games played for each team.
I also would like to calculate the winning/losing streak for each team.

The table:
Excel 2010

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
3									Atlantic Division
4	Game #	Date	Away				Home			Total
5	1	10/12/2016	Toronto Maple Leafs	4	OT	5	Ottawa Senators		Team	GP	W	L	OT	Pts	ROW	GF	GA	Diff	Home	Away	S/O	L10	Streak
6	2	10/12/2016	St. Louis Blues	5		2	Chicago Blackhawks		Boston Bruins	80	44	30	6	94	42	232	207	+25	23-16-0	21-14-6	2-2
7	3	10/12/2016	Calgary Flames	4		7	Edmonton Oilers		Buffalo Sabres	80	33	35	12	78	31	199	230	-31	20-15-6	13-20-6	2-6
8	4	10/12/2016	Los Angeles Kings	1		2	San Jose Sharks		Detroit Red Wings	80	32	36	12	76	23	201	240	-39	16-17-6	16-19-6	9-0
9	5	10/13/2016	Montréal Canadiens	4		1	Buffalo Sabres		Florida Panthers	79	33	35	11	77	28	202	231	-29	18-18-3	15-17-8	5-6
10	6	10/13/2016	Boston Bruins	6		3	Columbus Blue Jackets		Montréal Canadiens	80	46	25	9	101	43	221	194	+27	24-11-5	22-14-4	3-1
11	7	10/13/2016	New York Islanders	3		5	New York Rangers		Ottawa Senators	79	42	27	10	94	38	205	208	-3	21-11-8	21-16-2	4-4
12	8	10/13/2016	Detroit Red Wings	4		6	Tampa Bay Lightning		Tampa Bay Lightning	79	39	30	10	88	35	222	222	0	22-14-4	17-16-6	4-3
13	9	10/13/2016	New Jersey Devils	1	OT	2	Florida Panthers		Toronto Maple Leafs	79	39	25	15	93	38	243	232	+11	20-11-7	19-14-8	1-8
14	10	10/13/2016	Washington Capitals	2	OT	3	Pittsburgh Penguins
15	11	10/13/2016	Minnesota Wild	2		3	St. Louis Blues
16	12	10/13/2016	Carolina Hurricanes	4	OT	5	Winnipeg Jets
17	13	10/13/2016	Anaheim Ducks	2		4	Dallas Stars
18	14	10/14/2016	Chicago Blackhawks	2		3	Nashville Predators
19	15	10/14/2016	Edmonton Oilers	5		3	Calgary Flames
20	16	10/14/2016	Philadelphia Flyers	4		2	Los Angeles Kings		Points System
21	17	10/15/2016	Montréal Canadiens	3	OT	4	Ottawa Senators					Win	Loss
22	18	10/15/2016	Boston Bruins	1		4	Toronto Maple Leafs		Regulation Time			2	0	points
23	19	10/15/2016	New Jersey Devils	2		3	Tampa Bay Lightning		Overtime			2	1
24	20	10/15/2016	Detroit Red Wings	1		4	Florida Panthers		Shootout			2	1
25	21	10/15/2016	New York Islanders	1		2	Washington Capitals
26	22	10/15/2016	San Jose Sharks	3		2	Columbus Blue Jackets
27	23	10/15/2016	Anaheim Ducks	2		3	Pittsburgh Penguins
28	24	10/15/2016	Winnipeg Jets	3		4	Minnesota Wild
29	25	10/15/2016	New York Rangers	2		3	St. Louis Blues
30	26	10/15/2016	Nashville Predators	3		5	Chicago Blackhawks
31	27	10/15/2016	Dallas Stars	5		6	Colorado Avalanche
32	28	10/15/2016	Philadelphia Flyers	3	OT	4	Arizona Coyotes
33	29	10/15/2016	Calgary Flames	1	SO	2	Vancouver Canucks
34	30	10/16/2016	Anaheim Ducks	2	OT	3	New York Islanders
35	31	10/16/2016	Buffalo Sabres	6		2	Edmonton Oilers
36	32	10/16/2016	Carolina Hurricanes	3	OT	4	Vancouver Canucks
37	33	10/17/2016	Colorado Avalanche	4	OT	3	Pittsburgh Penguins
38	34	10/17/2016	San Jose Sharks	4		7	New York Rangers
39	35	10/17/2016	Ottawa Senators	1		5	Detroit Red Wings
40	36	10/17/2016	Boston Bruins	4		1	Winnipeg Jets
41	37	10/18/2016	Colorado Avalanche	0		3	Washington Capitals
42	38	10/18/2016	San Jose Sharks	3		2	New York Islanders
43	39	10/18/2016	Anaheim Ducks	1		2	New Jersey Devils
44	40	10/18/2016	Pittsburgh Penguins	0		4	Montréal Canadiens
45	41	10/18/2016	Arizona Coyotes	4		7	Ottawa Senators
46	42	10/18/2016	Florida Panthers	3	SO	4	Tampa Bay Lightning
47	43	10/18/2016	Los Angeles Kings	3		6	Minnesota Wild
48	44	10/18/2016	Dallas Stars	2		1	Nashville Predators
49	45	10/18/2016	Philadelphia Flyers	4		7	Chicago Blackhawks
50	46	10/18/2016	Carolina Hurricanes	2		3	Edmonton Oilers
51	47	10/18/2016	Buffalo Sabres	3	OT	4	Calgary Flames
52	48	10/18/2016	St. Louis Blues	1	OT	2	Vancouver Canucks
53	49	10/19/2016	Detroit Red Wings	2		1	New York Rangers
54	50	10/19/2016	Toronto Maple Leafs	4	OT	5	Winnipeg Jets

<tbody>
</tbody>

To me it would seem that you would take the total games played, subtract 10 games (or whatever amount you like) and calculate the W-L-OT record from there. Maybe I'm wrong, don't know yet.

Any advice/help is greatly appreciated.
Thanx in advance.

 

[ziggy1971]

bump

 

[ziggy1971]

I've been searching the forums/web for days and still can't find a solution.

Given the complex solutions I've seen posted, I find it hard to believe nobody has some advice/suggestions for me.
Is there something wrong with the data? Presentation? Layout?

...at wits end...

 

[jolivanes]

Googledocs wants me to sign in. I have enough bosses that tell me what to do. I don't need Googledocs to tell me also what to do.
If you use DropBox (free) I might have something for you

 

[ziggy1971]

Sorry, I didn't know Googledocs did that.
Yes, I do have Dropbox
Try this link...
https://www.dropbox.com/s/7yu5sboerltjxea/NHL 2016-17 - Scratch_5.xlsx?dl=0

 

[jolivanes]

I should have read your post better. You want a formula. That is beyond me unfortunately.
My apologies.