more in Max min relatives

[Mika]

Hi,

Say I have n points(values): P1,P2,.... Pn

I want to define a minimum relative if, for example a point (P3) has a
value lower than X points after and X points before. If X=2 then:

P1>P2>P3 and P3<P4<P5 then P3 is a minimum. or

=if(and(P1>P2>, P2>P3 , P3<P4,P4<P5 ),"P3 is Min","")

Now the questions is: how can I have similar formula with X a
variable? (X could be any value between 1 and 5, 1<=X<=5 )

Thanks for your time
Mika

 

[Bernie Deitrick]

Mika,

With your interval size (1, 2,3,4, or 5) entered into cell B1:

=IF(MIN(OFFSET(OFFSET(A10,-$B$1,0,1,1),0,0,2*$B$1+1,1))=A10,"MIN","NOT MIN")

for the value in cell A10....

HTH,
Bernie
MS Excel MVP

 

[JE McGimpsey]

One way:

Public Function RelativeMin( _
ByRef rP As Range, _
byVal x As Long, _
Optional ByVal bCol As Boolean = True _
) As Variant
Dim rRange As Range
Dim i As Long
Dim bResult As Boolean

Application.Volatile
On Error GoTo ErrorHandler
bResult = True 'assume minimum
With rP(1)
If bCol Then
Set rRange = .Offset(-x, 0).Resize(2 * x + 1, 1)
Else
Set rRange = .Offset(0, -x).Resize(1, 2 * x + 1)
End If
End With
For i = 1 To x
If rRange(i) <= rRange(i + 1) Or _
rRange(x + i) >= rRange(x + i + 1) Then
bResult = False
Exit For
End If
Next i
RelativeMin = bResult
ResumeHere:
Exit Function
ErrorHandler:
RelativeMin = CVErr(xlErrRef)
Resume ResumeHere
End Function

Call as (for data in column):

=RelativeMin(P3, x)

or

=IF(RelativeMin(P3,x,TRUE), "P3 is Min", "P3 is not Min")

For data in row:

=RelativeMIn(P3, x, FALSE)


Obviously, this could do with more error handling, but it should do as a
template.

 

[JE McGimpsey]

I could be misinterpreting the OP, but I don't think that will quite
work:

A5: 8
A6: 3
A7: 6
A8: 4
A9: 5
A10: 3
A11: 3
A12: 5
A13: 3
A14: 7
A15: 8

With 5 in B1,


=IF(MIN(OFFSET(OFFSET(A10,-$B$1,0,1,1),0,0,2*$B$1+1,1))=A10,"MIN","NOT
MIN")

returns "MIN"

which doesn't fit the pattern P1>P2, P2>P3, P3>P4, P4>P5, P5>P6, P6<P7,
P7<P8, P8<P9, P9<P10, P10<P11

 

[Bernie Deitrick]

JE,

I think you're right. So, perhaps, one of these:

Doesn't return MIN if the value is duplicated:
=IF(AND(MIN(OFFSET(OFFSET(A10,-$B$1,0,1,1),0,0,2*$B$1+1,1))=A10,COUNTIF(OFFSET(OFFSET(A10,-$B$1,0,1,1),0,0,2*$B$1+1,1),A10)=1),"MIN","NOT
MIN")

Only returns MIN if the value is preceeded by X descending values and succeeded by X ascending
values:
=IF(AND(SUMPRODUCT((OFFSET(A10,-$B$1,0,$B$1)>OFFSET(A10,-$B$1+1,0,$B$1))*1)=$B$1,SUMPRODUCT((OFFSET(A10,0,0,$B$1)<OFFSET(A10,1,0,$B$1))*1)=$B$1),"MIN","NOT
MIN")

HTH,
Bernie
MS Excel MVP

 

[JE McGimpsey]

That's what I was assuming. Nice.

A couple less function calls:

=IF(SUMPRODUCT((OFFSET(A10,-$B$1,0,$B$1)>OFFSET(A10,-$B$1+1,0,$B$1))
+ (OFFSET(A10,0,0,$B$1)<OFFSET(A10,1,0,$B$1)))=2*$B$1,"MIN","NOT MIN")

 

[Mika]

JE & Bernie

Wow !

Yes I can't use vba in this project. I inverted the operators to get
the maximum in another column.

I know that for this kind of problem it is used sumproduct and
although I read the help is difficult fo me to grasp how it works in
this case. Wonder if you can point me to some place to understand
it ....

thanks a lot
Mika