Help picking out Max and Min Values in a Column based on Criteria from Another Column.

[rgrocks]

Hey,

Attached is an example of my sheet.

I need help figuring out something pretty specific. I have a spreadsheet separated by systems. Within each system I want it to find me the Highest point in that system. However I only want it to count the values under the elevation tab if the feature is either a "Pipeline" or "Pipeline Valve".

Currently for my High Point Column, I have

=IF(OR($B12="Pipeline",$B12="Pipeline Valve")=TRUE,MAX($K$12:$K$2531),"Not Pipe")

Where the Column B will hold all the feature types and Column K holds all the elevation values. The problem with my formula right now is that it returns the MAX value even if the feature isn't a "Pipeline" or "Pipeline Valve".

Similar I have a function to find me the minimum value in the range in a separate column.

=IF(OR($B12="Pipeline",$B12="Pipeline Valve")=TRUE,MIN($F$12:$F$2531),"Not Pipe")


I want it to return the max and min value within the range only if that value corresponds to a "Pipeline" or "Pipeline Valve"


Other than manually changing the range for each system, I have no idea of telling Excel, now that your in system 2, redo the max and min check for the range only if it belongs to system 2 and only pull values if the feature is a pipeline or a pipeline valve. If you could help me with this portion as well It would greatly help me.



Kinda what I want to do

System	Feature	High Point	Elevation
1	Test Lead	Not Pipe	300
1	Pipeline	No	300
1	Pipeline Valve	No	400
1	Pipline	High Point	600
2	Test Lead	Not Pipe	300
2	Pipeline	No	300
2	Pipeline Valve	No	400
2	Pipline	High Point	600

Sample.xlsx

[FDibbins]

Hi and welcome to the forum

You didnt say where you wanted this, and also, I assume you mean max in column F, not K as in your formula above.

This is a bit messy because I dont seem to be able to use "Pipeline"&*"*.
=MAX(MAX(IF(B12:B2531="Pipeline Valve",F12:F2531,0)),MAX(IF(B12:B2531="Pipeline",F12:F2531,0)))

This is an array formula and must be entered using CTRL SHIFT enter, not just enter

[popipipo]

I dont seem to be able to use "Pipeline"&*"*.

This does not work for me too

A little bit less messy/less max:
=MAX(IF(B$12:B$2531="PIPELINE";F$12:F$2531);IF(B$12:B$2531="PIPELINE valve";F$12:F$2531))

[rgrocks]

Thanks ALOT

Where can I find a tutorial on Arrays. Thanks for the help. But I also wish to know How to come about this equation. Can you elaborate?

[Jacc]

Another solution here. I put those fomulas in separate cells, no need to redo the heavy calculations.

Please Login or Register  to view this content.

Array formula, has to be entered with Ctrl+Shift+Delete.

[rgrocks]

Hey,

Can you please go over logically whats going on in this equation? I tried to copy and paste the equation down the line as (using Ctrl+Shift+Enter)

=MAX(MAX(IF(B12:B2531="Pipeline Valve",F12:F2531,0)),MAX(IF(B12:B2531="Pipeline",F12:F2531,0)))

However the range kept changing as I went down the line. I then attempted to use this equation

=MAX(IF(B$12:B$2531="PIPELINE",;F$12:F$2531);IF(B$12:B$2531="PIPELINE valve";F$12:F$2531))

But It kept telling me "You cannot change part of an Array". I'm lost.



The reason I want this formula down the column, is because I have another Column "Low Point or High Point" That checks to see if the elevation on that row is the High Point or the Low Point. I have to determine, the High and Low points in every system. That's why I'd like the formula to check based on the range belonging to each system.


I then tried this Formula

=MAX(IF(B$12:B$2531="PIPELINE";F$12:F$2531);IF(B$12:B$2531="PIPELINE valve";F$12:F$2531))

But I keep getting an error that something is wrong with the formula and it highlights the first "PIPELINE" in the Formula.

[popipipo]

=MAX(IF(B$12:B$2531="PIPELINE";F$12:F$2531);IF(B$12:B$2531="PIPELINE valve";F$12:F$2531))

