# Return Value 1 or 0 based on Year and another condition

1. ## Return Value 1 or 0 based on Year and another condition

I want to return value 1 in Column E if INDEP_DIREC (column D) has value/no. of directors >= 3 for year 1995-2003 for firms in column A (Tickers). AND return the value 0 in if value/no. of directors >= 3 at any year between 1999-2003 in column F.

I tried doing this manually but due to long dataset it's quite difficult to manage this manually. Not quite sure which formula to go around this.

Any help would be appreciated.

2. ## Re: Return Value 1 or 0 based on Year and another condition

isn't there an overlap in your years? both 0 and 1 for directors count of > =3 and 1 for 1995 through 2003 while a 0 for 1999 through 2003, it seems that your overlap is 1999 through 2003.
AND I can't tell if column A has any influence on your requested formula.

3. ## Re: Return Value 1 or 0 based on Year and another condition

AND, this is the formula I've worked out so far but my questions still stand... =IF(A2="","",IF(AND(D2>=3,C2>=1995,C2<=2003),1,0))

4. ## Re: Return Value 1 or 0 based on Year and another condition

Yes you are absolutely right. As a matter of fact, I will later use this data to create dummy variable. My original criteria was that when firms(Column A) meets directors >= 3 for all years 1995-2003 it should be coded 1. But for the overlap directors >= 3 for any years between 1999-2003 should be coded 0 but SHOULD not include firms(column A) that has already been coded 1 or has >= 3 for all years 1995-2003.

But for simplicity of understanding for all, I decided to use column E to be coded 1 if directors >= 3 for all years 1995-2003 and Column F to code to code 0 for 1999-2003. Using this later I planned to check the data and combine them in one column.

5. ## Re: Return Value 1 or 0 based on Year and another condition

Are you OK with using a helper column? I would think that might be the easier way to accomplish this without a very complex formula.

6. ## Re: Return Value 1 or 0 based on Year and another condition

Thank you for the code. But this is evaluating each row and return 1 if directors>= 3. For example in the screenshot (I changed if false to "NO") its showing "NO" in year 1995 and 1999 as it doesn't meet the criteria. But is there is any formula which could evaluate all the years together to see if all the years has directors >= 3 or else it returns NO ?

Attachment 784944

7. ## Re: Return Value 1 or 0 based on Year and another condition

I think this would be great. Would really be helpful if you could guide one on this with a helper column. Thank you in advance

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