Counting amount of consecutive cells in a sequence of colored cells

[Artyomaa]

Hello!

I am working with the logging of shift reports on a construction site, and later i would like to use the data for statistical analysis. I have included a quick example of how my sheet is set up in excel, see the attached file. As you can see each activity consists of one or more intervals where work is performed.

I want excel to count the amount of cells (time spent (1 cel=5min)) in each interval during the entire timeframe, and for each activity.

So for instance, for the activity "sleeping", we have 4 intervals. In this case it is desired to make excel return something like [6 3 6 6], where the first value of 6 refers to the amount of cells in the first interval, during the timeframe for that activity (moving from left). The second value of 3 refers to the amount of cells in the second interval, etc...Dont mind the cell colors, they are irrelevant in this case, and i want to count all the intervals regardless of their color.

I have very limited expirience with excel, and im not even sure if this can be done. Further, i dont really know in what format it is best to get the desired output, the matrix form that i used in the example above is the first thing that came into my head. But what i can say, is that i need to be able to refer to these numbers as individual entries later on. And the amount of intervals for each activity may in some cases be quite large, since there could often be quite a few intervals spanning only 1-3 cells (=5-15 mins).

Might be worth noting that the cells within the timeframe are conditionally formatted to show blue color for the value of 1, and red for the value of 2. Its the most efficient way i found to color cells.

I hope i have supplied all the necessary details

Thanks in advance for your time!

[JeteMc]

Hello Artyomaa and Welcome to Excel Forum.
This proposed solution employs three helper tables which may be placed in an inconspicuous location, such as at the bottom of the attached spreadsheet.
The formula that populates the first table, which starts in cell D1048562, is:

Please Login or Register  to view this content.

The formula that populates the second table is:

Please Login or Register  to view this content.

The array entered formula* that populates the third table is:

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
The interval list in the range EX5:FB11 is populated with the values from the 3rd helper table.
Let us know if you have any questions.

[Artyomaa]

Hello, JeteMC!

Thanks for the welcoming

I had a quick glance at your proposition, and I must say this looks spot on! I will need some time to implement your formulas into my actual sheet, and make sure that it works like I want Ill try to do this by the end of the day tomorrow, and then tell you if anything comes up along the way.

Thanks alot for now!

[Artyomaa]

Let us know if you have any questions.

Hello again!

I have now attempted to implement the formulas into my worksheet, and i cant seem to make them function properly. I believe it's because of the column D in the yellow table, and column EQ in the green/brown table. These two columns refer to empty cells in your example, and im unable to find a matching location for them in my actual worksheet.

I have included an example of my actual worksheet, where i have tried to implement your formulas. Could you take a look at it, and correct the references of the formulas?

Note: The worksheet is supposed to contain all 7 days of the week, in the example i only have monday and tuesday.

[JeteMc]

The formulas are now working to report the intervals for the first set of 16 projects.
Let us know if you need help with the subsequent sets or if you have any questions.

[FlameRetired]

If I have the overall concept correctly this is a different approach. It returns the intervals for Operations in rows 5:20.

There is a helper column in column FD. Its function is to determine the variable number of rows that the FREQUENCY function returns in the final formula. Array entered in FD5 and filled down.

Please Login or Register  to view this content.

The intervals are returned with this array formula in FE5 filled down and across.

Please Login or Register  to view this content.

[Artyomaa]

Hello Dave!

You got the concept correctly This approach was easier for me to copy, so i've decided to use yours. However, i forgot to mention that i would prefer to see the values in the yellow table, in minutes. So esentially, all i want is to multiply all the yellow cells with 5. I tried this (multiplying your array formula with 5), but it gives me the #VALUE! error in cells that havent got any registered intervals. Is there a way to remove this error from these cells, and just leave them blank like they appear in the example you sent above?

[AliGW]

@Artyomaa

Please don't quote whole posts - it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below. Thanks!

