Discrete functions: max and min relatives

[Mika]

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

 

[JE McGimpsey]

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.

 

[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

 

[bplumhoff]

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

 

[Mika]

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´m saying is different now from my original request, but
you know, I´m user !!.

I found that if I modify JE's formula to: (i´m starting at A10)
=CHOOSE(2-(A11>A10)*(A11>MAX(A12:A20)))+(A11<A10)*(A11<MIN(A12:A20))),"max","","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]

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

 

[Mika]

Bernd,

U da man !!!

Thank you

 

[Mika]

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

 

[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

 

[Mika]

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

 

[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