Calculate the Home Win Streak for a team

[JO505]

In the file I have identified the wins and losses at home for a team. I am having problem getting them to sum properly. I want to show a running total for wins at home.

Jim O

[mcmahobt]

Calculates max "home win" win streak:

=MAX(FREQUENCY(IF($K$2:K55="Home Win",ROW($K$2:K55)),IF($K$2:K55<>"Home Win",ROW($K$2:K55))))

Confirm with CSE.

[MarvinP]

Hi Jim,

See if Column L and M give you what you want. They are running totals of Wins and then Wins at home. If not what you want, keep bumping this question. AND give us what the correct answer is. BTW - you may need the 2010 version of Excel to do a CountIFS() function as 2000 may not have had it yet.

[shg]

Another way:

Row\Col	K	L	M
1	Home Win Streak	Results
2	home loss	0	L2: =CHOOSE(MATCH(K2, {"away","home loss","home win"}, 0), N(L1), 0, N(L1)+1)
3	away	0
4	away	0
5	away	0
6	away	0
7	home win	1
8	away	1
9	home win	2
10	home loss	0
11	home loss	0
12	away	0
13	home loss	0
14	home win	1
15	home win	2
16	home win	3
17	home win	4
18	away	4
19	away	4
20	away	4
21	home win	5
22	away	5
23	away	5
24	home win	6
25	home loss	0

[samba_ravi]

L2=IF(K2<>"home win","",SUM(INDEX(((K$2:K2="home win")*ROW(K$2:K2)>=MAX((K$2:K2="home loss")*ROW(K$2:K2)))*1,0)))
TRY THIS AND COPY TOWARDS DOWN

[shg]

Conditional formatting can suppress display ...

Row\Col	K	L	M
1	Home Win Streak	Results
2	home loss	0	L2: =CHOOSE(MATCH(K2, {"away","home loss","home win"}, 0), N(L1), 0, N(L1)+1)
3	away	0
4	away	0
5	away	0
6	away	0
7	home win	1
8	away	1
9	home win	2
10	home loss	0
11	home loss	0
12	away	0
13	home loss	0
14	home win	1
15	home win	2
16	home win	3
17	home win	4
18	away	4
19	away	4
20	away	4
21	home win	5
22	away	5
23	away	5
24	home win	6
25	home loss	0

[JO505]

Thank you all for your input.

I like Sivas solution because it is very clean and only displays the wins once and shows all other results as "".

Again thank much to all.

Jim O

[samba_ravi]

You are welcome and thanks for your feedback

[JO505]

I applied the formulas to another data set and I have run into an issue I can't find out what is wrong. For some reason in this set the sequence is counting the data from "away" games, see columns "S" & "T".

Jim O

[shg]

Row\Col	R	S	T
3	Home Rec	Home Streak
4	home win	1	S4: =CHOOSE(MATCH(R4, {"away","home loss","home win"}, 0), N(S3), 0, N(S3)+1)
5	home win	2
6	home win	3
7	home win	4
8	home win	5
9	home win	6
10	home win	7
11	home win	8
12	away	8
13	home win	9
14	away	9
15	home win	10
16	home win	11
17	home win	12
18	away	12
19	home win	13
20	away	13
21	away	13
22	home win	14
23	away	14
24	home win	15
25	home win	16

[JO505]

SHG,

Still having an issue, see attached.

Jim O

[shg]

I don't see the problem - the shg column is correct:

Row\Col	R	S	T
3	Home Rec	Home Streak	SHG
4	home win	1	1
5	home win	2	2
6	home win	3	3
7	home win	4	4
8	home win	5	5
9	home win	6	6
10	home win	7	7
11	home win	8	8
12	away		8
13	home win	10	9
14	away		9
15	home win	12	10
16	home win	13	11
17	home win	14	12
18	away		12

As I said, you can use conditional formatting to suppress display of the numbers you don't want to see.

[JO505]

Whats is the best way to not show duplicate values in any single series, i.e. 7,8,8,9,9,10,11,12,12. Every time I try to add something like, IF something then "", the formula does not work. Any conditional format or something to display 7,8,9,10 etc?

Its not that big of a deal but I am just curious.

Jim O

[JO505]

Cell "S7" is not returning the correct value. Any ideas as to thr problem?

Jim O

[shg]

If column R <> "home win", format column T as ;;;

[shg]

You are a stubborn guy

Row\Col	R	S	T
3	Home Rec	SHG
4	home win	1	S4: =CHOOSE(MATCH(R4, {"away","home loss","home win"}, 0), N(S3), 0, N(S3)+1)
5	home win	2
6	away	2
7	home win	3	should be 3
8	home loss	0
9	away	0
10	away	0
11	home win	1
12	home win	2
13	away	2
14	home loss	0
15	away	0
16	home loss	0

[JO505]

Did you look at my last attachment?

Jim O

[JO505]

Here is my file snip.

Jim O

[shg]

Did you look at my last attachment?

Post #16 was copied from your last attachment. Here's the formula added to your workbook.

[samba_ravi]

=IF(R4<>"home win","",COUNTIF(R4:INDEX(R:R,MAX(INDEX((R$4:R4="home loss")*ROW(R$4:R4),0),1)),R4))
Try this formula and copy towards down
i hope it will solve your problem

[JO505]

It is working now. Thank you shg and all for your time and patience with this problem and me. I apologize if I came off as too stubborn, the formula was not giving me the correct data and I had no idea why. I was as frustrated with myself for not understanding why as I was with the formula itself for not working.

Again thanks for your help

Jim O

[shg]

You're welcome.