TRUE/FALSE Values Based on Contiguous Cells (Interval)

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
72
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
72
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
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)
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,767
A possible 2-formula solution:

ABCDEFGH
1interval4
27TRUEFALSE1FALSE
31FALSEFALSE0FALSE
410TRUETRUE1TRUE
512TRUETRUE2TRUE
615TRUETRUE3TRUE
716TRUETRUE4TRUE
82TRUEFALSE0FALSE
91FALSEFALSE0FALSE
100TRUEFALS
E
1FALSE
111FALSEFALSE0FALSE
12

<tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
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

Board Regular
Joined
Jul 24, 2013
Messages
72
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,767
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:

ABCDEF
1interval4
27TRUE1FALSE
31FALSE0FALSE
410TRUE1TRUE
512TRUE2TRUE
615TRUE3TRUE
716TRUE4TRUE
82TRUE0FALSE
91FALSE0FALSE
100TRUE1FALSE
111FALSE0FALSE
12

<tbody>
</tbody>


Worksheet Formulas
CellFormula
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,368
Messages
5,468,213
Members
406,573
Latest member
nasirpm

This Week's Hot Topics

Top