Return max of column based on cond. in row

February 17, 2011 at 06:47:07
Specs: Windows XP
I am trying to determine the maximum value of a column, when certain criteria is met for that intersection (i.e. row meets criteria, consider value for 'max value' in adjacent column). Let me give an example.

cycle................Pressure
step....................(psig)
1........................ 0.2
1........................ 0.9
1........................1.2
2........................1.9
2........................ 2.4
3........................3.5
3........................4.8
4........................ 6.1
5........................ 7.2
8........................8.5
8........................8.6
8........................8.4
8........................8.6
8........................ 8.7
12........................ 5.4
12........................ 3.2
13........................1.1

I want to mind the max pressure only when the cycle step is at 8, in other words, when the cycle is at 8, look at all pressure associated with that time, and find the max pressure in that range. The example above would yield 8.7.

I have tried several IF, MATCH, MAX, INDEX formulas with no avail.

Please help

**note, edited with periods (.) to illustrate example. just think of them as blank spaces. There are only two columns in my example.


See More: Return max of column based on cond. in row

Report •


#1
February 17, 2011 at 09:49:34
{=MAX(IF(A3:A19=8,B3:B19))}

This is an array formula.

Enter =MAX(IF(A3:A19=8,B3:B19)) in a cell and press Ctrl-Shift-Enter to have the { } appear. Do not type them in.

If you will be changing the "8" to other values, I suggest you use a cell reference so you don't have to keep changing the formula and pressing Ctrl-Shift-Enter everytime.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question