Set Theory Question...Overlapping line ranges

[feetimo]

I need some help...I've simplified the spreadsheet down to a couple columns, as follows. Essentially, the spreadsheet defines a continuous line in the "X" dimension (imagine if you are plotting this) by giving several points.

So, think of a "Zone" as a continuous line, defined by the points given...Zone 1 below would start at 44.20 and end at 51.40 (there are some points given between the MIN and MAX within the zone, but these may be ignored for this question). The MIN and MAX don't always come at the beginning or end row of the zone.

Some of these zones will intersect/overlap/be completely "encapsulated" by other zones. What I want to do is define these regions... in the example below, I want to define area "A" (or whatever we want to call it) as starting at 5.0 (minimum edge of Zone 1) and ending at 25.0 (maximum edge of Zone 6). Area "B" would be starting at -25.0 and ending at -2.0...in this case, just Zone 5 defines the area (though Zone 4 is "encapsulated" within it).

And, of course, the data is changeable, so the solution must be dynamic as the data changes. Some data sets may have only one Area defined by the data, while some may have 2, 3, 4, 5 non-Contiguous Areas defined by the data.




Bottom Line Questions:

• How many seperate (non-contiguous) Areas are there?
• What is the maximum and minimum values for each zone?

Col A Col B
Zone Position
1 44.20
1 44.20
1 44.92
1 50.68
1 51.40
1 51.40
2 43.00
2 43.00
2 43.00
2 42.56
2 41.24
2 40.80
2 40.80
3 19.80
3 19.80
3 19.80
3 19.50
3 18.90
3 18.60
3 18.60
4 27.60
4 27.60
4 27.60
4 28.49
4 31.15
4 32.04
4 32.04
5 37.04
5 37.04
5 37.04
5 37.94
5 56.76
5 57.65
5 57.65
6 62.65
6 62.65
6 62.65
6 63.34
6 68.17
6 68.85
6 68.85

 

[Marcelo Branco]

Tim,

With your last data-sample

Zone Start Dist End Dist
1 -10 -14
2 -8 -5
3 -1 -6
4 0 3
5 4 8
6 12 9
7 18 14
8 -15 -18

using my formulas i got this in the Last Step

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=64>Areas</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>Lower</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>Upper</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>AreaA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>-18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>-15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>AreaB</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>-14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>-10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>AreaC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>-8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>-1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>AreaD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>AreaE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>AreaF</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>AreaG</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>18</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>AreaH</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>

that seems correct to me...

All you have to do is correct the ranges (extend) to

B2
=IF(ROWS($B$2:B2)>Sheet1!$N$2,"",SMALL(Sheet1!$K$2:$K$9,SUM(COUNTIF($B$1:B1,Sheet1!$K$2:$K$9))+1))

Ctrl+Shift+Enter
copy down

C2
=IF(ROWS($C$2:C2)>Sheet1!$N$2,"",MAX(IF(Sheet1!$K$2:$K$9=B2,Sheet1!$L$2:$L$9)))

Ctrl+Shift+Enter
copy down

M.

M.

 

[Marcelo Branco]

To avoid the tedious task of adjusting the ranges everytime the data changes, try these formulas in Last Step

B2
=IF(ROWS($B$2:B2)>Sheet1!$N$2,"",SMALL(IF(Sheet1!$K$2:$K$100<>"",Sheet1!$K$2:$K$100),SUM(COUNTIF($B$1:B1,IF(Sheet1!$K$2:$K$100<>"",Sheet1!$K$2:$K$100)))+1))

C2
=IF(ROWS($C$2:C2)>Sheet1!$N$2,"",MAX(IF(Sheet1!$K$2:$K$100=B2,Sheet1!$L$2:$L$100)))

both Ctrl+Shift+Enter
copy down

M.

 

[feetimo]

Thanks!!!The last post is what I was getting at...the data may use all 8 areas, or just two, or anything in-between. I'll give the new formulas later today...thanks again!

-Tim

 

[Marcelo Branco]

Thanks!!!The last post is what I was getting at...the data may use all 8 areas, or just two, or anything in-between. I'll give the new formulas later today...thanks again!

-Tim

You are welcome and tks for the feedback

M.