Finding Maximum value while excluding some values
 

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

Finding Maximum value while excluding some values
 

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

Finding Maximum value while excluding some values
 

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

Finding Maximum value while excluding some values
 

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.

Finding Maximum value while excluding some values
 

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.

Finding Maximum value while excluding some values
 

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

Finding Maximum value while excluding some values
 

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

Finding Maximum value while excluding some values
 

Sorry! You need to confirm the formula with CONTROL+SHIFT+ENTER, not
just ENTER.

In article ,
tx12345 wrote:

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},A
1,D1,G1,J1,M1,P1)))
= 30500000

so it still returns the highest value

any ideas?

Finding Maximum value while excluding some values
 

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

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

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
tx12345 wrote:

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},A
1,D1,G1,J1,M1,P1)))
= 30500000

so it still returns the highest value

any ideas?

Finding Maximum value while excluding some values
 

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! <g

Biff

"Domenic" wrote in message
...
Biff's formula can be modified as follows...

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

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
tx12345 wrote:

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},A
1,D1,G1,J1,M1,P1)))
= 30500000

so it still returns the highest value

any ideas?

Finding Maximum value while excluding some values
 

In article ,
"Biff" wrote:

I was unable to to duplicate the OPs problem with #VALUE!...

The formula will return #VALUE! if a non-target cell contains a text
value.

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

OK, Aladin! <g

<VBG

Finding Maximum value while excluding some values
 

Biff

Your formula is great, but in cells b1,e1,h1, etc there is text
(reference)

now if there was a way to convert text to numbers then i can get rid of
the text (ie a=1, b=2, c=3, abc/ABC = 123, etc)


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

Finding Maximum value while excluding some values
 

Domenic,

OK, that was the trick, and the ctrl-shift-enter was not needed.

Many thanks,

Alladin :)


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

Finding Maximum value while excluding some values
 

Yeah, that text messes things up!

Use Domenic's modified version of my formula.

The added bit to the MOD function is there to account for any inserted
columns before column A.

Biff

"tx12345" wrote in
message ...

Biff

Your formula is great, but in cells b1,e1,h1, etc there is text
(reference)

now if there was a way to convert text to numbers then i can get rid of
the text (ie a=1, b=2, c=3, abc/ABC = 123, etc)


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

Finding Maximum value while excluding some values
 

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.

Finding Maximum value while excluding some values
 

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