Thanks:  0
Likes:  0

# Thread: Calculating streaks when some cells are blank

1. ## Calculating streaks when some cells are blank

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

2. ## Re: Calculating streaks when some cells are blank

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.

3. ## Re: Calculating streaks when some cells are blank

Thank you!

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

4. ## Re: Calculating streaks when some cells are blank

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

M.

5. ## Re: Calculating streaks when some cells are blank

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

6. ## Re: Calculating streaks when some cells are blank

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.

7. ## Re: Calculating streaks when some cells are blank

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.

8. ## Re: Calculating streaks when some cells are blank

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?

9. ## Re: Calculating streaks when some cells are blank

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.

10. ## Re: Calculating streaks when some cells are blank

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?