+ Reply to Thread
Results 1 to 7 of 7

Return Value 1 or 0 based on Year and another condition

  1. #1
    Registered User
    Join Date
    12-14-2021
    Location
    Bangkok
    MS-Off Ver
    MS Office 365, Verision 16.55
    Posts
    18

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,558

    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.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,558

    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. #4
    Registered User
    Join Date
    12-14-2021
    Location
    Bangkok
    MS-Off Ver
    MS Office 365, Verision 16.55
    Posts
    18

    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. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,558

    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. #6
    Registered User
    Join Date
    12-14-2021
    Location
    Bangkok
    MS-Off Ver
    MS Office 365, Verision 16.55
    Posts
    18

    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 ?

    Thank you once again for your kind advice

    Attachment 784944

  7. #7
    Registered User
    Join Date
    12-14-2021
    Location
    Bangkok
    MS-Off Ver
    MS Office 365, Verision 16.55
    Posts
    18

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Return with all dates based on Year Criteria
    By Sameh_Sam in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-06-2020, 03:48 PM
  2. [SOLVED] Return months based on year
    By Abdur_rahman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2020, 04:20 AM
  3. [SOLVED] Formula to extract the year from the date based on a condition ?
    By donny007 in forum Excel Formulas & Functions
    Replies: 29
    Last Post: 10-26-2018, 02:06 AM
  4. [SOLVED] Return value based on two variables - year and month
    By Keelin in forum Excel General
    Replies: 3
    Last Post: 04-12-2016, 02:23 AM
  5. [SOLVED] Help with formula to return a value based on if that expiry date is within year
    By mw91 in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 01-08-2015, 11:37 AM
  6. [SOLVED] Return month based on day of the year
    By Marco-Kun in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 03-27-2014, 11:58 AM
  7. Return a value based on a condition
    By uberathlete in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-08-2013, 08:58 PM

Tags for this Thread

Bookmarks

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