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 any value over 1000. So instead of =MAX(a1,d1,g1,j1) returning 10,000, =???(???) will return the next highest max of 800.



Thanks

tx

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" <tx12345.22mme0_1138924802.3073@excelforum-nospam.com> wrote in
message news:tx12345.22mme0_1138924802.3073@excelforum-nospam.com...
>
> 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,CHOOSE({1,2,3,4},A1,C3,F2,Z1
2)))

Both formulas need to be confirmed with just ENTER.

Hope this helps!

In article <0B7FD468-BF41-4937-BCF0-A0A2AD186EEA@microsoft.com>,
"joeu2004@hotmail.com" <joeu2004hotmailcom@discussions.microsoft.com>
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.

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

"joeu2004@hotmail.com" <joeu2004hotmailcom@discussions.microsoft.com> wrote
in message news:0B7FD468-BF41-4937-BCF0-A0A2AD186EEA@microsoft.com...
> "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.

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

In article <tx12345.22o6ep_1138997410.0977@excelforum-nospam.com>,
tx12345 <tx12345.22o6ep_1138997410.0977@excelforum-nospam.com> 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)<=999999,CHOOSE({1,2,3,4,5,6},A
> 1,D1,G1,J1,M1,P1)))
> = 30500000
>
> so it still returns the highest value
>
> any ideas?

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.22o6ep_1138997410.0977@excelforum-nospam.com>,
tx12345 <tx12345.22o6ep_1138997410.0977@excelforum-nospam.com> 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)<=999999,CHOOSE({1,2,3,4,5,6},A
> 1,D1,G1,J1,M1,P1)))
> = 30500000
>
> so it still returns the highest value
>
> 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! <g>

Biff

"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-823A7D.16000803022006@msnews.microsoft.com...
> 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.22o6ep_1138997410.0977@excelforum-nospam.com>,
> tx12345 <tx12345.22o6ep_1138997410.0977@excelforum-nospam.com> 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)<=999999,CHOOSE({1,2,3,4,5,6},A
>> 1,D1,G1,J1,M1,P1)))
>> = 30500000
>>
>> so it still returns the highest value
>>
>> any ideas?

In article <#OuokkQKGHA.2304@TK2MSFTNGP15.phx.gbl>,
"Biff" <biffinpitt@comcast.net> 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>

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)

Domenic,

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

Many thanks,

Alladin

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" <tx12345.22oe1m_1139007301.6744@excelforum-nospam.com> wrote in
message news:tx12345.22oe1m_1139007301.6744@excelforum-nospam.com...
>
> 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
>

joeu2004@hotmail.com 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.

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