How to force intermediate FALSE values to TRUE with a condition

[gschmidt]

Hi,

I have a working formula to range Chainages:
1. It will check for each Chainage* (Column D) value IF it is valid TRUE/FALSE (Column N)....the OR formula is shown in the fx bar
2. AND lists all consecutive TRUE chainages where they Start (Column P) and End (Column Q)
Formula for column P:

=IFERROR(INDEX($D$4:$D$58653,AGGREGATE(15,6,(ROW($D$4:$D$58653)-ROW($D$4)+1)/($N$3:$N$58652=FALSE)/($N$4:$N$58653=TRUE),ROWS(P$4:P4))),"")

Formula for column Q:

=IFERROR(INDEX($D$4:$D$58653,AGGREGATE(15,6,(ROW($D$4:$D$58653)-ROW($D$4)+1)/($N$3:$N$58652=TRUE)/($N$4:$N$58653<>TRUE),ROWS(Q$4:Q4))-1),"")

But as you can see in the picture there are some FALSE cells which split 2 larger Ranges of TRUE
Is it possible to force those FALSE values into TRUE with a given Chainage gap value (D2)? e.g.:

IF D2=0 THEN nothing changes in column N
IF D2=2 THEN 2 consecutive FALSE values (enclosed between TRUE values) are changed to TRUE
IF D2=4 THEN 4 consecutive FALSE values (enclosed between TRUE values) are changed to TRUE


Notes:

• Chainage is the horizontal distance as measured along a combination of curves and straight lines (curvilinear) between two points
• In this example each value of a column D cell is +1, but it can also be +2 or +5

 

[jasonb75]

IF D2=4 THEN 4 consecutive FALSE values (enclosed between TRUE values) are changed to TRUE

Should that be exactly 4 consecutive FALSE values, or up to 4?

Also, should there be a minimum number of consecutive TRUE's between FALSE groups. For example, if you had 4 consecutive FALSE, 1 TRUE, another 4 consecutive FALSE, should both FALSE sets be changed to TRUE?

What about smaller FALSE groups? (depending on your answers to the first 2 points, this may be irrelevant) If you had 4 in D2, with something like TRUE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, TRUE in column N, how should that be processed?

 

[gschmidt]

Should that be exactly 4 consecutive FALSE values, or up to 4?

Also, should there be a minimum number of consecutive TRUE's between FALSE groups. For example, if you had 4 consecutive FALSE, 1 TRUE, another 4 consecutive FALSE, should both FALSE sets be changed to TRUE?

What about smaller FALSE groups? (depending on your answers to the first 2 points, this may be irrelevant) If you had 4 in D2, with something like TRUE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, TRUE in column N, how should that be processed?

Sorry, your'e right, it should be up to 4....I couldn't modify the post after 10min anymore.

Yes also consectutive TRUE's encapsulated between FALSE groups (did'nt think of that..thanx!)

The TRUE's are more important than the FALSE...so in case of:
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE
The 3x FALSE and 2x FALSE between the TRUE's must become TRUE

Only incase of:
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,
The TRUE's must be FALSE

 

[jasonb75]

Looking at the last part, should we say that consecutive TRUE's less than D2 should be converted to FALSE?

I think this is going to be a bit of a challenge, but first theory is to convert the <=D2 consecutive FALSE's to TRUE, then look for any remaining short runs of TRUE to convert to FALSE. We need a hard fast rule for deciding which TRUE's to change to FALSE though.

 

[gschmidt]

Looking at the last part, should we say that consecutive TRUE's less than D2 should be converted to FALSE?

I think this is going to be a bit of a challenge, but first theory is to convert the <=D2 consecutive FALSE's to TRUE, then look for any remaining short runs of TRUE to convert to FALSE. We need a hard fast rule for deciding which TRUE's to change to FALSE though.

1. Yes
   
   
2. Since this probably will be another TRUE/FALSE column, I would say that before/after a small cluster of TRUE's an equal given number (set in a fixed cell) of cells must be FALSE.
   This excel sheet will be used to create (in other software) polygons around 3d-models to dredge sand at certain locations along a route (offshore).
   The Chainage value is the distance of the route starting at 0. In the example picture the Chainage starts at 37700, which is the location after 37.7km from start.
   It is also possible that the Chainage step is not 1 but 2...So the given number of cells to look for represent a distance.
   This way the user can determine which distance is suitable to remove too small dredging areas (TRUE's)
   
   I agree that this is a challenge...otherwise I wouldn't ask advice
   

 

[jasonb75]

I agree that this is a challenge...otherwise I wouldn't ask advice

Fair point, but there are often times when there is a simple solution that the person asking the question doesn't know about.

With the TRUE / FALSE patterns in column N, adding this formula to column O appears to be converting the small groups of FALSE to TRUE correctly.

** You may need to use Ctrl Shift Enter to array confirm this in Excel 2016 **

=IF(N4=FALSE,(IFERROR(MATCH(TRUE,N5:N$58654,0),ROWS(N5:N$58654))+IFERROR(ROWS(N$4:N4)-MATCH(2,1/N$4:N4),MAX($D$2+1,ROWS(N$4:N4)))-1)<=$D$2,N4)

I'm still working on a second formula to convert the too small areas of TRUE to FALSE, something is not quite working as planned when I reverse the logic of the first formula.

 

[jasonb75]

Following up, moving the start / end chain formulas in columns P and Q to the right, then entering this formula into column P along with the one from above in column O appears to be working as needed.

=IF(O4=TRUE,(IFERROR(MATCH(FALSE,O5:O$58654,0),ROWS(O5:O$58654))+IFERROR(ROWS(O$4:O4)-MATCH(2,1/(O$4:O4=FALSE)),MAX($D$2+1,ROWS(O$4:O4)))-1)>$D$2,O4)

I've only tested with a step of 1 so far, in theory using a value of gap/step in the formulas in place of the gap value in D2 should work.

It is rare that a formula like this works perfectly as required on the first attempt but hopefully it will be somewhere close.