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.1I 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.

{=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.

Ask Your Question

Weekly Poll