+ Reply to Thread
Results 1 to 9 of 9

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,571

    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,571

    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,571

    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

  8. #8
    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,571

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

    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?

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,097

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

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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