pass
pass
pass
fail
pass
pass
fail
pass
days without incident is 1.
What is a simple way to write the code for it?
pass
pass
pass
fail
pass
pass
fail
pass
days without incident is 1.
What is a simple way to write the code for it?
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.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
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
Last edited by Moo the Dog; 12-05-2012 at 11:43 AM.
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:Formula:
Please Login or Register to view this content.
- Moo
* Of course, feel free to change the ranges as per your own requirements
Last edited by Moo the Dog; 12-05-2012 at 01:34 AM.
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?
you could add a helper that uses countif() to incrementally increases the count, then index/match or something like that to return the position?
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
Here is a working example. {See attached file}
- Moo
Wow, just Wow. Thanks Moo. Sorry when you said array, I thought I had to copy the formular down next to every date.
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![]()
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.
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
Omg it worked!
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks