days without incident, counting method

[slxia1]

pass
pass
pass
fail
pass
pass
fail
pass

days without incident is 1.
What is a simple way to write the code for it?

[FDibbins]

how do you arrive at 1? i see 6 passes and 2 fails?

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Moo the Dog]

Don't have time to figure out how to do it in one cell, but if you want to use a helper column, try this:

With your pass/fail entries in cells A1:A100, put this in B1 and fill down: =IF(ISBLANK(A1),"",COUNTIF($A$1:A1,"fail"))
Then, in any other cell, enter this formula to get the Days without incident: =COUNT(B1:B100)-MATCH(MAX(B1:B100),B1:B100,0)

- Moo

*EDIT - SEE MY NEXT POST FOR A SOLUTION THAT DOES NOT USE A HELPER COLUMN. IT IS ENTIRELY IN ONE CELL

[Moo the Dog]

OK, figured it out so it can be done in 1 cell, without using a helper column. This formula is an array formula, and needs to be applied by holding down Ctrl + Shift keys and hitting Enter:

Again, it assume your pass/fail list is in cells A1:A100, put this formula in any cell NOT in range A1:A100:

Please Login or Register  to view this content.

- Moo

* Of course, feel free to change the ranges as per your own requirements

[slxia1]

Array causes the spread sheet to a bit more complex. But it still a pretty good solution. There must be a way to count the position of the latest Fail?

[FDibbins]

you could add a helper that uses countif() to incrementally increases the count, then index/match or something like that to return the position?

[Moo the Dog]

I'm confused, how is adding one array formula to one individual cell making the sheet a bit more complex? With the formula below you don't need a helper column or anything, just enter it in almost any cell (just not in the range it is checking), adjust the ranges to suit, and hit Ctrl + Shift + Enter.

=COUNTA(A1:A100)-MAX((A1:A100="fail")*(ROW(A1:A100)))

- Moo

[Moo the Dog]

Here is a working example. {See attached file}
- Moo

[slxia1]

Wow, just Wow. Thanks Moo. Sorry when you said array, I thought I had to copy the formular down next to every date.

[Moo the Dog]

You're welcome. Sorry if my explanation was confusing. That is what an array formula replaces. Instead of entering formulas down an entire column, you can enter an array formula that can do it in one cell. It's not always possible - or appropriate - but in small doses, it can be quite useful.

- Moo

[slxia1]

Can you please take a look at the adjustments I made on your spreadsheet.
I have two problems hoping for your help.
1. I copy and pasted EXACTLY the same formula from d10 to e10. but answer is different?
2. I moved the Entire data set and formula to another location (sheet 2), with adjusted ranges. yet it gives wrong result.

adjustments to moo.xls

I actually thought I understood the row() array, but do I am doubting myself now.

[Moo the Dog]

The row() argument must always think it is starting at row 1. Since your range starts in row 13, you will need to account for the 12 rows above the starting range, so your formula should read:

=COUNTA(J13:J29)-MAX((J13:J29="fail")*(ROW(J13:J29)-12)) applied using Ctrl + Shift + Enter, not just Enter.

So if you wanted a range of J33:J100, your formula would look like this: =COUNTA(J33:J100)-MAX((J33:J100="fail")*(ROW(J33:J100)-32))

Hope that explains it.

- Moo

[slxia1]

Omg it worked!

[Moo the Dog]

I'm torn.. do I feel happy that it works, or sad that you doubted me? LOL jk

Glad to help

- Moo

* Thanks for the rep, too!