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]

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.

 

[HBTim67]

Thank you!

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

 

[Marcelo Branco]

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

M.

 

[HBTim67]

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

 

[Marcelo Branco]

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]

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]

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]

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]

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?