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

1. ## 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!

2. ## Re: Formula that assigns value of one if a number transition through a border.

This regular formula, copied down, flags sequential changes that cross the 10/11 threshold
``Please Login or Register  to view this content.``
EDITED TO INCLUDE THIS SIMPLER ALTERNATIVE:
Copied down
``Please Login or Register  to view this content.``
Is that something you can work with?

3. ## Re: Formula that assigns value of one if a number transition through a border.

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?

4. ## Re: Formula that assigns value of one if a number transition through a border.

Ah...I must have missed that in your original request.
This regular formula, copied down, flags when a specific company prefix crosses the threshold
``Please Login or Register  to view this content.``
Does that help?

5. ## Re: Formula that assigns value of one if a number transition through a border.

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!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1