How to automatically change the range of a function based on the system it belongs too.

[rgrocks]

I want to determine the MAX and MIN in a confined system. I want to be able to drag and drop a formula whose range will only correspond to the elevations within the system. In the example below the Low and High Value in the system will be calculated via the max/min formula within a range. How do I confine my range to just stay within the boundaries of "SYSTEM 1", "SYSTEM 2", "SYSTEM 3" without actually manually changing the range for each system. It's impractical for me to manual change the range for each system because I have rows being added and removed all the time which I'm sure will cause errors. I'd like the formula to cover the range of elevations in the sheet (over 20k lines) but only find the max and min values separately for each system. That means in this example, three systems will have three max values and three low values (I have over a hundred systems in my file and they aren't named as neatly as SYSTEM-#.).


Please help!




My table should look something like this

SYSTEM	LOW VALUE IN SYSTEM	HIGH VALUE IN SYSTEM	ELEVATION
SYSTEM 1	1	10	1
SYSTEM 1	1	10	5
SYSTEM 1	1	10	3
SYSTEM 1	1	10	10
SYSTEM 1	1	10	4
SYSTEM 2	14	33	14
SYSTEM 2	14	33	22
SYSTEM 2	14	33	30
SYSTEM 2	14	33	15
SYSTEM 2	14	33	33
SYSTEM 3	6	16	6
SYSTEM 3	6	16	6
SYSTEM 3	6	16	16
SYSTEM 3	6	16	10
SYSTEM 3	6	16	15

[djapigo]

Hi rgrocks,

I had to use arrays to solve this...
For B2: =MIN(IF(($A$2:$A$16=$A2),($A$2:$A$16=$A2)*$D$2:$D$16))
For C2: =MAX(($A$2:$A$16=$A2)*$D$2:$D$16)

You must use array-enter (CTRL-SHIFT-ENTER), not just ENTER... this will give you these curly brackets {} which will tell you that the array is working...

After copy in cells B2 and C2 and using array-enter, then you copy the formulas to the rest of the columns...

Let me know if this doesn't work...

Dennis

[FDibbins]

Try this.

In E2 downwards, enter the "systems" that you have (you cpuld get these by using advanced filters if tere are that many)
then in F2 copy this array down,,,
=MIN(IF($A$2:$A$16=$E2,$B$2:$B$16))
and in F2, copy this array down...
=MAX(IF($A$2:$A$20=$E2,$C$2:$C$20))
Array formulas must be entered using CTRL SHIFT enter, not just entetr

[rgrocks]

Try this.

In E2 downwards, enter the "systems" that you have (you cpuld get these by using advanced filters if tere are that many)
then in F2 copy this array down,,,
=MIN(IF($A$2:$A$16=$E2,$B$2:$B$16))
and in F2, copy this array down...
=MAX(IF($A$2:$A$20=$E2,$C$2:$C$20))
Array formulas must be entered using CTRL SHIFT enter, not just entetr

This doesn't check for the Min or Max within the set of range within Column D.

Column B and Column C are the columns I want to create with the formula. Essentially the formula should check the range within Column D and output the Low/Min and the High/Max value within the range that is covered by System 1, System 2, System 3, System 4 separately.

The Formula given looks like it finds the Min and Max if the System name matches the system name in another location in the sheet. Essentially that would give me two columns with the exact same info, the system name. The Formula doesnt take into account a change in system name. If the system name doesnt match,

[FDibbins]

upload a sample workbook

[rgrocks]

upload a sample workbook

Sample Work Book Determining Highs and Lows.xlsx

I tried to label the top with what I am trying to do. Basically I'm trying to find the high's and low's in the elevation for each system. Each version of me attempting it has a different level of sophistication in the formula. The part I am having most trouble with is my Column with the RED header.

I'm trying to figure out the MAX and MIN Values independent of each system. I want the column to only attribute a MAX and MIN value to row's with "pipeline" in the FEATURE CODE. meaning if their is a feature code as "tree" and its elevation is clearly the highest. It wont be attributed towards the calculating of MAX because it doesn't contain the word "pipeline"

Aside from that, I want the formula to calculate the Highs and Lows for each system. Meaning that when I drag the formula down the column, it will automatically know that system 2 has a different range to look for the highs and lows. similarly with system 3 and 4.

Finally if the feature code doesnt contain the word "pipeline" I want it to display "Not Pipe"

The current array I'm working with is

=IF(OR($B12="Pipeline",$B12="Pipeline Valve")=TRUE, MAX(IF($A$12:$A$2531='System Names'!$A3,IFERROR(SEARCH("pipeline",$B$12:$B$883),FALSE),$F$12:$F$883)),"Not Pipe")


But its not giving me what I want. Please have a look and help me.

[Pepe Le Mokko]

Quoting entire posts is not only useless, but clutters the thread. Please avoid in the future

[rgrocks]

After tinkering around with all the information I have, I was able to solve the problem. Thanks!

For those wondering how I did it. Here is my Min Equation

array

=IF(OR($B12="Pipeline",$B12="Pipeline Valve")=TRUE,MIN(IF($A$12:$A$2531='System Names'!$A3,IF(IFERROR(SEARCH("pipeline",$B$12:$B$2531),FALSE),$F$12:$F$2531))),"Not Pipe")

THANKS FOR ALL THE HELP ON THIS ONE

[FDibbins]

glad you managed to get it solved and thanks for the update