MAX value but with two comparison filtering criteria

[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. 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.

 

[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. Valko]

Array entered:

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

 

[Struggling in Sheffield]

Cheers Biff

Array entered:

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

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Cheers Biff