software

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: www.excelforum.com/member.php...oamp;userid=24776
View this thread: www.excelforum.com/showthread...hreadid=507940Hi!

Try this:

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

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

Biff

quot;tx12345quot; gt; wrote in
message ...
gt;
gt; Hi
gt;
gt; I have a row of data with the cells I want to find a MAX for are
gt; separated from each other:
gt;
gt; a1=300 d1=500 g1=800 j1=10,000
gt;
gt; I want to find the MAX value for a1,d1,g1,j1, but exclude and value
gt; over 1000. So instead of =MAX(a1,d1,g1,j1) returning 10,000, =???(???)
gt; will return the next highest max of 800.
gt;
gt;
gt;
gt; Thanks
gt;
gt; tx
gt;
gt;
gt; --
gt; tx12345
gt; ------------------------------------------------------------------------
gt; tx12345's Profile:
gt; www.excelforum.com/member.php...oamp;userid=24776
gt; View this thread: www.excelforum.com/showthread...hreadid=507940
gt;
quot;Biffquot; wrote:
gt; quot;tx12345quot; wrote:
gt; gt; a1=300 d1=500 g1=800 j1=10,000
gt; gt; I want to find the MAX value for a1,d1,g1,j1, but exclude
gt; gt; and value over 1000.
gt; [....]
gt; Entered as an array using the key combo of CTRL,SHIFT,ENTER:
gt; =MAX((MOD(COLUMN(A1:J1),3)=1)*(A1:J1lt;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} lt;= 1000, {A1;C3;F2;Z12}))

Of course, that does not work.

Try...

=MAX(IF(N(INDIRECT({quot;A1quot;,quot;C3quot;,quot;F2quot;,quot;Z12quot;}))lt;=1000, N(INDIRECT({quot;A1quot;,quot;C3quot;,quot;
F2quot;,quot;Z12quot;}))))

or