[Artyomaa]

Let us know if you need help with the subsequent sets or if you have any questions.

Hello, again!

FlameRetired suggested an approach that was easier for me to copy, so i decided to go for his But thanks alot for your help and time! Much appreciated!

Best regards

[Artyomaa]

Please don't quote whole posts - it's just clutter

Got you! I will keep this in mind

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Artyomaa]

@AliGW

I still have a question pending here But i will mark it as solved as soon as it is

[JeteMc]

Try modifying the array entered formula* in cell FE5 so that it reads:

Please Login or Register  to view this content.

*Remember to activate by simultaneously pressing the Ctrl, Shift and Enter keys before you copy across and down.
Let us know if you have any questions.

[FlameRetired]

@ JeteMc

Yup! That does it. Thanks once again for the backup.

[JeteMc]

@Dave, Your Welcome. I hope that you have a blessed day.

[Artyomaa]

Try modifying the array entered formula* in cell FE5 so that it reads...

@JeteMc @FlameRetired

That did it for me! Thank you guys for all your great support! I am writing my master thesis, and your help now allows me to get data that I otherwise wouldnt be able to Again, thank you!

Best Regards

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

[FlameRetired]

You are welcome. Glad to help. Thank you for the feedback. This was a challenging puzzle so thank you for posting.

Master's thesis. Impressive.

[Artyomaa]

This was a challenging puzzle so thank you for posting.

You're welcome! Glad that i could offer a challenge

[Artyomaa]

@FlameRetired Hello!

I have been using the solution you presented to me in this thread for quite a bit now. It's still working very well, but i was wondering if it's possible to tweak the formulas a bit.

So, originally i wanted the formulas to count intervals regardless of which colour the cells had. But im wondering, is it difficult to change the formulas, so that they only count intervals based on consecutive cells that are green and grey?

I included an example to clarify what i want. If you check row 25 (operation 1), you will see that the cells have different colours. So in reference to the original case, we would have a total of 4 intervals. But now, i want the formulas to only take into account green and grey cells, when calculating the length of an interval. So for operation 1 in this case, the intervals would have to be 50 cells (only green cells), 29 cells (green+grey cells), 18 cells (green+grey cells). This is a total of 3 intervals, because 1 interval is only red cells.

Is it possible to tweak the array formulas that you previously made for me, to account for my new request? You can find the array formulas that you previously made for me, between columns LA and MX.

Things worth mentioning: The cells are coloured based on the numbers 1 (green), 2 (red), 3 (grey).

As always, if there is anything i need to clarify further, let me know

[FlameRetired]

In helper column KZ array enter this in KZ25 and fill down.

Please Login or Register  to view this content.

Then array enter this in LA25 and fill across to column MX.

Please Login or Register  to view this content.

[Artyomaa]

@FlameRetired

I have now implemented this tweak into my workbook. It seemingly works just like i wanted it to! Once again, thank you very much! I am truly greatful for all your support

Best regards

[FlameRetired]

You are welcome. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Artyomaa]

@FlameRetired

Hi, once again!

I have another request regarding the worbook that has been discussed in this thread. As always, i would be very happy if you could take a look at it

As you might recall, the workbook calculates interval durations for a number of operations. I am looking for a way to deal with intervals that last from 06:00 o'clock on one day and continue from 06:00 the next day. As of now, the interval counting formulas treat these occurences as two seperate intervals, while as a matter of fact it should be treated as one. Its not very hard to override this manually, but it would be much effective and user friendly if it could be automated. I included an example that explains my request in detail. I put in the explanation into a comment box that references to the interval in question, so make sure that all comment-boxes are visible

Big thanks in advance!

Best regards

[AliGW]

This is a very different query to the one you started the thread for, so please start a new thread with a new title. By all means give a link back to this thread, if you think it relevant. Thanks!

[FlameRetired]

Artyomaa I'll do my best to stay alert for your new thread.