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,

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

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 Mika,

Change $A$22 to $A$65536.

Normally the min/max should change if and only if the underlying data
changes or is added.

The -1E308 and 1E308 are very low (negative) and very high (positive)
numbers which are given to the formula when we face the list boundaries
(lower or upper end). We do not want to get #REF! errors but we do not
want to introduce any value which alters the min/max comparison,
either. So these values are fed as "innocent" comparison values (1E308
will not hurt any min calculation, -1E308 any max calc).

If you still have problems, post or email an instructive example,
please.

Have fun,
Bernd

Thanks Bernd,

it is working !, my mistake was that I had replaced A22 for the end of
the variable interval I was checking, not the inferior limit of that
data.


Mika

Hi Bernd,

Still have some problems when adding new date and using the formula. To
show it better, I sent you an excel file showing it.

Rg
Mika