Agamemnon_2002
New Member
- Joined
- Jul 7, 2018
- Messages
- 3
A | B | |
1 | 10:00 | 8 |
2 | 10:30 | 9 |
3 | 11:00 | 12 |
4 | 11:30 | 13 |
5 | 12:00 | 13 |
6 | 12:30 | 13 |
7 | 13:00 | 13 |
8 | 13:30 | 14 |
9 | 14:00 | 13 |
10 | 14:30 | 15 |
11 | 15:00 | 15 |
12 | 15:30 | 13 |
13 | 16:00 | 13 |
14 | 16:30 | 13 |
15 | 17:00 | 13 |
16 | 17:30 | 14 |
17 | 18:00 | 13 |
18 | 18:30 | 14 |
19 | 19:00 | 13 |
20 | 19:30 | 13 |
21 | 20:00 | 13 |
22 | 20:30 | 12 |
23 | 21:00 | 10 |
24 | 21:30 | 8 |
<tbody>
</tbody>
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.