# Calculating streaks when some cells are blank

#### HBTim67

##### New Member
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

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Marcelo Branco

##### MrExcel MVP
Welcome to Mr Excel forum

To get the longest streak try this array formula
=MAX(FREQUENCY(IF(A1:A6="W",ROW(A1:A6)),IF(A1:A6="L",ROW(A1:A6))))
Ctrl+Shift+Enter

Hope this helps

M.

Last edited:

#### HBTim67

##### New Member
Thank you!

Do you have an answer to how I can calculate a streak incorporating blank cells?

#### Marcelo Branco

##### MrExcel MVP
Not sure i understand what you want. Could you provide an example?

M.

#### HBTim67

##### New Member

ADVERTISEMENT

If you look at column L you will see blanks because the team did not play during that time.

#### Marcelo Branco

##### MrExcel MVP
I can see this, but i don't understand what you mean by "streak incorporating blank cells".
Considering your data sample above what would be the desired/expected result?

M.

#### HBTim67

##### New Member

ADVERTISEMENT

I want it to be able to skip a blank cell and continue the streak. As of right now it counts blank cells as 0 and the streak ends when in all actuality no game was played that day.

#### Joe4

##### MrExcel MVP, Junior Admin
Marcelo's formula seems to work for me. Based on the example you posted in your first post, his formula returns 3, which appears correct to me.
What does it return for you?
If it returns something other than 3, are you sure that A4 is really blank?

#### HBTim67

##### New Member
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.

#### Joe4

##### MrExcel MVP, Junior Admin
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.
Please be sure to not leave out any important details!
People often have tendency to oversimplify things, and then find out that the proposed solutions don't work for them (because we are missing some key information).

So what exactly would be in the cell, other than W or L?
What is the exact formula?

Replies
3
Views
31
Replies
23
Views
2K
Replies
7
Views
535
Replies
1
Views
69
Replies
2
Views
369

Threads
1,109,013
Messages
5,526,270
Members
409,689
Latest member
martin_br