Formula that assigns value of one if a number transition through a border.

Hello,

I would like to ask you how to assign a value of 1 (and 0 otherwise) if there was a change in the value of a variable, so that it was 10 or below 10 in the previous year, but in the next year got bigger then 10. I would also like to assign 1 if the value was 11 or bigger than 11 and is now smaller than 11.

In other words, I care for the borderline between 10 and 11 and whether the value of the variable trespassed that borderline, then it should trigger a change from 0 to 1.

Each observation has unique ID - which is a name and a year.

I have uploaded the excel file with the IDs and the values.

Thanks a lot!

Thank you Ron, it works.

However, I would like to get 1 only if the name of the firm is the same (the name of the firm is the latter in front of the year). In other words, A2002...A2006 should be treated separately from AAA2002...AAA2006. Not every cross of the border is important to me, only the crosses that correspond to the same firm.

Any idea how to do that?

Thanks, Ron it is a big progress and the result is almost the same as the one I am looking for. The small change than I need is that under column "Was 10 or below 10 and is now bigger than 10" one is assigned only when the number was (0...10) and is now bigger than 10. Under column "Was 11 or above 11 and is now lower than 11" I would like one to be assigned only in the cases, in which the number was 11 or above 11 and is now (0...10).

Because now I get one whenever the number crosses the borderline. However, I care for the history of the number as well.

Thanks a lot for helping with this!