TRUE/FALSE Values Based on Contiguous Cells (Interval)

[rkaczano]

Hi I need help with a conditional logic test that spans a range of predefined contiguous cells.

I have a column of random numbers in A1:A10. In B1:B10, in each row, I do a True/False Test to see if a number is Less Then or Equal to its equivalent (i.e. B1 = IF(3 is <=A1, TRUE, FALSE). So I have a bunch of random TRUE and FALSE items in Column B.

Now in column C I want to be able to test whether my TRUE results in Column B occur consecutively over a preset interval (range of cells). For example assume I have an interval or range input set to 4. So for example in C4 I want to be able to test whether the value in B4 is TRUE AND whether it is within a 4 cell range (interval) of values that are also TRUE. Effectively I want to identify if my TRUE is part of a collection of TRUEs.

So if you see below, I want to isolate the TRUE values in column B that are part of a 4 cell range (interval) AND are TRUE. In this example it would be the range B3:B6 that is the relevant range that conforms to the interval I have set up. It is a forward looking range so B7 would be excluded from that range. So C3:C6 would be TRUE and C7 would be FALSE. I am assuming the formula in C needs to incorporate an OFFSET function incorporating the interval and/or it needs to be an Array function.

Number Test = 3
Interval = 4

A1 7; B1 TRUE; C1 FALSE
A2 1; B2 FALSE; C2 FALSE
A3 10; B3 TRUE; C3 TRUE
A4 12; B4 TRUE; C4 TRUE
A5 15; B5 TRUE; C5 TRUE
A6 16; B6 TRUE; C6 TRUE
A7 2; B7 TRUE; C7 FALSE
A8 1; B8 FALSE; C8 FALSE
A9 0; B9 TRUE; C9 FALSE
A10 1; B10 FALSE; C10 FALSE

Any ideas?

Thanks in advance

 

[rkaczano]

No takers on this? Let me simplify. I have range of TRUE/FALSEs in column B. I want to reproduce those TRUE/FALSE in column C based on a constraint that says I can only have so many contiguous TRUEs. So for example, I can only have three TRUEs back-to-back. I want a formula in Column C that reads the raw data in Column B and makes the adjustment to the data based on the constraint I enter. Column B will have too many TRUEs. Column C will adjust (reduce) these.

I want the constraint to be a variable (input) that I can change.

Thanks again.

 

[Marcelo Branco]

See if this does what you need

1st step
Create a named constant vertical array
Formula > Names Manager > New
Name: arr
Refers to: ={-3;-2;-1;0}

2nd step
Array formula in C1 copied down
=OR(IFERROR((ROW(OFFSET(B1,N(arr),0))>=ROW(B$1))*(ROW(OFFSET(B1,N(arr),0))<=ROW(B$10)-3)*COUNTIF(OFFSET(B1,arr,0,4,1),TRUE),0)=4)
confirmed with Ctrl+Shift+Enter

M.

 

[Joyner]

Ok, more testing needed, but does this work?

=IF(B1=FALSE,FALSE,IFERROR(COUNTIFS(B1:OFFSET(B1,-4,0),TRUE)<=4,TRUE))

By the way this:

=IF(3 is <=A1, TRUE, FALSE)

Could just be this:

=3<=A1

Edit: I think I missed this part: TRUE AND whether it is within a 4 cell range (interval)

 

[Eric W]

A possible 2-formula solution:

	A	B	C	D	E	F	G	H
1							interval	4
2	7	TRUE	FALSE	1	FALSE
3	1	FALSE	FALSE	0	FALSE
4	10	TRUE	TRUE	1	TRUE
5	12	TRUE	TRUE	2	TRUE
6	15	TRUE	TRUE	3	TRUE
7	16	TRUE	TRUE	4	TRUE
8	2	TRUE	FALSE	0	FALSE
9	1	FALSE	FALSE	0	FALSE
10	0	TRUE	FALS E	1	FALSE
11	1	FALSE	FALSE	0	FALSE
12

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula D2 =IF(B2=FALSE,0,IF(D1=$H$1,0,D1+1)) E2 =IF(AND(B2=TRUE,MAX(OFFSET(D2,0,0,$H$1))=$H$1),TRUE) <tbody> </tbody>

<tbody>
</tbody>

 

[rkaczano]

For clarification, your two formula solution focuses on columns D and E. What is the formula are you using in Column C and how does this feed into Column D and E?

Thanks

 

[Eric W]

I just left in column C from your example to show that the values I generated in column E matched. Without that, it would look something like:

	A	B	C	D	E	F
1					interval	4
2	7	TRUE	1	FALSE
3	1	FALSE	0	FALSE
4	10	TRUE	1	TRUE
5	12	TRUE	2	TRUE
6	15	TRUE	3	TRUE
7	16	TRUE	4	TRUE
8	2	TRUE	0	FALSE
9	1	FALSE	0	FALSE
10	0	TRUE	1	FALSE
11	1	FALSE	0	FALSE
12

<tbody>
</tbody>

Worksheet Formulas Cell Formula C2 =IF(B2=FALSE,0,IF(C1=$F$1,0,C1+1)) D2 =IF(AND(B2=TRUE,MAX(OFFSET(C2,0,0,$F$1))=$F$1),TRUE) <tbody> </tbody>

<tbody>
</tbody>

Note that the formulas do require an empty row above the top line. Also, you can hide column C after you put the formulas in.

 

[rkaczano]

Yup that worked! Thanks again.