Counting Min-Max of Sequence Events and its Number of Cycles

Mujubie717

Mujubie717

New Member
Joined
Jul 23, 2023
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Hello Everyone, :)
Good morning/afternoon/evening,
I am trying to count the duration of the sequence events of (active or 1) or (idle or 0), get the results from the trial & error experiment. Finally, I got the result from the maximum values of both events only.
Unfortunately, after several times beeping the formula, I couldn't find any better answer to fulfill my expected result of the minimum duration of both active and idling events. Further, I also want to count the total cycle of both events (without any help Column).

Effort and help are much appreciated.
Thanks in advance.


ASK2.xlsx
ABCDEFGHIJKLMNOPQRSTUV
19The longest Active [1]=MAX(FREQUENCY(IF(B7:B57<>0,ROW(B7:B57)),IF(B7:B46=0,ROW(B7:B57))))
20The shortest Active [1]=MIN(FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B47=0,ROW(B8:B57))))
37The longest Idle time [0]=MAX(FREQUENCY(IF(B7:B57<>0,"",ROW(B7:B57)),IF(B7:B57=0,"",ROW(B7:B57)))) atau '=MAX(FREQUENCY(IF(B7:B57<>1,ROW(B7:B57)),IF(B7:B57=1,ROW(B7:B57))))
40The shortest idle time [0]=MIN(FREQUENCY(IF(B8:B57<>0,"",ROW(B8:B57)),IF(B8:B57=0,"",ROW(B8:B457))))
5Number active cycles [1]
6Number idling cycles [0]
7
810
921
1031
1141
1251
1361
1471
1581
1690
17100
18110
19121
20131
21141
22150
23160
24171
25181
26191
27200
28210
29220
30231
31241
32251
33260
34270
35281
36291
37301
38310
39320
40331
41341
42350
43360
44370
45380
46390
47400
48410
49421
50431
51441
52451
53461
54471
55481
56491
57501
AKTIF_STOP
Cell Formulas
RangeFormula
B1B1=MAX(FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B47=0,ROW(B8:B57))))
B2B2=MIN(FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B47=0,ROW(B8:B57))))
B3B3=MAX(FREQUENCY(IF(B8:B57<>0,"",ROW(B8:B57)),IF(B8:B57=0,"",ROW(B8:B457))))
B4B4=MIN(FREQUENCY(IF(B8:B57<>0,"",ROW(B8:B57)),IF(B8:B57=0,"",ROW(B8:B457))))
A9:A57A9=A8+1
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B8:B48Other TypeColor scaleNO
B49:B57Other TypeColor scaleNO
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
As in
Excel Formula: 
=MIN(IF(FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B57=0,ROW(B8:B57)))<>0,FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B57=0,ROW(B8:B57))),""))
Excel Formula: 
=SUM(SIGN(FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B57=0,ROW(B8:B57)))))?
 
Upvote 0
Anonymous1378 said:
As in
Excel Formula: 
=MIN(IF(FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B57=0,ROW(B8:B57)))<>0,FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B57=0,ROW(B8:B57))),""))
Excel Formula: 
=SUM(SIGN(FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B57=0,ROW(B8:B57)))))?
Click to expand...
Thank you for your effort, For B2 and also B5 and B6, your answer formula has worked perfectly. (y)
However, for the B4, the shortest idle is still counting zero (0) after I changed the reference row. Much appreciation for the effort.
 
Upvote 0
Anonymous1378 said:
Did you change all the 0 to 1? The 0 in the IF condition needs to remain as 0.
Click to expand...
Hi, thanks for the quick response.
If i change all the signs of "<>" into 1 (from 0) and left the "=0" then the result is 1, if I change all the sign from (previous 0) into 1, the the result is 0.
----
Could you write it down again for the B4?
much thank you (y)
 
Upvote 0
Mujubie717 said:
Hi, thanks for the quick response.
If i change all the signs of "<>" into 1 (from 0) and left the "=0" then the result is 1, if I change all the sign from (previous 0) into 1, the the result is 0.
----
Could you write it down again for the B4?
much thank you (y)
Click to expand...
The problem is solved now. Much appreciation to you. #Thumbs
 
Upvote 0
Mujubie717 said:
Hi, thanks for the quick response.
If i change all the signs of "<>" into 1 (from 0) and left the "=0" then the result is 1, if I change all the sign from (previous 0) into 1, the the result is 0.
----
Could you write it down again for the B4?
much thank you (y)
Click to expand...

