Count of consecutive days

[fireman1417]

I'm trying to write a formula that displays the consecutive number of days an employee has worked, then start counting again after a blank cell occurs in the row of data. Our employees are required to take a fatigue day after working 13 consecutive days.
My data would be the employees down the first column, number of days worked consecutively in next column, then columns for each day, and simply put a 1 if they are present and blank if not present on that day.
So, if for Employee X, worked a range of days, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 (total of 13) he would have to take off the next day, then the formula would begin counting at 1 after the blank day. If he returned after a day off and has worked 4 more consecutive days 1, 1, 1, 1 my total would show 4 for this employee

 

[63falcondude]

How's this?

Excel 2010
	A	B	C	D	E	F	G
1	Employee	Consecutive Days Worked	1/1/2016	1/2/2016	1/3/2016	1/4/2016	1/5/2016
2	Mark	3	1		1	1	1
3	Luke	0	1	1	1	1
4	Sue	1	1		1		1
Sheet3

Cell Formulas
Range	Formula
B2	{=LOOKUP(9.99E+307,FREQUENCY(IF(C2:G2=1,COLUMN(C2:G2)),IF(1-(C2:G2=1),COLUMN(C2:G2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Source: http://www.mrexcel.com/forum/excel-questions/550976-counting-consecutive-cells-value-2.html

 

[fireman1417]

Thats exactly what I'm looking for, however I can't get the formula to work when I paste it in.
I did the Cntl+Shift+Enter. I also changed the cells in the series where you have C2:G2 in the formula. My dates start on G2 (8/16/16) and run through DI2 (11/30/16), I have employee name, and some other information in the preceeding columns. My result just stays 0 in the cell. Does the 9.99E+307 in the formula need to be something??

 

[63falcondude]

Thats exactly what I'm looking for, however I can't get the formula to work when I paste it in.
I did the Cntl+Shift+Enter. I also changed the cells in the series where you have C2:G2 in the formula. My dates start on G2 (8/16/16) and run through DI2 (11/30/16), I have employee name, and some other information in the preceeding columns. My result just stays 0 in the cell. Does the 9.99E+307 in the formula need to be something??

The 9.99E+307 stays the same. That is (close to) the largest number in Excel and is used in the LOOKUP function. I probably have to see your data to diagnose why the formula is not working.

 

[63falcondude]

Actually, I just realized why it isn't working. Your data goes into the future so it will have blanks after the current date and the formula sees the blanks as starting over (i.e. no longer consecutive). Just to check to make sure that this is the case, change your ranges to G2:U2 (assuming U2 is today's date of 8/30/2016). The formula will have to be modified to account for the blank future cells.

Maybe someone else can chime in here. Something that would limit the range based on the date should work. Something like =IF(G1:DI1<=TODAY(),...

 

[fireman1417]

That did it. I changed the formula to run through today and it shows the correct value up to today. Anyone know of a way to make it run up to a future date and still count so that I don't have to re-do the formula every day?

 

[skris]

Hi Guys

First post here!

I was unable to find any thread that outlines how to count the average consecutive number of consecutive cells of text. I have been able to work out how to count the largest and the smallest, but not the average.

Say I have a data series that has one column of data, each cell contains "Up" and "Down" - is there a way I could find out the average number of times "Up" occurs consecutively?

Thank you.