Counting Consecutive Texts

[nicofernandez00]

Hello,

I really need help figuring out the formula for an assignment. On my spreadsheet, I have the monthly status of projects in action. From cells H28-S28, the cells are labeled either "on track", "off-track", "at risk" and "completed". I want to count the amount of consecutive times the projects were labeled "off-track" or "at risk" consecutively.

For instance:

On Track

On Track

On Track

On Track

On Track

At Risk

At Risk

At Risk

At Risk

Off Track

Off track

Off Track

<TBODY>
</TBODY>

The answer here would be 7 because "at risk" and "off track" were the consecutive statuses for seven months straight.

Please help!

 

[nicofernandez00]

Re: Counting Consecutive Text Values

Sure.

AT RISK AT RISK AT RISK OFF TRACK ON TRACK ON TRACK ON TRACK ON TRACK ON TRACK ON TRACK ON TRACK ON TRACK=4
AT RISK AT RISK AT RISK AT RISK ON TRACK ON TRACK ON TRACK ON TRACK ON TRACK ON TRACK ON TRACK ON TRACK=0
AT RISK AT RISK AT RISK ON TRACK ON TRACK ON TRACK ON OFF TRACK TRACK ON TRACK ON TRACK ON TRACK ON TRACK=0
AT RISK AT RISK AT RISK OFF TRACK OFF TRACK ON TRACK ON TRACK ON TRACK ON TRACK ON TRACK ON TRACK ON TRACK=5
OFF TRACK OFF TRACK OFF TRACK ON TRACK ON TRACK ON TRACK ON TRACK ON TRACK ON AT RISK AT RISK ON TRACK ON TRACK=0

 

[XOR LX]

Re: Counting Consecutive Text Values

Interesting. You've only given examples in which there is precisely one such occurrence of a consecutive string? Will that always be the case?

I notice that you never replied to Domenic's query along the same lines back in post #12?

Regards

 

[XOR LX]

Re: Counting Consecutive Text Values

Also, in all your examples, where both AT RISK and OFF TRACK occur in a consecutive string, they are never interwoven, and what's more the string of AT RISKs always precedes the string of OFF TRACKs.

So, for example, you have given no examples such as:

AT RISK OFF TRACK AT RISK
OFF TRACK OFF TRACK AT RISK AT RISK

Can you confirm whether this also is always the case?

Regards

 

[nicofernandez00]

Re: Counting Consecutive Text Values

Interesting. You've only given examples in which there is precisely one such occurrence of a consecutive string? Will that always be the case?

I notice that you never replied to Domenic's query along the same lines back in post #12?

Regards

There are no such occurrences on my spreadsheet. However, that is a very interesting case. The formula gave me a result of 4.

 

[nicofernandez00]

Re: Counting Consecutive Text Values

Also, in all your examples, where both AT RISK and OFF TRACK occur in a consecutive string, they are never interwoven, and what's more the string of AT RISKs always precedes the string of OFF TRACKs.

So, for example, you have given no examples such as:

AT RISK OFF TRACK AT RISK
OFF TRACK OFF TRACK AT RISK AT RISK

Can you confirm whether this also is always the case?

Regards

There are instances where they are interwoven. Based of your examples, the results would be 3 and 4, respectively.

 

[XOR LX]

Re: Counting Consecutive Text Values

Apologies. I was hoping that out of those half a dozen lines I requested you'd be able to give a comprehensive covering of different types of possible strings.

Instead they're all pretty much of the same type, as I mentioned, so I'm still unsure as to where each of these categories can occur, in which order, and next to which other categories.

Regards

 

[nicofernandez00]

Re: Counting Consecutive Text Values

Apologies. I was hoping that out of those half a dozen lines I requested you'd be able to give a comprehensive covering of different types of possible strings.

Instead they're all pretty much of the same type, as I mentioned, so I'm still unsure as to where each of these categories can occur, in which order, and next to which other categories.

Regards

The formula works for when AT RISK and OFF TRACK are consecutive which is what I needed. However, say I have a row full of 8 "ON TRACK" and 4 "AT RISK"... when the formula is applied the result should be zero because we are only looking for when AT RISK and OFF-TRACK are next to one another. We do not want when at risk is independently consecutive with other at risk. The same goes for off-track. I am really sorry this can get confusing. I really appreciate your help.

 

[Redwolfx]

Re: Counting Consecutive Text Values

Try This

=IFERROR(IF(OR(INDEX(H28:R28,1,MATCH("Off Track",I28:S28,0))="At Risk",INDEX(H28:R28,1,MATCH("At Risk",I28:S28,0))="Off Track"),MAX(FREQUENCY(IF(SUBSTITUTE(PROPER(H28:S28),"Off Track","At Risk")="At Risk",COLUMN(H28:S28)),IF(SUBSTITUTE(PROPER(H28:S28),"Off Track","At Risk")<>"At Risk",COLUMN(H28:S28)))),0),0)

Confirm with Ctrl+Shift+Enter

Drag Down

 

[nicofernandez00]

Re: Counting Consecutive Text Values

try this

=iferror(if(or(index(h28:r28,1,match("off track",i28:s28,0))="at risk",index(h28:r28,1,match("at risk",i28:s28,0))="off track"),max(frequency(if(substitute(proper(h28:s28),"off track","at risk")="at risk",column(h28:s28)),if(substitute(proper(h28:s28),"off track","at risk")<>"at risk",column(h28:s28)))),0),0)

confirm with ctrl+shift+enter

drag down

this worked!!! Thank you all a ton!! Great help!!!

 

[XOR LX]

Re: Counting Consecutive Text Values

The formula works for when AT RISK and OFF TRACK are consecutive which is what I needed. However, say I have a row full of 8 "ON TRACK" and 4 "AT RISK"... when the formula is applied the result should be zero because we are only looking for when AT RISK and OFF-TRACK are next to one another. We do not want when at risk is independently consecutive with other at risk. The same goes for off-track. I am really sorry this can get confusing. I really appreciate your help.

So let me give you my interpretation as a series of logical steps and if you could kindly tell me if this would hold in all cases:

1) Examine all pairs of consecutive cells in the range and locate the first pair which comprises one cell containing AT RISK and the other OFF TRACK, in either order.

2) Taking the rightmost of these cells, find the first cell to the right which contains neither AT RISK nor OFF TRACK

3) Taking the leftmost of these cells, find the first cell to the left which contains neither AT RISK nor OFF TRACK

4) Count the number of cells in between those found in 2) and 3) above, exclusive

Edit: looks like it's just been solved! Never mind!

Regards