Hi

I have a row of data with the cells I want to find a MAX for are
separated from each other:

a1=300 d1=500 g1=800 j1=10,000

I want to find the MAX value for a1,d1,g1,j1, but exclude and value
over 1000. So instead of =MAX(a1,d1,g1,j1) returning 10,000, =???(???)
will return the next highest max of 800.



Thanks

tx


--
tx12345
------------------------------------------------------------------------
tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=507940

Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MAX((MOD(COLUMN(A1:J1),3)=1)*(A1:J1<1000)*(A1:J1) )

Biff

"tx12345" wrote in
message ...

Hi

I have a row of data with the cells I want to find a MAX for are
separated from each other:

a1=300 d1=500 g1=800 j1=10,000

I want to find the MAX value for a1,d1,g1,j1, but exclude and value
over 1000. So instead of =MAX(a1,d1,g1,j1) returning 10,000, =???(???)
will return the next highest max of 800.



Thanks

tx


--
tx12345
------------------------------------------------------------------------
tx12345's Profile:
http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=507940

"Biff" wrote:
"tx12345" wrote:
a1=300 d1=500 g1=800 j1=10,000
I want to find the MAX value for a1,d1,g1,j1, but exclude
and value over 1000.
[....]
Entered as an array using the key combo of CTRL,SHIFT,ENTER:
=MAX((MOD(COLUMN(A1:J1),3)=1)*(A1:J1<1000)*(A1:J1) )

That's cute. It relies on the fact that the OP coincidentally(?)
specified every 3rd cell. If that's the OP's intent, great. But
is there a solution that works for any non-contiguous list of
cells that does not fit a pattern?

Ostensibly, I am looking for a solution like this array formula:

=max(if({A1;C3;F2;Z12} <= 1000, {A1;C3;F2;Z12}))

Of course, that does not work.

Try...

=MAX(IF(N(INDIRECT({"A1","C3","F2","Z12"}))<=1000, N(INDIRECT({"A1","C3","
F2","Z12"}))))

or

=MAX(IF(CHOOSE({1,2,3,4},A1,C3,F2,Z12)<=1000,CHOOS E({1,2,3,4},A1,C3,F2,Z1
2)))

Both formulas need to be confirmed with just ENTER.

Hope this helps!

In article ,
"
wrote:

"Biff" wrote:
"tx12345" wrote:
a1=300 d1=500 g1=800 j1=10,000
I want to find the MAX value for a1,d1,g1,j1, but exclude
and value over 1000.
[....]
Entered as an array using the key combo of CTRL,SHIFT,ENTER:
=MAX((MOD(COLUMN(A1:J1),3)=1)*(A1:J1<1000)*(A1:J1) )

That's cute. It relies on the fact that the OP coincidentally(?)
specified every 3rd cell. If that's the OP's intent, great. But
is there a solution that works for any non-contiguous list of
cells that does not fit a pattern?

Ostensibly, I am looking for a solution like this array formula:

=max(if({A1;C3;F2;Z12} <= 1000, {A1;C3;F2;Z12}))

Of course, that does not work.

"Domenic" wrote:
=MAX(IF(CHOOSE({1,2,3,4},A1,C3,F2,Z12)<=1000,
CHOOSE({1,2,3,4},A1,C3,F2,Z12)))

Thanks. This one seems to be the most intuitive answer --
entered as an array formula.

Patterns are your friend!

No "elegant" method that I know of. Domenic's samples will work but how
elegant would those be if the range was 100 cells?

This is another reason why a good spreadsheet design is essential.

Biff

" wrote
in message ...
"Biff" wrote:
"tx12345" wrote:
a1=300 d1=500 g1=800 j1=10,000
I want to find the MAX value for a1,d1,g1,j1, but exclude
and value over 1000.
[....]
Entered as an array using the key combo of CTRL,SHIFT,ENTER:
=MAX((MOD(COLUMN(A1:J1),3)=1)*(A1:J1<1000)*(A1:J1) )

That's cute. It relies on the fact that the OP coincidentally(?)
specified every 3rd cell. If that's the OP's intent, great. But
is there a solution that works for any non-contiguous list of
cells that does not fit a pattern?

Ostensibly, I am looking for a solution like this array formula:

=max(if({A1;C3;F2;Z12} <= 1000, {A1;C3;F2;Z12}))

Of course, that does not work.

Many thanks Biff, joeu and Domenic!

tx


--
tx12345
------------------------------------------------------------------------
tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=507940

Biff, I like your idea, but it seems if there are negative numbers in
the set I get a #VALUE! error.

Domenic, I like your idea, but then it still returns the highest value
and doesn't exclude it:

a1=850 d1=914 g1=825.5 j1=-1159 m1=867 p1=30500000000

=MAX(IF(CHOOSE({1,2,3,4,5,6},A1,D1,G1,J1,M1,P1)<=9 99999,CHOOSE({1,2,3,4,5,6},A1,D1,G1,J1,M1,P1)))
= 30500000

so it still returns the highest value

any ideas?


--
tx12345
------------------------------------------------------------------------
tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=507940

wrote:

[...]

[...] But
is there a solution that works for any non-contiguous list of
cells that does not fit a pattern?

Ostensibly, I am looking for a solution like this array formula:

=max(if({A1;C3;F2;Z12} <= 1000, {A1;C3;F2;Z12}))

Of course, that does not work.

If you download and install the latest version of the morefunc.xll add-in:

=MAX(IF(SETV(ARRAY.JOIN(A1,C3,F2,Z12))<1000,GETV() ))

which you need to confirm with control+shift+enter, not just with enter.