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.
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.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
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))
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.
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.
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 ?
Thank you once again for your kind advice
Attachment 784944
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
I was away for a bit and hoping someone else would happen onto this post and try to give you their solution but...
anyway, your "attachment" in post #6 is invalid. As for your workbook in post #1, it would be VERY helpful for you to show a couple coded examples of your expected results because if I'm understanding your post #4, you don't want the tickers (col A) to have a 0 if they have a year range that goes between 1995 and 2003, only if they have a min year that begins in year 1998. But I went through all the data (all 4977 rows) and there are none that only begin in 1998. So I might be misunderstanding what you are writing in posts #4 and #6, especially since you mention directors and column D appears to have something to do with directors so it may be that I need to include something in the formula to account for that column?
I added a helper column (G) as I feel that the blanks in column D should be considered to be zeros.
The helper column is populated using: =IF(ISNUMBER(D2),D2,0)
Column E is populated using: =(AVERAGEIFS(G$2:G$4978,A$2:A$4978,A2,C$2:C$4978,">=1995",C$2:C$4978,"<=2003")>=3)+0
I am not sure how to populate column F or if it is still needed as column E displays both 1's and 0's (zeros).
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks