Calculate ranges based on 2 columns

[gschmidt]

Hi,

I have 2 columns with data:

• Column A has chainage (CH) values, which are ascending consecutive numbers, and not always start with chainage 1
• Column B has FALSE/TRUE values

I want to determine the values of all the Start (see column D) and End chainages (see column E), for the rows where the value of column B between Start and End is TRUE.
See example picture.

This is an example of 30 lines for column A/B, but the amount of rows and the amount of TRUE/FALSE values may differ
So the amount of [D] Start CH and [E] End CH rows also may differ

How do I do this in Excel?

 

[gschmidt]

You're col N values are text rather than boolean values.
Change the col N formula to

=OR(K4>=$L$2, L4>=$L$2, M4>=$L$2)

That is basically the same....thanx for the help

 

[Fluff]

You're welcome & thanks for the feedback.

 

[gschmidt]

You're welcome & thanks for the feedback.

Now that the formula is working I have 2 other questions about the formula.

1. Between Start CH and End CH is a gap of 3 (from 37712-37717 to 37720-37724), because 37720 - 37717 = 3.
   Which means 37718 and 37719 must have a FALSE boolean value.
   Is it possible to modify the formula to:
   IF the Gap between 2 consecutive ranges is smaller/or equal than a given number e.g. 3
   THEN the range becomes according to table: 37712-37784
   
   
   
   If this is possible I would feed it with a fixed cell value as in the example
   
   
   
   
   
   
2. Will the formula still work if the difference between two consecutive Chainage cells is not 1 but e.g. 2?
   e.g. 23400, 23402, 32404, etc.
   

 

[Fluff]

I'm afraid I have no idea how to do that. I suggest you start a new thread & see if anyone else has an idea.

 

[gschmidt]

I'm afraid I have no idea how to do that. I suggest you start a new thread & see if anyone else has an idea.

Okay, I will