• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to Calculate Longest Streak of Certain String

R

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

  • Attendance Dummy.xlsx
    16.1 KB · Views: 9
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])
 
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)

add total max below table.

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

Attachments

  • Attendance Dummy.xlsx
    28.1 KB · Views: 2
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

  • Attendance Dummy Streak AliGW.xlsx
    25.7 KB · Views: 3
AliGW said:
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])
Click to expand...
Thanks Ali...is there a way to do it without helper columns too? If not no problem.
 
rumshar said:
Thanks Ali...is there a way to do it without helper columns too? If not no problem.
Click to expand...
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
 
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).
 
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

  • temp - longest period of absence.xlsx
    12.4 KB · Views: 6
@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.
 
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. :)
 
Peter Bartholomew said:
@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.
Click to expand...

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.:eek:
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.
 
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?
 
You must log in or register to reply here.
Back
Top