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
 

Some videos you may like

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
Joined
Aug 23, 2010
Messages
16,393
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
Joined
Aug 10, 2017
Messages
20
Thank you!

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

HBTim67

New Member
Joined
Aug 10, 2017
Messages
20

ADVERTISEMENT

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

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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
Joined
Aug 10, 2017
Messages
20

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
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 10, 2017
Messages
20
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
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top