MAX value but with two comparison filtering criteria
 

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:G1002H3:H1002, but also where E3:E1002F3:F1002.

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

MAX value but with two comparison filtering criteria
 

One way is to just nest another IF test.

Array entered:


=IF(I1019="","",MAX(IF(G3:G1002H3:H1002,IF(E3:E10 02F3:F1002,I3:I1002))))

This has the meaning of:

IF G3:G1002H3:H1002 *and* IF E3:E1002F3:F1002 on a cell by cell basis.

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"
wrote in message ...
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:G1002H3:H1002, but also where E3:E1002F3:F1002.

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

MAX value but with two comparison filtering criteria
 

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" wrote:

One way is to just nest another IF test.

Array entered:


=IF(I1019="","",MAX(IF(G3:G1002H3:H1002,IF(E3:E10 02F3:F1002,I3:I1002))))

This has the meaning of:

IF G3:G1002H3:H1002 *and* IF E3:E1002F3:F1002 on a cell by cell basis.

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"
wrote in message ...
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:G1002H3:H1002, but also where E3:E1002F3:F1002.

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


.

MAX value but with two comparison filtering criteria
 

Array entered:

=MAX(IF((G3:G10027)+(H3:H10027),I3:I1002))

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"
wrote in message ...
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" wrote:

One way is to just nest another IF test.

Array entered:


=IF(I1019="","",MAX(IF(G3:G1002H3:H1002,IF(E3:E10 02F3:F1002,I3:I1002))))

This has the meaning of:

IF G3:G1002H3:H1002 *and* IF E3:E1002F3:F1002 on a cell by cell basis.

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"

wrote in message
...
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:G1002H3:H1002, but also where E3:E1002F3:F1002.

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


.

MAX value but with two comparison filtering criteria
 

Cheers Biff

"T. Valko" wrote:

Array entered:

=MAX(IF((G3:G10027)+(H3:H10027),I3:I1002))

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"
wrote in message ...
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" wrote:

One way is to just nest another IF test.

Array entered:


=IF(I1019="","",MAX(IF(G3:G1002H3:H1002,IF(E3:E10 02F3:F1002,I3:I1002))))

This has the meaning of:

IF G3:G1002H3:H1002 *and* IF E3:E1002F3:F1002 on a cell by cell basis.

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"

wrote in message
...
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:G1002H3:H1002, but also where E3:E1002F3:F1002.

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


.



.

MAX value but with two comparison filtering criteria
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"
wrote in message ...
Cheers Biff

"T. Valko" wrote:

Array entered:

=MAX(IF((G3:G10027)+(H3:H10027),I3:I1002))

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"

wrote in message
...
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" wrote:

One way is to just nest another IF test.

Array entered:


=IF(I1019="","",MAX(IF(G3:G1002H3:H1002,IF(E3:E10 02F3:F1002,I3:I1002))))

This has the meaning of:

IF G3:G1002H3:H1002 *and* IF E3:E1002F3:F1002 on a cell by cell
basis.

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"

wrote in message
...
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:G1002H3:H1002, but also where E3:E1002F3:F1002.

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


.



.