Formula to Calculate Longest Streak of Certain String
- Thread starter rumshar
- Start date
Yeah Sure...Please go ahead. Thank you.
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)
add total max below table.
PS: @ AliGW, congrats for being moderator on the other forum. Impressive figures over there, +12K messages (wow!).
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!).
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
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.
I am not suggesting that you adopt this method; just that the approach is not mainstream and may be of some interest.
AliGW
Active Member
If volatile functions like OFFSET can be avoided, then it's best to do so.
Here's some information: http://www.decisionmodels.com/calcsecretsi.htm
Here's some information: http://www.decisionmodels.com/calcsecretsi.htm
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.
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.
GraH - Guido
Well-Known Member
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?