Change the ; in a ,
In holland we use ; and I forgot to change it.
What went wrong with the solution of Jacc (#5)

[FDibbins]

I would have thought that finding teh max of a range would be a 1-cell deal? I wasnt expecting that you would copy it down anywhere. Try deleting the formula completely, and then re-entering it

The way the forumla works is as follows...
=MAX( MAX(IF(B12:B2531="Pipeline Valve",F12:F2531,0)) , MAX(IF(B12:B2531="Pipeline",F12:F2531,0)))

working from inside...MAX(IF(B12:B2531="Pipeline Valve",F12:F2531,0))
this is testing column B for the value "Pipeline Valve", and then for only those values in B that match, calc the MAX in F.
As I said, I could not get the wild card * to work, so I used another of the above, based on "Pipeline"
MAX(IF(B12:B2531="Pipeline",F12:F2531,0)))

I then put both of them inside a MAX() to find which 1 of the 2 was greater

[rgrocks]

System	Feature	Low Point or High Point of PIPE	Lowest Elevation	Highest Elevation	Elevation
1	TEST LEAD	NOT PIPE	3	10	1
1	PIPE	LOW POINT	3	10	3
1	PIPE	NO	3	10	4
1	TREE	NOT PIPE	3	10	15
1	PIPE	HIGH POINT	3	10	10
2	TREE	NOT PIPE	6	33	3
2	PIPE	NO	6	33	15
2	PIPE	LOW POINT	6	33	6
2	PIPE VALVE	HIGH POINT	6	33	33

This is the way I want the finish product to look like and behave like. My full sheet is a sheet that has several thousand lines and separated by systems.

Within each system, the entry under lowest value should be the same for the entire system, the entry for the high value should be the same for the entire system. I'd like to be able to copy my formula through my entire sheet and have it calculate the low and high elevation value for each system.

However getting the max and min value is not enough. I only want max and min values if the "Feature" is a "PIPELINE" or a "PIPELINE VALVE". In my example, the Test Lead has a lower elevation than the PIPELINE, but since its not a PIPELINE, the elevation of the "Test Lead" isnt factored in the min or the max.


My Goal is not to create a new spreadsheet but rather insert three columns into my sheet of thousands of lines. (That already has the System name and Elevations) The three columns I want to input are Highest Value, Lowest Value and Low Point or High Point of Pipe. I want to automate the process as much as possible.

How would you tackle this problem?

[rgrocks]

I would have thought that finding teh max of a range would be a 1-cell deal? I wasnt expecting that you would copy it down anywhere. Try deleting the formula completely, and then re-entering it

The way the forumla works is as follows...
=MAX( MAX(IF(B12:B2531="Pipeline Valve",F12:F2531,0)) , MAX(IF(B12:B2531="Pipeline",F12:F2531,0)))

working from inside...MAX(IF(B12:B2531="Pipeline Valve",F12:F2531,0))
this is testing column B for the value "Pipeline Valve", and then for only those values in B that match, calc the MAX in F.
As I said, I could not get the wild card * to work, so I used another of the above, based on "Pipeline"
MAX(IF(B12:B2531="Pipeline",F12:F2531,0)))

I then put both of them inside a MAX() to find which 1 of the 2 was greater

Thank you this made a lot of sense.


The reason why I needed this to move down multiple cells is because I filter my sheet quite frequently and would like to know what the Max and Min elevation of the system is for every system. Creating a single cell for every system seems like alot of work. I'd like to copy and paste the array and have it detect what the max and min is for every system. That means within the formula, it should only check the max or min within a system. That way when it moves to system 2, it preforms another check for the max and min within system 2 only. and so on...

[rgrocks]

Another solution here. I put those fomulas in separate cells, no need to redo the heavy calculations.

Please Login or Register  to view this content.

Array formula, has to be entered with Ctrl+Shift+Delete.

This worked for solving the min and max value. THANK YOU,

However it doesn't take into account what system the elevation belongs to. Currently you have it showing me that the low point occurs at system 2, while the high point occurs at system 1. What I need is for it to tell me the low and high point for system 1 and the low and high point for system 2.

