Checking if a sequence of values is met

[Ride The Lightning]

Hi

I have a column of data with the values either True or False. I want to set up a formula that calculates if "True" appears 5 times in a sequence. I have tried the IF function and tried using it in an array, however, the formula doesn't account for the whole range.

Any pointers of what I could use would be much appreciated.

Cheers

RTL

 

[RoryA]

Do you mean exactly 5 times in sequence, or at least 5 times?

 

[mouse88]

This should do the trick. Just change the range to whatever you need it to be.

=IF(COUNTIF(A1:A10, "=TRUE") = 5, TRUE, FALSE)

 

[Ride The Lightning]

Do you mean exactly 5 times in sequence, or at least 5 times?

Yes, exactly 5 times in a sequence.

 

[mouse88]

If you did want the value to be true if it is more than 5 then just change it to:

=IF(COUNTIF(A1:A10, "=TRUE") > 5, TRUE, FALSE)

 

[Ride The Lightning]

This should do the trick. Just change the range to whatever you need it to be.

=IF(COUNTIF(A1:A10, "=TRUE") = 5, TRUE, FALSE)

That seems to do the trick, thank you.

 

[RoryA]

COUNTIF will only tell you if TRUE appears 5 times somewhere in the list, not in sequence.

 

[Ride The Lightning]

COUNTIF will only tell you if TRUE appears 5 times somewhere in the list, not in sequence.

Thanks for pointing that out. Any ideas for how to calculate it for a sequence?

 

[Marcelo Branco]

Hi,

Assuming True and False are boolean values (not text) in range A1:A10 the array-formula below checks if exactly 5 consecutives True exist

=IF(MAX(FREQUENCY(IF(A1:A10,ROW(A1:A10)),IF(NOT(A1:A10),ROW(A1:A10))))=5,"Yes","No")

Ctrl+Shift+Enter

HTH

M.

 

[Marcelo Branco]

oops...

My formula has a flaw, if exists 6 consecutives True and also 5.

Wait, need to re-think

M.