Question description:
This user has given permission to use the problem statement for this
blog.
Hi all,
Having a little drama with a roster redesign I'm doing.
Each employee has a row, 365 cells long, representing the entire year. Days off are marked "X", while days worked are filled with a numerical start time.
To ensure the employees are rostered as equally as possible, I would like a way to count the number of blocks of >7 consecutive shifts. What this means is the number of times there is at least 7 cells between two cells which contain "X".
The count is then used in a separate sheet to compare against other employees.
I've nutted out how to count the number of weekend shifts for my other analysis, but this one has me stumped.
Any help appreciated!
Solved by M. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
18/12/2017 - 06:26
hello
Excelchat Expert
18/12/2017 - 06:26
Thank you for choosing got it pro
Excelchat Expert
18/12/2017 - 06:26
how may I help you today
User
18/12/2017 - 06:26
the description above covers the problem.
Excelchat Expert
18/12/2017 - 06:27
I got it
Excelchat Expert
18/12/2017 - 06:27
do you have a file to share?
User
18/12/2017 - 06:27
just pasted some into the right panel
Excelchat Expert
18/12/2017 - 06:27
Okay
Excelchat Expert
18/12/2017 - 06:27
Let me just review everything here
User
18/12/2017 - 06:28
I need a formula to count along a row and give me the # of times there are blocks of >7 cells between cells which contain "X"
User
18/12/2017 - 06:29
So in the case to the right the result should be 1.
Excelchat Expert
18/12/2017 - 06:30
I just need to make sure I understand this
Excelchat Expert
18/12/2017 - 06:31
For ex. on week 1, I do see 2 "x", will that be counted as 1?
Excelchat Expert
18/12/2017 - 06:32
Or anything that is less than 7 in a single row is counted as one?
User
18/12/2017 - 06:32
I want to count how many times there are 7 or more cells containing anything other that "x" between cells which contain "x"
User
18/12/2017 - 06:34
so, it'd check along row 7. when it finds a cell containing "X", it counts the number of cells along the row until it finds another cell containing "X". Then, if it's 7 or more cells between "X", it increments the total by 1.
Excelchat Expert
18/12/2017 - 06:35
okay to confirm, 7X is equivalent to 1, correct?
User
18/12/2017 - 06:36
7 or more cells in a row (between "X"s) count as 1
Excelchat Expert
18/12/2017 - 06:36
7X in a row is = 1 count, is that right?
User
18/12/2017 - 06:36
no, 7*(anything other than X) = 1
Excelchat Expert
18/12/2017 - 06:38
is there any other number that is in the row aside from 7 and X?
Excelchat Expert
18/12/2017 - 06:39
Okay, I think I got it now
User
18/12/2017 - 06:40
there's X, which means day off
User
18/12/2017 - 06:40
and then a number between 0-24, which represent the start times of the shift
Excelchat Expert
18/12/2017 - 06:41
ex: from G7 the number of cells with "7" before tha next cell with "X" is 8, so that should be counted as 1, is that correct?
User
18/12/2017 - 06:41
yes, that's ight
User
18/12/2017 - 06:41
right
Excelchat Expert
18/12/2017 - 06:42
okay, got it
Excelchat Expert
18/12/2017 - 06:42
give me few minutes to figure this out
User
18/12/2017 - 06:42
thanks man
Excelchat Expert
18/12/2017 - 06:42
please extend the chat while I am working on this
User
18/12/2017 - 06:42
how do I do that?
Excelchat Expert
18/12/2017 - 06:43
I will try to create the formula then I'll explain it once done
User
18/12/2017 - 06:43
ok
User
18/12/2017 - 06:46
Row 7 should return 1
User
18/12/2017 - 06:46
Row 9 should return 2
User
18/12/2017 - 06:46
just for example
Excelchat Expert
18/12/2017 - 06:46
the whole row?
Excelchat Expert
18/12/2017 - 06:47
ok I got it
Excelchat Expert
18/12/2017 - 06:47
cause row 7 contain more than 7 working days
Excelchat Expert
18/12/2017 - 06:47
got that
User
18/12/2017 - 06:47
yep
User
18/12/2017 - 06:48
and row 9 has two blocks of >7 shifts in a row
Excelchat Expert
18/12/2017 - 06:48
still trying to create formula functions
Excelchat Expert
18/12/2017 - 06:48
got it
Excelchat Expert
18/12/2017 - 06:55
give me few more minutes please
User
18/12/2017 - 06:56
no worries
User
18/12/2017 - 07:03
just extended again
Excelchat Expert
18/12/2017 - 07:03
Almost there
Excelchat Expert
18/12/2017 - 07:21
May I ask, the number of weeks in single row should be a maximum of 4, correct?
User
18/12/2017 - 07:21
no, would be 52
User
18/12/2017 - 07:21
the whole year
User
18/12/2017 - 07:22
on my sheet, the year runs from column F to column NE
Excelchat Expert
18/12/2017 - 07:23
ok, so your putting the whole year in a single row?
User
18/12/2017 - 07:23
yes
Excelchat Expert
18/12/2017 - 07:24
Okay since we only have few minutes left
Excelchat Expert
18/12/2017 - 07:24
Can I send you the solution via email?
Excelchat Expert
18/12/2017 - 07:24
If so can I have your email address
User
18/12/2017 - 07:24
please do
User
18/12/2017 - 07:25
email is stringerdylanm@gmail.com
Excelchat Expert
18/12/2017 - 07:25
what is your email
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
