Discrete functions: max and min relatives

Hy guys:

here is the thing, let=B4s say column A has a list of 500 values
(discrete function). Is there a way to identify max and min relatives
in that list ? (of course, I=B4m not interested in max and min
absolutes!!!) . I guess some type of criteria need to be done. The
result could be something like:

Colum A, Column B,,,,,,

A1
A2 I'm a max rel.
..
A40 I=B4m a max rel.
..
..
A105 I'm a min rel.
..
..
A500

One way, if there are no local duplications:

in B2:

=CHOOSE(2-(A2>A1)*(A2>A3)+(A2<A1)*(A2<A3),"rel max","","rel min")

copy down as far as req'd.


In article <1148762380.536678.230980@u72g2000cwu.googlegroups.com>,
"Mika" <rigel_58@yahoo.com> wrote:

> Hy guys:
>
> here is the thing, let´s say column A has a list of 500 values
> (discrete function). Is there a way to identify max and min relatives
> in that list ? (of course, I´m not interested in max and min
> absolutes!!!) . I guess some type of criteria need to be done. The
> result could be something like:
>
> Colum A, Column B,,,,,,
>
> A1
> A2 I'm a max rel.
> .
> A40 I´m a max rel.
> .
> .
> A105 I'm a min rel.
> .
> .
> A500

Hello,

And in case of local duplications:

=CHOOSE(TRUNC((SIGN(A2-A1)+SIGN(A2-A3))/2)+2,"I'm a min rel.","","I'm a
max rel.")

Regards,
Bernd

Thanks a lot Bernd and JE,

I found that if I use your formulas I got "a lot" of close min and
max... instead I would like to have if not exactly THE min or max ,
close enough in that region....


I know what I=B4m saying is different now from my original request, but
you know, I=B4m user !!.

I found that if I modify JE's formula to: (i=B4m starting at A10)
=3DCHOOSE(2-(A11>A10)*(A11>MAX(A12:A20)))+(A11<A10)*(A11<MIN(A12:A20))),"ma=
x","","min")

gives me less points and close enough to the regions where there is a
max or a min. however sometimes miss completely some of them. I tried
several extensions of the range A12:A20 but always loose some max or
mins.

I wonder, if you guys can tweak it to catch those.....

Thanks again for your time.

Mika



bplumhoff@gmail.com wrote:
> Hello,
>
> And in case of local duplications:
>
> =3DCHOOSE(TRUNC((SIGN(A2-A1)+SIGN(A2-A3))/2)+2,"I'm a min rel.","","I'm a
> max rel.")
>=20
> Regards,
> Bernd

Hello Mika,

Ok. You can specify your range now in cell C1 (enter 3, for example to
calculate rel min/max across next/previous 3 cells).

A1 is your first cell, A22 your last (please change if necessary).

Then enter into B1:
=IF(A1>MAX(IF(ROW()<=ROW($A$1),-E1308,OFFSET(A1,MAX(-$C$1,ROW($A$1)-ROW()),0,MIN($C$1,ROW()-ROW($A$1)))),IF(ROW()>=ROW($A$22),-E1308,OFFSET(A1,1,0,MIN($C$1,ROW($A$22)-ROW())))),"Max",IF(A1<MIN(IF(ROW()<=ROW($A$1),E1308,OFFSET(A1,MAX(-$C$1,ROW($A$1)-ROW()),0,MIN($C$1,ROW()-ROW($A$1)))),IF(ROW()>=ROW($A$22),E1308,OFFSET(A1,1,0,MIN($C$1,ROW($A$22)-ROW())))),"Min",""))

anc copy down to B22.

HTH,
Bernd

Bernd,

U da man !!!

Thank you

Hi Bernd,

I found that when I add new data (every day the list increases in 1
item and has more than 500 already) the previous max and min change...

other questions, what is the rol of -E1308 in the formula ??

Thanks
Mika


bplumhoff

Hello,

Enter into B2:

=CHOOSE((SIGN(A2-A1)+SIGN(A2-A3))/2+2,"I'm a min rel.","","I'm a max
rel.")

and copy down as far as necessary.

HTH,
Bernd