Formula for counting most recent consecutive failures based on multiple criteria.

S

singha23

New Member
Joined
Nov 28, 2016
Messages
7
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)))))))

1/01/2016applebananaFail
1/02/2016applebananaFail
1/03/2016applebananaFail
1/04/2016applebananaPass
1/05/2016applebananaPass
1/06/2016applebananaPass
1/07/2016applebananaPass
1/08/2016applebananaPass
1/09/2016applebananaPass
1/10/2016applebananaPass
1/11/2016applebananaFail
1/12/2016applebananaFail

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Hi,


=MAX(FREQUENCY(IF(A5:A16<=DATE(2016,11,1),IF(B5:B16="apple",IF(C5:C16="banana",IF(D5:D16="Fail",ROW(D5:D16))))),IF(A5:A16<=DATE(2016,11,1),IF(B5:B16="apple",IF(C5:C16="banana",IF(D5:D16<>"Fail",ROW(D5:D16)))))))

<tbody>
</tbody>

Is that of any help to you?


Regards
 
Last edited:
Upvote 0
Thanks for your reply Canapone. The formula you provided gives a result of 3. I am trying to get a result of 1 from the formula because the November failure is the first one in the second sequence of failures. The earlier failures need to ideally be disregarded. Hopefully this makes sense.
 
Upvote 0
singha23 said:
Thanks for your reply Canapone. The formula you provided gives a result of 3. I am trying to get a result of 1 from the formula because the November failure is the first one in the second sequence of failures. The earlier failures need to ideally be disregarded. Hopefully this makes sense.
Click to expand...

No, it doesn't. One should never describe one's Excel problem in terms of an inappropriate or non-working formula. Better: describe in words how 1 obtains given the sample you posted. Is 1 a count or what?
 
Upvote 0
Hi singha23,

not sure:

Code: 
=SUM(--(N(IF(1,FREQUENCY(IF(A5:A16<=DATE(2016,11,1),IF(B5:B16="apple",IF(C5:C16="banana",IF(D5:D16="Fail",ROW(D5:D16))))),IF(A5:A16<=DATE(2016,11,1),IF(B5:B16="apple",IF(C5:C16="banana",IF(D5:D16<>"Fail",ROW(D5:D16))))))))>1))

to be confirmed with contr+shift+enter

To be tested...


Regards

--------

Sorry Aladin Akyurek, did not mean to overlap.
 
Last edited:
Upvote 0
Thanks for your replies guys. Canapone that gets pretty close to what I was after. It counts 1 failure for November and 2 failures for December. It doesn't appear to count the earlier set of failures though if I set the date back to February the failure count shows 1 for example instead of 2. To give a clearer idea of what I am hoping for the results of the formula I am hoping for would be as follows:

For some background, I have written a custom function in vba which gives the desired result but it is a little slow when calculating over a lot of rows so was hoping there was a way to do this with a formula. If the frequency formula is not the best way to do this open to any and all suggestions.

DateConsecutive fail count
1/01/20161
1/02/20162
1/03/20163
1/04/20160
1/05/20160
1/06/20160
1/07/20160
1/08/20160
1/09/20160
1/10/20160
1/11/20161
1/12/20162

<colgroup><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Hi again

the data you shared show 1 (group of) consecutive Fail <=february 2016 (not 2).
 
Last edited:
Upvote 0
Hi, I am hoping to have each individual failure counted but only when they are in a consecutive group. If you look at my previous post you will see what I was hoping the results would be for each row. Perhaps this isn't something that can be done with a formula and my custom function was the way to go?
 
Upvote 0
singha23 said:
Hi, I am hoping to have each individual failure counted but only when they are in a consecutive group. If you look at my previous post you will see what I was hoping the results would be for each row. Perhaps this isn't something that can be done with a formula and my custom function was the way to go?
Click to expand...

Still not clear why 1 is the desired count...
 
Upvote 0
For November, 1 is the desired count because in October the value was a "Pass". Therefore in counting consecutive 'Fails' this is the first in the sequence. December would be the second 'Fail' in that sequence. In the first group of consecutive fails the count would be 1 for January, 2 for February and 3 for March. April would be zero because that row is a 'Pass' and so on.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Consecutive formula, not counting most recent sequence
Replies
6
Views
187
jchick90
J
E
  • Solved
COUNTIF and SUMIF formula with multiple criteria
Replies
2
Views
128
excelnewb221
E
S
  • Solved
Lookup results in a table based on multiple criteria and set number of occurrences
Replies
4
Views
124
kevin9999
K
N
  • Question
Count - specific consecutive order
Replies
6
Views
512
Peter_SSs
Peter_SSs
TheMacroNoob
  • Solved
Multiple Criteria from Multiple Columns - Any Combination Match
Replies
4
Views
337
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,962
Messages
6,122,482
Members
449,088
Latest member
Melvetica

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top