Intersection reminder - for nobel prize:)

[oneandtwo]

Hi guys,

I have a value, lets say 100, which is a "border" value and I would need a signal anytime this border would be crossed. Yes I know, simple If command could solve this very fast... =if(A1=>100;"More";"Less").... and if VALUE would be 110 or 186 I would always have a signal it is "MORE".

BUT!

Let's say I don't want to have this "MORE" or "LESS" signal for the value all the time. For an example... If value jumps from 90 to 115, I would just like to get one signal, but when it goes to 116 or 200 or 1500, I would not like to have it anymore. And of course when values are starting to fall under 100 I would need a signal for the first value which hits under 100, while rest would not be tracked...

I am sorry, I am not able to use VBA editor with whom many of you are working. Is there any simple solution within excel?

Thank you.

[shg]

Maybe ...

Row\Col	A	B	C
1
2	97	--	B2: =IF(AND(A1<=100, A2>100), "More", IF(AND(A1>=100, A2<100), "Less", "--"))
3	101	More
4	103	--
5	100	--
6	103	More
7	102	--
8	100	--
9	97	Less
10	95	--
11	105	More
12	99	Less
13	98	--
14	103	More
15	101	--
16	105	--
17	95	Less

[oneandtwo]

Thank you for reply. I tried your formula but it doesn't work.

Really basically I need just one signal for the first value which would cross the border value of let's say 100.

Let's assume A1 has value 79. B1 should be empty. A1 goes to 99, B1 is still empty. But when A1 goes to 110, B1 should show signal that value went over 100 (let's say: "Crossed"), then A1 would be 150 and B1 would need to be empty, as long A1 doesn't fall under 100 again and B1 would signal that.

For me it is essential that B1 which follows A1 is empty most of the time, only when A1 value crosses the border (in up or down) it would need to give a signal that border value was crossed.

[Punnam]

=IF(A1<100,"",IF(AND(A1>100,A1<150),"Crossed",""))

Try this in b1

Punnam

[oneandtwo]

Thanks guys, I solved it with iterative option... Thank you for your help anyway! I appreciate it.