Calculating streaks when some cells are blank

[HBTim67]

I am trying to calculate winning and losing streaks, but some of my cells do not contain information because a team might not play on that current day. How can I have a formula that will over look a blank cell, but still have it continue a streak?

Example

Cell A1. W
A2. L
A3. W
A4.
A5. W
A6. W

I should have a streak of 3 wins, but every time I try a formula it will only calculate it as 2 wins. How do I have a formula overlook the blank cell?

And then I need it to calculate the longest streak as well.


Any and all help would be greatly appreciated

Thank you,

Tim

 

[Marcelo Branco]

Technically A4 is not blank. W or L is determined by the score a a formula calculates if a W or an L is placed in that cell. Sorry I guess I should have included that before.

Could you, please, tell us what is the expected result (considering data sample in post #1 )?

M.
ps: Thank you Joe for helping out. I was thinking that the problem could be my English (not my native language). Sometimes I do not understand exactly what is wanted.

 

[HBTim67]

Formula reads this. =IF(J14=K14,"",IF(J14>K14,"W",IF(J14<K14,"L")))

I get this. #VALUE

 

[Joe4]

It looks like your formula is being cut off.
See this: Formulas Not Appearing Completely? Read This
and then try posting it again.

 

[HBTim67]

I am trying to calculate winning and losing streaks and the largest winning and losing streaks.

 

[HBTim67]

=if(J14=K14,"",IF(J14 > K14,"W",IF(J14 < K14,"L")))

 

[Joe4]

His formula still seems to work for me.
Are you sure that you have adjusted it correctly to reflect your actual range and you entered it using CTRL-SHIFT-ENTER and not just Enter?
If you still are getting the #VALUE error, please post your attempt to use his formula, exactly as you have modified it.
And do you have any errors (in any of the rows) being returned by the formula you posted above?

 

[HBTim67]

It works, I had 1 error in the very last cell.

Thank you for all your help.

Now can you tell me how to calculate the won/loss record for the last 10 games that are played.

Example: if a team has played 50 games so far how can I calculate just the last 10 that are played and continue that after every game that is played so that way I know what they have done during a 10 game stretch.

 

[Marcelo Branco]

Now can you tell me how to calculate the won/loss record for the last 10 games that are played.

Maybe something like this

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Game​

[/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[TD]

LastGames​

[/TD]
[TD]

DataFirstRow​

[/TD]
[TD]

RowFirstCell​

[/TD]
[TD]

DataLastRow​

[/TD]
[TD]

Formula​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD]

10​

[/TD]
[TD]

3​

[/TD]
[TD]

11​

[/TD]
[TD]

23​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

2​

[/TD]
[TD]

L​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

3​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

5​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

6​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

8​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

9​

[/TD]
[TD="bgcolor: #D9D9D9"]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

10​

[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

11​

[/TD]
[TD="bgcolor: #D9D9D9"]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

12​

[/TD]
[TD="bgcolor: #D9D9D9"]

L​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

13​

[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

14​

[/TD]
[TD="bgcolor: #D9D9D9"]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

15​

[/TD]
[TD="bgcolor: #D9D9D9"]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

16​

[/TD]
[TD="bgcolor: #D9D9D9"]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

17​

[/TD]
[TD="bgcolor: #D9D9D9"]

L​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

18​

[/TD]
[TD="bgcolor: #D9D9D9"]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD]

19​

[/TD]
[TD="bgcolor: #D9D9D9"]

L​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD]

20​

[/TD]
[TD="bgcolor: #D9D9D9"]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD]

21​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Given values
10 in D3
3 in E3
23 in G3

Array formula in F3
=E3-1+MATCH(COUNTIF(B3:B23,"?*")-D3+1,COUNTIF(OFFSET(B3,,,ROW(B3:B23)-ROW(B3)+1),"?*"),0)
Ctrl+Shift+Enter

Array formula in H3
=MAX(FREQUENCY(IF(INDEX(B:B,F3):INDEX(A:A,G3)="W",ROW(INDEX(B:B,F3):INDEX(B:B,G3))),IF(INDEX(B:B,F3):INDEX(B:B,G3)="L",ROW(INDEX(B:B,F3):INDEX(B:B,G3)))))
Ctrl+Shift+enter

M.

 

[Marcelo Branco]

Sorry, the formula in H3 (result) has a typo.
Correct version
=MAX(FREQUENCY(IF(INDEX(B:B,F3):INDEX(B:B,G3)="W",ROW(INDEX(B:B,F3):INDEX(B:B,G3))),IF(INDEX(B:B,F3):INDEX(B:B,G3)="L",ROW(INDEX(B:B,F3):INDEX(B:B,G3)))))
Ctrl+Shift+Enter

M.

 

[HBTim67]

The formula in F3 works, but the formula in H3 doesn't calculate correctly.