Longest streak?

salange

Board Regular
Joined
Mar 4, 2002
Messages
62
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!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBN
3Weekday Max Streak
4VolunteerWeek Max Streak1234567Overall Max StreakMon 12/01/08Tue 12/02/08Wed 12/03/08Thu 12/04/08Fri 12/05/08Sat 12/06/08Sun 12/07/08Mon 12/08/08Tue 12/09/08Wed 12/10/08Thu 12/11/08Fri 12/12/08Sat 12/13/08Sun 12/14/08Mon 12/15/08Tue 12/16/08Wed 12/17/08Thu 12/18/08Fri 12/19/08Sat 12/20/08Sun 12/21/08Mon 12/22/08Tue 12/23/08Wed 12/24/08Thu 12/25/08Fri 12/26/08Sat 12/27/08Sun 12/28/08Mon 12/29/08Tue 12/30/08Wed 12/31/08Thu 1/01/09Fri 1/02/09Sat 1/03/09Sun 1/04/09Mon 1/05/09Tue 1/06/09Wed 1/07/09Thu 1/08/09Fri 1/09/09Sat 1/10/09Sun 1/11/09Mon 1/12/09Tue 1/13/09Wed 1/14/09Thu 1/15/09Fri 1/16/09Sat 1/17/09Sun 1/18/09Mon 1/19/09Tue 1/20/09Wed 1/21/09Thu 1/22/09Fri 1/23/09Sat 1/24/09Sun 1/25/09
5Abe541522223152141321144121221341432412
6Bob3113312242552354145523432435553
7Cal313122127213251442455354242322353
8Don31222233321321113152314542234124413
9Eve441211225434311434313142325435522
Sheet59
Cell Formulas
RangeFormula
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]
 
Upvote 0
I think this worked perfectly (once I entered it as an array CSE formula). Thank you so much!
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,970
Messages
6,175,710
Members
452,667
Latest member
vanessavalentino83

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