Thank You.

[rgrocks]

Another solution here. I put those fomulas in separate cells, no need to redo the heavy calculations.

Please Login or Register  to view this content.

Array formula, has to be entered with Ctrl+Shift+Delete.

Can you describe the logic behind this? Also what makes array formulas different?

[Jacc]

What makes array formulas different is that they will accept a range where a normal formula would only accept a single cell. I added a demo sheet, use the Evaluate Formula to see each step in calculation.
SEARCH("pipe";$B$12:$B$2531) will search each of the cells in $B$12:$B$2531 for the word "pipe". The output is a vector of 2519 rows containing the number of the character where "pipe" was found, in this case 1 since the strings begin with pipe. Where it's not found it will cause error. The IFERROR statement replaces those errors in the vector with FALSE.
Nex the IF takes any value separate from 0 as a TRUE. If we look at IF(TRUE;$F$12:$F$2531) it says basically makes a new vector and all those rows that had a number (and therefore is TRUE) will now contain the corresponding row from $B$12:$B$2531. Then the MIN picks the minimum value ofcourse.

Without the Ctrl+Shift+Delete the IF would only accept a single cell as an argument.

[rgrocks]

What makes array formulas different is that they will accept a range where a normal formula would only accept a single cell. I added a demo sheet, use the Evaluate Formula to see each step in calculation.
SEARCH("pipe";$B$12:$B$2531) will search each of the cells in $B$12:$B$2531 for the word "pipe". The output is a vector of 2519 rows containing the number of the character where "pipe" was found, in this case 1 since the strings begin with pipe. Where it's not found it will cause error. The IFERROR statement replaces those errors in the vector with FALSE.
Nex the IF takes any value separate from 0 as a TRUE. If we look at IF(TRUE;$F$12:$F$2531) it says basically makes a new vector and all those rows that had a number (and therefore is TRUE) will now contain the corresponding row from $B$12:$B$2531. Then the MIN picks the minimum value ofcourse.

Without the Ctrl+Shift+Delete the IF would only accept a single cell as an argument.

Thank You. I didnt know about the evaluate formula option.

I understand now, how to choose specific values to go into my MAX and MIN Value... However I still have the problem of tackling determining the MAX and MIN confined in a system. What I plan on doing with the information I learned from here is to add value to an existing enormous spread sheet. I think I'm going to start another thread on this issue. Lets simplify the problem, assumed I dont have to pick and choose values that contain "Pipeline". 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. I'd like the formula to cover the range of the sheet 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 #.).





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

[Jacc]

I believe that is what my last workbook does. You can just add as many systems as you want in the little table above and adjust the formulas.

[rgrocks]

I believe that is what my last workbook does. You can just add as many systems as you want in the little table above and adjust the formulas.

Can you elaborate? Your formula =MIN(IF(IFERROR(SEARCH("pipe",$B$4:$B$12),FALSE),IF($A$4:$A$12=F$3,$C$4:$C$12)))

Under the assumption of not needing to find "Pipe" and F3 is the name of the system, A column has the System name for each row, C Column has the elevation

=MIN(IF($A$4:$A$12=F$3,$C$4:$C$12)))

Will return the minimum value within the range (of the entire sheet), but will only give me the minimum value within the system labeled in F3

If I wanted to also find the next system's minimum value I would need to place the system name (system 2) in another cell , F4, then change the formula for just the cells that belong to system 2

=MIN(IF($A$4:$A$12=F$4,$C$4:$C$12)))

This method would require me to copy and paste a different formula for each system which I'm trying to avoid.

I'm probably missing something that would make this easier, please explain how I can work this for my application

[Jacc]

Maybe I wasn't clear enough. The workbook in post #13 (Pipe MIN MAX 2.xlsx)‎ did not only contain a calculation demo, it also contained upgraded formulas that look like:

Please Login or Register  to view this content.

You can expand the little table in cells E6:G8 as much as you want to include more system numbers (or names if you want that).
Since the min and max values don't are constant there is no need to calculate them in each cell, better to just calculate them once (which is don in the E6:G8 table).