Finding Maximum value while excluding some values

[tx12345]

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

 

[Biff]

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

 

[Guest]

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]

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!

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.

 

[Biff]

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

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.

 

[tx12345]

Many thanks Biff, joeu and Domenic!

tx

 

[tx12345]

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

so it still returns the highest value

any ideas?

 

[Domenic]

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

 

[Domenic]

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

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

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

Hope this helps!

 

[Biff]

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(A11)-COLUMN(A1),3)=0

OK, Aladin! <g>

Biff

 

[Domenic]

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(A11)-COLUMN(A1),3)=0

OK, Aladin! <g>

<VBG>

 

[tx12345]

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]

Domenic,

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

Many thanks,

Alladin

 

[Biff]

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

 

[Aladin Akyurek]

(e-mail address removed) 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.

 

[Guest]

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