Excel Formula: 
=MIN(IF(FREQUENCY(IF(B8:B57<>1,ROW(B8:B57)),IF(B8:B57=1,ROW(B8:B57)))<>0,FREQUENCY(IF(B8:B57<>1,ROW(B8:B57)),IF(B8:B57=1,ROW(B8:B57))),""))
 
Upvote 0
Solution
The problem is solved now.
thanks for the help and much effort to member https://www.mrexcel.com/board/members/anonymous1378.507412/

the final version would be like this,
ASK2.xlsx
ABCDEFGHIJKLMNOPQRSTUV
19The longest Active [1]=MAX(FREQUENCY(IF(B7:B57<>0,ROW(B7:B57)),IF(B7:B46=0,ROW(B7:B57))))
22The shortest Active [1]=MIN(IF(FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B57=0,ROW(B8:B57)))<>0,FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B57=0,ROW(B8:B57))),""))
37The longest Idle time [0]=MAX(FREQUENCY(IF(B7:B57<>0,"",ROW(B7:B57)),IF(B7:B57=0,"",ROW(B7:B57)))) atau '=MAX(FREQUENCY(IF(B7:B57<>1,ROW(B7:B57)),IF(B7:B57=1,ROW(B7:B57))))
41The shortest idle time [0]=MIN(IF(FREQUENCY(IF(B8:B57<>1,ROW(B8:B57)),IF(B8:B57=1,ROW(B8:B57)))<>0,FREQUENCY(IF(B8:B57<>1,ROW(B8:B57)),IF(B8:B57=1,ROW(B8:B57))),""))
57Number active cycles [1]=SUM(SIGN(FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B57=0,ROW(B8:B57)))))
67Number idling cycles [0]=SUM(SIGN(FREQUENCY(IF(B8:B57<>1,ROW(B8:B57)),IF(B8:B57=1,ROW(B8:B57)))))
7
810
921
1031
1141
1251
1361
1471
1581
1690
17100
18110
19121
20131
21141
22150
23160
24171
25181
26191
27200
28210
29220
30231
31241
32251
33260
34270
35281
36291
37301
38310
39320
40331
41341
42350
43360
44370
45380
46390
47400
48410
49421
50431
51441
52451
53461
54471
55481
56491
57501
AKTIF_STOP
Cell Formulas
RangeFormula
B1B1=MAX(FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B47=0,ROW(B8:B57))))
B2B2=MIN(IF(FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B57=0,ROW(B8:B57)))<>0,FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B57=0,ROW(B8:B57))),""))
B3B3=MAX(FREQUENCY(IF(B8:B57<>0,"",ROW(B8:B57)),IF(B8:B57=0,"",ROW(B8:B457))))
B4B4=MIN(IF(FREQUENCY(IF(B8:B57<>1,ROW(B8:B57)),IF(B8:B57=1,ROW(B8:B57)))<>0,FREQUENCY(IF(B8:B57<>1,ROW(B8:B57)),IF(B8:B57=1,ROW(B8:B57))),""))
B5B5=SUM(SIGN(FREQUENCY(IF(B8:B57<>0,ROW(B8:B57)),IF(B8:B57=0,ROW(B8:B57)))))
B6B6=SUM(SIGN(FREQUENCY(IF(B8:B57<>1,ROW(B8:B57)),IF(B8:B57=1,ROW(B8:B57)))))
A9:A57A9=A8+1
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B8:B48Other TypeColor scaleNO
B49:B57Other TypeColor scaleNO
 
Upvote 0
Upvote 0
smozgur said:
The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.
Click to expand...
Hi, thanks for letting me know about.
Your suggestion has already been done.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Mujubie717
  • Question Question
How to generate random events (active=1) and (idle=0) in the specified occurrences
Replies
1
Views
470
Mujubie717
Mujubie717
M
  • Solved
Count consecutive number with largest value and sum this
2
Replies
16
Views
1K
meohen1992
M
nostradamus
  • Question Question
Max Min Difference from Range from previous Cell Reference
Replies
1
Views
536
nostradamus
nostradamus
W
  • Solved
Count the Number of Unique Sequences
Replies
6
Views
666
William53
W
L
  • Question Question
partial text frequency counting
Replies
1
Views
592
Fluff
Fluff

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top