Eliminating non-consecutive data

[Agamemnon_2002]

[TABLE="width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]10:00
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]10:30
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]11:00
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]11:30
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]12:00
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]12:30
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]13:00
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]13:30
[/TD]
[TD]14
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]14:00
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]14:30
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]15:00
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]15:30
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]16:00
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]16:30
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]17:00
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]17:30
[/TD]
[TD]14
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]18:00
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]18:30
[/TD]
[TD]14
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]19:00
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]19:30
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]20:00
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]20:30
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]21:00
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]21:30
[/TD]
[TD]8
[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

I've been trying to write a function to eliminate certain data in a COUNTIFS with no success
Suppose I have the above data that shows how many tasks are being performed at a certain time. I want to show the peak number of tasks, but do not want to include tasks that do not occur for one hour or less (less than 2 consecutive occurrences). In this set, the peak would occur at 11:30 with 13 tasks; 14 and 15 would be eliminated because they do not occur for more than an hour.

The best I can come up with is:
COUNTIF(A1:A24,MAX(A1:A24))>2)
This eliminates 15 because it only occurs twice, but still counts 14 because it occurs three times. I want to also eliminate 14 because it does not occur for more than one consecutive hour. I also see the possibility where data may occur like this: "...,14,15,15,14,...". In this case 14 would be counted because there would be 14 tasks occurring consecutively for more than one hour.

 

[PhilS2520]

Hi Agamemnon_2002,

So can we say that you want to look for “buckets” of time, and then see which “bucket” has the most tasks performed?

Could you rephrase your question, so that it can be better understood by people who are not working on your project!

Why is the peak at 11:30 with 13 but not 14:30 with 15? That is how do you find the time frame with the most tasks?

Why is the 15 eliminated because it only occures twice? 12 also occurred twice?

 

[Ron Coderre]

I couldn't build an elegant single-formula solution so I resorted to a helper column.
With your sample data in A1:B24

This helper-formula, copied down, counts consecutive values

C1: =IF(B1=B2,MATCH(0,INDEX(--(B1:B$23=B2:B$24),0),0),0)

And this regular formula returns the largest activity count that occurs for an hour or more:

D1: =INDEX(B1:B24,MATCH(MAX(INDEX((C1:C23>2)*B1:B23,0)),B1:B23,0))

Does that do what you want?

 

[Marcelo Branco]

I also see the possibility where data may occur like this: "...,14,15,15,14,...". In this case 14 would be counted because there would be 14 tasks occurring consecutively for more than one hour.

Maybe...

=MAX(INDEX(SUBTOTAL(5,OFFSET(B1,ROW(B1:B21)-ROW(B1),0,4)),0))

M.

 

[Agamemnon_2002]

Marcelo,

Thank you so much! The formula works perfectly. I only had to change the OFFSET height to 3.

 

[Marcelo Branco]

Marcelo,

Thank you so much! The formula works perfectly. I only had to change the OFFSET height to 3.

You are welcome. Thanks for the feedback.

M.

 

[Agamemnon_2002]

Marcelo's MAX INDEX SUBTOTAL OFFSET formula was exactly what I needed.

The data i provided was fictitious to simplify my problem. I also wrote Marcelo's formula into an INDEX MATCH formula to show what time this corrected peak first occurs.

What i was looking for was a way to find the MAX in column B, but I needed to find a way to eliminate essentially extraneous data that didn't really reflect the peak number of tasks (some tasks overlap and while at a certain time may show a peak of 14 or 15 in this case, they do not represent the true peak). Another way to look at it is concurrent jobs that begin and end at different times. I arbitrarily choose that tasks needed to be concurrent for 3 consecutive 30 minute periods to be counted as peak.

Hope this explained a bit better what i was looking for. I changed the height from 4 to 3 because the 4 was making the count require 4 consecutive 30 minute periods for peak instead of 3.