Calculating streaks when some cells are blank

HBTim67

New Member
Joined
Aug 10, 2017
Messages
20
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
 
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.
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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?
 
Upvote 0
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.
 
Upvote 0
Now can you tell me how to calculate the won/loss record for the last 10 games that are played.

Maybe something like this

A
B
C
D
E
F
G
H
1
2
Game​
Result​
LastGames​
DataFirstRow​
RowFirstCell​
DataLastRow​
Formula​
3
1​
W​
10​
3​
11​
23​
3​
4
2​
L​
5
3​
W​
6
4​
7
5​
W​
8
6​
W​
9
7​
10
8​
W​
11
9​
W​
12
10​
13
11​
W​
14
12​
L​
15
13​
16
14​
W​
17
15​
W​
18
16​
W​
19
17​
L​
20
18​
W​
21
19​
L​
22
20​
W​
23
21​

<tbody>
</tbody>


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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top