Creating a formula to indicate lapse years

[jthompso]

I'm using Excel 2013. I have my parameters, but am having trouble translating it into a formula.

Below I have columns of data actuals for each year. On the far right, there is a corresponding column for each year for lapsed ID's. Note that the second column from the left is a "total" count that adds up the columns of data.

Here is what I am trying to do:

1. If a zero is given in any year, and there is a number greater than zero in the previous year, return “true” in the “Lapse” year column in which the zero occurs. Don't worry about the first year of data (2005).
2. But, No “Lapse” year shall be indicated “true” until the year the Total count reaches at least 2, and only on the second instance. See the Row with the ID “Chris” for example. It would only return “true” for 2013, but not for 2008.

[TABLE="width: 1599"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Total 2005-2015[/TD]
[TD]2005[/TD]
[TD]2006[/TD]
[TD]2007[/TD]
[TD]2008[/TD]
[TD]2009[/TD]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]Inception[/TD]
[TD][/TD]
[TD]Lapse2006[/TD]
[TD]Lapse2007[/TD]
[TD]Lapse2008[/TD]
[TD]Lapse2009[/TD]
[TD]Lapse2010[/TD]
[TD]Lapse2011[/TD]
[TD]Lapse2012[/TD]
[TD]Lapse2013[/TD]
[TD]Lapse2014[/TD]
[TD]Lapse2015[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Earl[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jake[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Pecan]

Hi, this is the formula that I used in the Chris & Lapse2006 cell: =IF(AND(D10=0, C10>0, SUM($C10:D10)>=2), "True", ""). I hope this works for you.

 

[jthompso]

No. The problem is that it is returning "true" for 2008, which is not what I want, because for 2008, there was only one instance that had occurred by that year. So, when deciding whether Chris had lapsed in 2008, the answer was 'no' because he only had one instance. On the other hand, when deciding whether Chris had lapsed in 2013, the answer was 'yes' because he had two instances total by that year (in 2007 and 2012). Each year's calculations should have no knowledge of future years.

Did I explain that clearly?

 

[Pecan]

??? I did not receive a "true" for 2008 and did receive one for 2013. Obviously, I'm not getting the same results that you are. Though the information is not aligning properly in the copy and paste below; if you copy the below and paste in Excel, you can see the results I achieved using the formula provided. You may want to ensure that the formula row/column information matches the Excel file that you are using.

ID Lapse2006 Lapse2007 Lapse2008 Lapse2009 Lapse2010 Lapse2011 Lapse2012 Lapse2013 Lapse2014 Lapse2015
Bob TRUE
Mike TRUE
Joe TRUE
Earl TRUE TRUE
Jake TRUE
Chris TRUE

 

[Pecan]

Try this image instead.

ID Lapse2006 Lapse2007 Lapse2008 Lapse2009 Lapse2010 Lapse2011 Lapse2012 Lapse2013 Lapse2014 Lapse2015
Bob True
Mike True
Joe True
Earl True True
Jake True
Chris True

 

[jthompso]

Pecan,

I apologize. My mistake. The answer you gave works great! I had dropped it into my spreadsheet incorrectly.

Thank you!