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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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:
Upvote 0
If you look at column L you will see blanks because the team did not play during that time.
imagejpeg
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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