(Solved) sum maximum consecutive 1 value in a calendar row
Hi,
I've created a calendar that puts a value of 1 if an employee has leave on that day. If they don't, it has a value of 0 in that day. I'm trying to find a formula that will return the maximum sum of consecutive 1's in that calendar row for the employee. For example, over a two year period, I want to return the highest continuos period on leave for that period.
Regards
Wen
Last edited by WenPeters; 02-09-2023 at 10:46 PM.
If your data in column
Formula:
=MAX(FREQUENCY(IF($A$2:$S$2>0,COLUMN($A$2:$S$2)),IF($A$2:$S$2<=0,COLUMN($A$2:$S$2))))
If in row
Formula:=MAX(FREQUENCY(IF(A2:A20>0,ROW(A2:A20)),IF(A2:A20<=0,ROW(A2:A20))))
one time pressing shift+ctrl+enter
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Hi avk, thanks for the quick response. I have tried that, but it seems to be just returning the sum of the row. I've attached a sample, fyi. Thanks again Wen
Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat
MP Employee ID 01-Dec 02-Dec 03-Dec 04-Dec 05-Dec 06-Dec 07-Dec 08-Dec 09-Dec 10-Dec 11-Dec 12-Dec 13-Dec 14-Dec 15-Dec 16-Dec 17-Dec 18-Dec 19-Dec 20-Dec 21-Dec 22-Dec 23-Dec 24-Dec 25-Dec 26-Dec 27-Dec 28-Dec 29-Dec 30-Dec 31-Dec Max consecutive Should be
00000001 1 0 0 0 1 1 0 0 0 0 0 1 1 1 1 1 0 0 0 1 1 0 0 0 0 0 0 1 1 1 0 13 5
00000002 0 1 0 0 0 0 0 1 1 0 0 0 0 0 0 1 0 0 0 0 1 1 1 0 0 0 0 0 0 0 0 7 3
00000003 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 5 3
00000004 0 1 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 1 1 0 0 0 0 0 0 1 0 0 0 6 2
00000006 0 0 0 0 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 1 0 0 0 1 0 1 1 1 1 13 7
Its better to attach sample file with original data with required output.
Hi avk, please find attachment. Thanks
ABD3=MAX(FREQUENCY(IF(B3:ABC3=1,COLUMN(B3:ABC3)),IF(B3:ABC3<>1,COLUMN(B3:ABC3))))
for office 365 enter
for other office control+shift+enter
copy down
Formula:=MAX(FREQUENCY(IF(B3:ABC3>0,COLUMN(B3:ABC3)),IF(B3:ABC3<=0,COLUMN(B3:ABC3))))
By pressing one time shift+ctrl+enter
Since asking maximum consecutive 1 value
with 365, another option:
=MAX(LEN(TEXTSPLIT(TEXTJOIN("",TRUE,B3:ABC3),0)))
Thankyou avk, Caracall and Gregb11 - much appreciated
You're welcome, and thanks for the rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
WenPeters 
Register To Reply
Bookmarks