# Formula to Calculate Longest Streak of Certain String

#### rumshar

##### Member
Hello Excel Experts,
Can you guys help me with the formula to calculate the longest streak of a string in a range? Example file is being attached. Thank you.

#### Attachments

• 16.1 KB Views: 9

#### AliGW

##### Active Member
Are you happy with helper columns?

#### rumshar

##### Member
Are you happy with helper columns?

#### AliGW

##### Active Member
Create two new columns - Emp A Count and Emp B Count.

Copy this into D2 and drag copy across and down:

=IF(AND(B2="P",B2=B1),D1+1,IF(B2="P",1,""))

For Emp A: =MAX(Table1[Emp A Count])
For Emp B: =MAX(Table1[Emp B Count])

#### GraH - Guido

##### Well-Known Member
Beaten by Ali in speed.
I got other helper columns, but same idea all together.
add columns P Streak A and P Streak B.
formulae being:
=([@[Emp A]]="P")*(N(D1)+1)
=([@[Emp B]]="P")*(N(E1)+1)

PS: @ AliGW, congrats for being moderator on the other forum. Impressive figures over there, +12K messages (wow!).

#### Attachments

• 28.1 KB Views: 2

#### AliGW

##### Active Member
My file attached.

Thanks. I don't pay much attention to post counts, except that I know I've posted over 15000 times!!!

Being a moderator has its drawbacks.

#### Attachments

• 25.7 KB Views: 3

#### rumshar

##### Member
Create two new columns - Emp A Count and Emp B Count.

Copy this into D2 and drag copy across and down:

=IF(AND(B2="P",B2=B1),D1+1,IF(B2="P",1,""))

For Emp A: =MAX(Table1[Emp A Count])
For Emp B: =MAX(Table1[Emp B Count])
Thanks Ali...is there a way to do it without helper columns too? If not no problem.

#### AliGW

##### Active Member
You can hide the helper columns - what's your perceived problem with them?

#### bosco_yip

##### Excel Ninja
Thanks Ali...is there a way to do it without helper columns too? If not no problem.
Formula without helper columns

1] Longest streak of 'P' for Emp A :

=MAX(FREQUENCY(ROW(Table1[Emp A]),(Table1[Emp A]<>"P")*ROW(Table1[Emp A])))-1

2] Longest streak of 'P' for Emp B :

=MAX(FREQUENCY(ROW(Table1[Emp B]),(Table1[Emp B]<>"P")*ROW(Table1[Emp B])))-1

Regards
Bosco

#### AliGW

##### Active Member
Try this:

=MAX(FREQUENCY(IF(Table1[Emp A]="P",ROW(2:441)),IF(Table1[Emp A]<>"P",ROW(Table1[Emp A]))))

=MAX(FREQUENCY(IF(Table1[Emp B]="P",ROW(2:441)),IF(Table1[Emp B]<>"P",ROW(Table1[Emp B]))))

Both entered as array formulae (C+S+E).

#### rumshar

##### Member
Thank you...the case can be closed..!!!!

You're welcome!

#### Peter Bartholomew

##### Well-Known Member
I have just noticed that I have such a spreadsheet lying around from another discussion. From my recollection it uses two arrays, offset by one cell, to identify start and end indices by comparing each cell with its predecessor. SMALL is then used to compact the lists, so allowing the 'starts' to be subtracted from the 'ends'.

I am not suggesting that you adopt this method; just that the approach is not mainstream and may be of some interest.

#### Attachments

• 12.4 KB Views: 6

#### Peter Bartholomew

##### Well-Known Member
@AliGW

Hi Ali, you are perfectly correct and I do tend to use INDEX:INDEX in preference to OFFSET for that reason. There are times, though, when you would never see the difference or, indeed, know that a calculation has taken place. In that case one might as well use the clearest syntax rather than the most efficient calculation?

Thought for discussion. Now that we have Power Query and Power Pivot, the workbooks that were forever on the verge of collapse under the weight of their data (Calculation Manual) should have migrated away from formula-based manipulation. This means that, for other classes of workbook (model building in particular), optimisation for speed is no longer of primary importance?

This is simply a thought and I would not necessarily claim it to be true.

#### AliGW

##### Active Member
Learning Power Query (or being in the process of so-doing, to be more precise) is one of the best things I ever undertook. It has been a revelation.

#### GraH - Guido

##### Well-Known Member
@AliGW

Thought for discussion. Now that we have Power Query and Power Pivot, the workbooks that were forever on the verge of collapse under the weight of their data (Calculation Manual) should have migrated away from formula-based manipulation. This means that, for other classes of workbook (model building in particular), optimisation for speed is no longer of primary importance?

This is simply a thought and I would not necessarily claim it to be true.
Hi Peter, continuing on this fine thought of moving towards PQ/PP, I would say that the-need-for-speed is always of essence.
If your PQ will take forever, it will kill the user experience as quickly as a badly designed workbook would do. To make PP go slow, you must try very hard I think. But badly designed DAX will have a negative impact too.
Rob Collie calls it the 3s rule. If a calculation takes more then 3 seconds, he advises to rethink your DAX formula. Put that into perspective of those "slow" workbooks we all have worked with or even wurst made them ourselves.
Having said this, you do have a fair point by putting clarity above efficiency when efficiency is of lesser importance. I try to pursue both and I often find that in an effort to make things clear, the result is more efficient or vice versa.

#### Peter Bartholomew

##### Well-Known Member
Just one more thought; just stirring really. Power Query and Power Pivot do not seem to need any notation for addressing individual cells / values (workarounds with conditional formulas perhaps). Whatever was the justification for introducing such notation for spreadsheets?