Sequential match

[Luke]

Thought I could handle this but it's kicking me good.

If "ALL" of the last 4 entries in colA are in sequential order (ascending or
descending) AND are "ALL" either <=, or, >= A1, then display B1 in A12
otherwise nothing.
A B
70 325

33
4
501
88
76
78
100
130

325 <<<A12 Results

Thank you now, and into the future
Luke

 

[Bernie Deitrick]

Luke,

For your example, witht he last four numbers in A7:A10:

=IF(AND(OR(SUMPRODUCT((A8:A10>A7:A9)*1)=3,
SUMPRODUCT((A8:A10<A7:A9)*1)=3),OR(MIN(A7:A10)>A1,MAX(A7:A10)<A1)),B1,"")

though it wasn't clear to me if the descending needed to all be less than A1, or....

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Oops: forgot about the <= / >=

=IF(AND(OR(SUMPRODUCT((A8:A10>A7:A9)*1)=3,
SUMPRODUCT((A8:A10<A7:A9)*1)=3),OR(MIN(A7:A10)>=A1,MAX(A7:A10)<=A1)),B1,"")

 

[Luke]

I works so far! I have a lot of entries s I will new post if I find any
problems.

Yeah, I was woundering if you would get the meaning.. Essentially I need B1
to show weather the sequence is ascending or descending as well as being
Higher or lower that A1.
Thanks, you're one of the best!
Luke

 

[Luke]

Thanks a bunch Bernie! I didn't even catch that.
Luke