Longest streak?

[salange]

I have years worth of data in the following form: each row is a volunteer, each column is a day, and the data are either blanks (signifying not volunteering that day) or numbers (signifying the number of shifts that volunteer worked that day).

What I would like is a formula that will look across the entire data and, for each volunteer, calculate the longest streak that vol has of volunteering on a given day of the week. So in other words, if Bob never came on a weekday, came three times in a row on Saturday, and came 12 times on Sunday 10 of which were consecutive, the formula would return "10."

I'd love one formula to cover all the days, but if it needs to be one formula for each day and then I just add a colum to take the maximum of those 7 columns, that's fine too.

We're a wildlife rehabilitation center, so you'll be helping save injured wild animals if you can give me an answer. Thanks in advance for any help!

 

[DRSteele]

I think I can help, even though I eat a lot of animals.

Copy B5 and J5 down, then C5 down and across. This C5 formula finds the longest streak of numbers in a cell per weekday (with Monday being day1) for all the data per Volunteer.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN
3			Weekday Max Streak
4	Volunteer	Week Max Streak	1	2	3	4	5	6	7	Overall Max Streak	Mon 12/01/08	Tue 12/02/08	Wed 12/03/08	Thu 12/04/08	Fri 12/05/08	Sat 12/06/08	Sun 12/07/08	Mon 12/08/08	Tue 12/09/08	Wed 12/10/08	Thu 12/11/08	Fri 12/12/08	Sat 12/13/08	Sun 12/14/08	Mon 12/15/08	Tue 12/16/08	Wed 12/17/08	Thu 12/18/08	Fri 12/19/08	Sat 12/20/08	Sun 12/21/08	Mon 12/22/08	Tue 12/23/08	Wed 12/24/08	Thu 12/25/08	Fri 12/26/08	Sat 12/27/08	Sun 12/28/08	Mon 12/29/08	Tue 12/30/08	Wed 12/31/08	Thu 1/01/09	Fri 1/02/09	Sat 1/03/09	Sun 1/04/09	Mon 1/05/09	Tue 1/06/09	Wed 1/07/09	Thu 1/08/09	Fri 1/09/09	Sat 1/10/09	Sun 1/11/09	Mon 1/12/09	Tue 1/13/09	Wed 1/14/09	Thu 1/15/09	Fri 1/16/09	Sat 1/17/09	Sun 1/18/09	Mon 1/19/09	Tue 1/20/09	Wed 1/21/09	Thu 1/22/09	Fri 1/23/09	Sat 1/24/09	Sun 1/25/09
5	Abe	5	4	1	5	2	2	2	2	3	1						5	2	1		4	1			3		2	1	1			4		4			1	2		1	2		2					1	3		4	1	4		3		2	4	1		2
6	Bob	3	1	1	3	3	1	2	2	4												2	5				5	2		3	5	4		1	4			5			5		2			3			4		3				2	4		3		5	5	5	3
7	Cal	3	1	3	1	2	2	1	2	7				2				1	3		2	5	1	4			4				2		4		5										5		3	5	4					2			4		2	3	2	2	3	5	3
8	Don	3	1	2	2	2	2	3	3	3		2	1		3		2		1	1					1				3				1	5	2		3		1			4	5	4				2			2	3			4	1	2		4					4	1	3
9	Eve	4	4	1	2	1	1	2	2	5	4	3	4		3			1			1		4	3	4		3		1	3	1	4	2								3	2		5			4				3	5			5							2			2
Sheet59

Cell Formulas
Range	Formula
B5	=MAX(C5:I5)
C5	=MAX(FREQUENCY(IF(WEEKDAY($K$4:$BN$4,2)=C$4,IF(ISNUMBER($K5:$BN5),COLUMN($K$4:$BN$4))),IF(WEEKDAY($K$4:$BN$4,2)=C$4,IF(NOT(ISNUMBER($K5:$BN5)),COLUMN($K$4:$BN$4)))))
J5	=MAX(FREQUENCY(IF(ISNUMBER($K5:$BN5),COLUMN($K$4:$BN$4)),IF(NOT(ISNUMBER($K5:$BN5)),COLUMN($K$4:$BN$4))))

[/FONT]

 

[salange]

I think this worked perfectly (once I entered it as an array CSE formula). Thank you so much!

 

[salange]

Oooo, one more request: any chance you could provide a formula to identify the longest "currently active" streak on each day? In other words, the longest streak still going as of the most recent Mon/Tues/Wed/Th/Fri/Sat/Sun entered in the sheet?

Thanks in advance if you can!

 

[DRSteele]

I have several workbooks containing formulas that report current streaks based on conditions. I am having difficulty adapting them to this topic, but when I get it sussed out I will surely post a reply for you.

 

[salange]

I have several workbooks containing formulas that report current streaks based on conditions. I am having difficulty adapting them to this topic, but when I get it sussed out I will surely post a reply for you.

That would be awesome. Thanks in advance!