3rd column based on values of the 1st & 2nd column

Question

I have this table with Firm & Year column, and Answer column is what I want.

I need a 1 for each starting year of the firm, then 1 after every 3 years for that same firm. Is this possible with just formulas in Excel?

Firm        Year        Answer
Nokia       2007        1
Nokia       2008        0
Nokia       2009        0
Nokia       2010        0
Nokia       2011        1
Nokia       2012        0
Nokia       2013        0
Nokia       2014        0
Nokia       2015        1
Apple       2012        1
Apple       2013        0
Apple       2014        0
Apple       2015        0
Samsung     2009        1
Samsung     2010        0
Samsung     2011        0
Samsung     2012        0
Samsung     2013        1
Samsung     2014        0
Samsung     2015        0
Samsung     2016        0
Samsung     2017        1

Answer 1

Try,

=SIGN(NOT(MOD(COUNTIF(A$2:A2, A2)-1, 4)))

        

Fill down as necessary.

Answer 2

Assuming your year column is contiguous, something like this should do (starting from cell C2 and down):

=IF(MOD(ROW()-MATCH($A2,$A:$A,0),3),0,1)

Answer 3

A simple IF with a INDEX/MATCH will work:

=IF(OR(A2<>A1,MOD(B2-INDEX(B:B,MATCH(A2,A:A,0)),4)=0),1,0)