I have a very large spreadsheet of dates and hours worked. I am trying to count the maximum number of consecutive days worked regardless of the hours worked in the day. The dates and hours are in a row rather than a column as exampled below.
Here is an example:
17 Feb 2013 8
18 Feb 2013 8
19 Feb 2013 10
20 Feb 2013
21 Feb 2013 11
22 Feb 2013 8
23 Feb 2013 9
24 Feb 2013 10
25 Feb 2013 7
26 Feb 2013 9
27 Feb 2013
28 Feb 2013
01 Mar 2013 8
02 Mar 2013 8
03 Mar 2013 9
= 6 consecutive days
Assuming the dates are always sequential,The curly braces indicate that the formula MUST be confirmed with Ctrl+Shift+Enter -- that's what makes them appear in the formula bar.
----A---- --B-- C D -------------------------------------E------------------------------------- 1 Date Hours 2 17-Feb-13 8 6 D2: {=MAX(FREQUENCY(IF(B2:B16>0, ROW(B2:B16)), IF(B2:B16=0, ROW(B2:B16))))} 3 18-Feb-13 8 4 19-Feb-13 10 5 20-Feb-13 6 21-Feb-13 11 7 22-Feb-13 8 8 23-Feb-13 9 9 24-Feb-13 10 10 25-Feb-13 7 11 26-Feb-13 9 12 27-Feb-13 13 28-Feb-13 14 01-Mar-13 8 15 02-Mar-13 8 16 03-Mar-13 9
Entia non sunt multiplicanda sine necessitate
Unfortunately it didn't work.
I changed the formula to: {=MAX(FREQUENCY(IF(D9:GB9>0, ROW(D9:GB9)), IF(D9:GB9=0, ROW(D9:GB9))))} with the ctrl shift enter
It game me a answer of 152 and should have been 34 (in the line example I tested)
All of the non-work days are blank - would that have caused the error?
See attached.
Last edited by Delleeb; 08-26-2013 at 03:21 PM. Reason: Attached example.
If the data is disposed in rows, change ROW to COLUMN.
EDIT:
----GC---- --GD--- ----------------------------------------GE---------------------------------------- 1 Max Consec Formula 2 34.00 34.00 GD2: {=MAX(FREQUENCY(IF(E2:GC2>0, COLUMN(E2:GC2)), IF(E2:GC2=0, COLUMN(E2:GC2))))} 3 27.00 27.00
Last edited by shg; 08-26-2013 at 03:47 PM.
You Rock!! It works - thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks