Hello,
I am trying to create a formula that will count the most recent consecutive appearances of "Fail" in the table below based on a number of criteria. I have been researching and come across a proposal for using a max/frequency array formula which I adapted for this dataset and pasted in below. The problem is that I want it to only count the most recent occurences of consecutive failure based on the date entered. For example the formula I am using returns a count of 5 consecutive failures before 1/11/2016. I want the result of this formula to display as 1 consecutive failure because there are "Pass" values that proceed the November row. If I was to set the date value in the formula to "1/12/2016" the formula should display 2 consecutive failures because both November and December's values are a 'Fail' but the failures earlier in the year should be disregarded. I am hoping to develop a catch-all formula that can be carried across the entire history of my data which will continue to be appended to each month and will only count the most recent set of consecutive failures for the given criteria.
I have a sample workbook I can provide with the data and formula if that would be helpful.
**enter as array formula (ctrl+shift+enter)
=MAX(FREQUENCY(IF(A5:A16<="1/11/2016",IF(B5:B16="apple",IF(C5:C16="banana",IF(D5:D16="Fail",ROW(D5:D16))))),IF(A5:A16>="1/11/2016",IF(B5:B16<>"apple",IF(C5:C16<>"banana",IF(D5:D16<>"Fail",ROW(D5:D16)))))))
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
I am trying to create a formula that will count the most recent consecutive appearances of "Fail" in the table below based on a number of criteria. I have been researching and come across a proposal for using a max/frequency array formula which I adapted for this dataset and pasted in below. The problem is that I want it to only count the most recent occurences of consecutive failure based on the date entered. For example the formula I am using returns a count of 5 consecutive failures before 1/11/2016. I want the result of this formula to display as 1 consecutive failure because there are "Pass" values that proceed the November row. If I was to set the date value in the formula to "1/12/2016" the formula should display 2 consecutive failures because both November and December's values are a 'Fail' but the failures earlier in the year should be disregarded. I am hoping to develop a catch-all formula that can be carried across the entire history of my data which will continue to be appended to each month and will only count the most recent set of consecutive failures for the given criteria.
I have a sample workbook I can provide with the data and formula if that would be helpful.
**enter as array formula (ctrl+shift+enter)
=MAX(FREQUENCY(IF(A5:A16<="1/11/2016",IF(B5:B16="apple",IF(C5:C16="banana",IF(D5:D16="Fail",ROW(D5:D16))))),IF(A5:A16>="1/11/2016",IF(B5:B16<>"apple",IF(C5:C16<>"banana",IF(D5:D16<>"Fail",ROW(D5:D16)))))))
1/01/2016 | apple | banana | Fail |
1/02/2016 | apple | banana | Fail |
1/03/2016 | apple | banana | Fail |
1/04/2016 | apple | banana | Pass |
1/05/2016 | apple | banana | Pass |
1/06/2016 | apple | banana | Pass |
1/07/2016 | apple | banana | Pass |
1/08/2016 | apple | banana | Pass |
1/09/2016 | apple | banana | Pass |
1/10/2016 | apple | banana | Pass |
1/11/2016 | apple | banana | Fail |
1/12/2016 | apple | banana | Fail |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>