=MAX(IF(CHOOSE({1,2,3,4},A1,C3,F2,Z12)lt;=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 gt;,
quot; gt;
wrote:

gt; quot;Biffquot; wrote:
gt; gt; quot;tx12345quot; wrote:
gt; gt; gt; a1=300 d1=500 g1=800 j1=10,000
gt; gt; gt; I want to find the MAX value for a1,d1,g1,j1, but exclude
gt; gt; gt; and value over 1000.
gt; gt; [....]
gt; gt; Entered as an array using the key combo of CTRL,SHIFT,ENTER:
gt; gt; =MAX((MOD(COLUMN(A1:J1),3)=1)*(A1:J1lt;1000)*(A1:J1) )
gt;
gt; That's cute. It relies on the fact that the OP coincidentally(?)
gt; specified every 3rd cell. If that's the OP's intent, great. But
gt; is there a solution that works for any non-contiguous list of
gt; cells that does not fit a pattern?
gt;
gt; Ostensibly, I am looking for a solution like this array formula:
gt;
gt; =max(if({A1;C3;F2;Z12} lt;= 1000, {A1;C3;F2;Z12}))
gt;
gt; Of course, that does not work.

Patterns are your friend!

No quot;elegantquot; 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

quot; gt; wrote
in message ...
gt; quot;Biffquot; wrote:
gt;gt; quot;tx12345quot; wrote:
gt;gt; gt; a1=300 d1=500 g1=800 j1=10,000
gt;gt; gt; I want to find the MAX value for a1,d1,g1,j1, but exclude
gt;gt; gt; and value over 1000.
gt;gt; [....]
gt;gt; Entered as an array using the key combo of CTRL,SHIFT,ENTER:
gt;gt; =MAX((MOD(COLUMN(A1:J1),3)=1)*(A1:J1lt;1000)*(A1:J1) )
gt;
gt; That's cute. It relies on the fact that the OP coincidentally(?)
gt; specified every 3rd cell. If that's the OP's intent, great. But
gt; is there a solution that works for any non-contiguous list of
gt; cells that does not fit a pattern?
gt;
gt; Ostensibly, I am looking for a solution like this array formula:
gt;
gt; =max(if({A1;C3;F2;Z12} lt;= 1000, {A1;C3;F2;Z12}))
gt;
gt; Of course, that does not work.

Many thanks Biff, joeu and Domenic!

tx--
tx12345
------------------------------------------------------------------------
tx12345's Profile: www.excelforum.com/member.php...oamp;userid=24776
View this thread: 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=850d1=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)lt;=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: www.excelforum.com/member.php...oamp;userid=24776
View this thread: www.excelforum.com/showthread...hreadid=507940Sorry! You need to confirm the formula with CONTROL SHIFT ENTER, not
just ENTER.

In article gt;,
tx12345 gt; wrote:

gt; Biff, I like your idea, but it seems if there are negative numbers in
gt; the set I get a #VALUE! error.
gt;
gt; Domenic, I like your idea, but then it still returns the highest value
gt; and doesn't exclude it:
gt;
gt; a1=850d1=914 g1=825.5 j1=-1159 m1=867 p1=30500000000
gt;
gt; =MAX(IF(CHOOSE({1,2,3,4,5,6},A1,D1,G1,J1,M1,P1)lt;=9 99999,CHOOSE({1,2,3,4,5,6},A
gt; 1,D1,G1,J1,M1,P1)))
gt; = 30500000
gt;
gt; so it still returns the highest value
gt;
gt; any ideas?

Biff's formula can be modified as follows...

=MAX(IF(MOD(COLUMN(A1:P1)-COLUMN(A1),3)=0,IF(A1:P1lt;1000,A1:P1)))

....confirmed with CONTROL SHIFT ENTER.

Hope this helps!

In article gt;,
tx12345 gt; wrote:

gt; Biff, I like your idea, but it seems if there are negative numbers in
gt; the set I get a #VALUE! error.
gt;
gt; Domenic, I like your idea, but then it still returns the highest value
gt; and doesn't exclude it:
gt;
gt; a1=850d1=914 g1=825.5 j1=-1159 m1=867 p1=30500000000
gt;
gt; =MAX(IF(CHOOSE({1,2,3,4,5,6},A1,D1,G1,J1,M1,P1)lt;=9 99999,CHOOSE({1,2,3,4,5,6},A
gt; 1,D1,G1,J1,M1,P1)))
gt; = 30500000
gt;
gt; so it still returns the highest value
gt;
gt; any ideas?

I was unable to to duplicate the OPs problem with #VALUE! and the formula
worked just fine using negative numbers. If there might be empty cells you
could add an array: (ISNUMBER(A1:J1).

MOD(COLUMN(A1:P1)-COLUMN(A1),3)=0

OK, Aladin! lt;ggt;

Biff

quot;Domenicquot; gt; wrote in message
...
gt; Biff's formula can be modified as follows...
gt;
gt; =MAX(IF(MOD(COLUMN(A1:P1)-COLUMN(A1),3)=0,IF(A1:P1lt;1000,A1:P1)))
gt;
gt; ...confirmed with CONTROL SHIFT ENTER.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; tx12345 gt; wrote:
gt;
gt;gt; Biff, I like your idea, but it seems if there are negative numbers in
gt;gt; the set I get a #VALUE! error.
gt;gt;
gt;gt; Domenic, I like your idea, but then it still returns the highest value
gt;gt; and doesn't exclude it:
gt;gt;
gt;gt; a1=850 d1=914 g1=825.5 j1=-1159 m1=867 p1=30500000000
gt;gt;
gt;gt; =MAX(IF(CHOOSE({1,2,3,4,5,6},A1,D1,G1,J1,M1,P1)lt;=9 99999,CHOOSE({1,2,3,4,5,6},A
gt;gt; 1,D1,G1,J1,M1,P1)))
gt;gt; = 30500000
gt;gt;
gt;gt; so it still returns the highest value
gt;gt;
gt;gt; any ideas?