TRUE/FALSE Values Based on Contiguous Cells (Interval)

R

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
A possible 2-formula solution:

ABCDEFGH
interval
FALSE

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALS
E[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(B2=FALSE,0,IF(D1=$H$1,0,D1+1))[/TD]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=IF(AND(B2=TRUE,MAX(OFFSET(D2,0,0,$H$1))=$H$1),TRUE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
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
interval
15
FALSE

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas
[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(B2=FALSE,0,IF(C1=$F$1,0,C1+1))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(AND(B2=TRUE,MAX(OFFSET(C2,0,0,$F$1))=$F$1),TRUE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
IF CELL CONTAINS COMBINATION OF TWO SPECIFIC TEXT VALUES, RETURN TRUE OR FALSE
Replies
3
Views
401
JTH94BT
J
T
  • Question Question
Dynamic Data Validation
Replies
2
Views
106
thbutterflycollector_
T
Z
  • Solved
Basic IF & IF/AND Not Working…
Replies
2
Views
273
zero269
Z
ibmy
  • Question Question
How to structure the formula properly?
Replies
2
Views
243
ibmy
ibmy
T
  • Solved
Two Value if true, one value if false
Replies
5
Views
397
Joe4
Joe4

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top