# MAX value but with two comparison filtering criteria

S

#### Struggling in Sheffield

Hi again chaps,
I'm using the following array formula to determine the MAX value in column
I3:I1002:

{=IF(I1019="","",MAX(IF(\$G\$3:\$G\$1002>\$H\$3:\$H\$1002,I3:I1002)))}

I get the MAX value from column I3:I1002 but only where corresponding values
in G3:G1002 are larger than the values in H3:H1002.

This appears to work fine but I now need the MAX value not only with
G3:G1002>H3:H1002, but also where E3:E1002>F3:F1002.

If someone could work the additional criteria into my formula it's be a
great help.
Many thanks for looking.

T

#### T. Valko

One way is to just nest another IF test.

Array entered:

=IF(I1019="","",MAX(IF(G3:G1002>H3:H1002,IF(E3:E1002>F3:F1002,I3:I1002))))

This has the meaning of:

IF G3:G1002>H3:H1002 *and* IF E3:E1002>F3:F1002 on a cell by cell basis.

S

#### Struggling in Sheffield

Hi Biff,
Thanks for that, works a treat.

Just hit the buffers again though where I need to find the MAX value in
I3:I1002 but using an OR function rather than AND.
If value in column G3:G1002 is >7 OR value in H3:H1002 is >7 (or even if
both columns have a value >7 in them), how could I find the MAX corresponding
value in column I3:I1002 where one or both criteria are met?

T

#### T. Valko

Array entered:

=MAX(IF((G3:G1002>7)+(H3:H1002>7),I3:I1002))

S

#### Struggling in Sheffield

Cheers Biff

T. Valko said:
Array entered:

=MAX(IF((G3:G1002>7)+(H3:H1002>7),I3:I1002))

--
Biff
Microsoft Excel MVP

.

T

#### T. Valko

You're welcome!

--
Biff
Microsoft Excel MVP

Struggling in Sheffield said:
Cheers Biff