Count # of Sequences in a range

I

Innsbruck

New Member
Joined
Oct 2, 2013
Messages
15
Hi there,

I have a huge data set (10 units and 5 years data set) that contains for each unit an indicator whether a unit is fully or partially operational or zero if not in operation. I am interested to know the number of blocks (a block is defined as a sequence of fully or partially operational days) and the average operational days within a year.

For the sample data below, Unit 1 has 2 blocks and Unit 2 has 3 blocks. Average Operational days for Unit 1 is (1.5+1.8)/2 = 1.65 days

Is this doable in Excel or Excel VBA ?

Any help in this is much appreciated, thanks in advance.

[TABLE="width: 204"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Unit 1[/TD]
[TD]Unit 2[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/12/2019[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD="align: right"]1/13/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/15/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/16/2019[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]0.2[/TD]
[/TR]
[TR]
[TD="align: right"]1/17/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/19/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Try this:


Excel 2010
ABC
1DateUnit 1Unit 2
21/11/201910
31/12/20190.50.5
41/13/201901
51/14/201900
61/15/201910
71/16/20190.80.2
81/17/201901
91/18/201900
101/19/201901
11
121.651.233333
Sheet3
Cell Formulas
RangeFormula
B12=SUM(B2:B10)/COUNTIF(B2:B10,1)
C12=SUM(C2:C10)/COUNTIF(C2:C10,1)
 
Upvote 0
Try:


Book1
ABCDEFGH
1DateUnit 1Unit 2BlocksAverage Operational Days
21/11/201910Unit 1Unit 2Unit 1Unit 2
31/12/20190.50.5231.651.233333
41/13/201901
51/14/201900
61/15/201910
71/16/20190.80.2
81/17/201901
91/18/201900
101/19/201901
Sheet5
Cell Formulas
RangeFormula
G3=SUM(B:B)/E3
H3=SUM(C:C)/F3
E3{=SUM(SIGN(FREQUENCY(IF(B2:B10>0,ROW(B2:B10)),IF(B2:B10=0,ROW(B2:B10)))))}
F3{=SUM(SIGN(FREQUENCY(IF(C2:C10>0,ROW(C2:C10)),IF(C2:C10=0,ROW(C2:C10)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.


Same basic idea as kweaver, but the formula I use to count the number of blocks doesn't assume that there is exactly one "1" per block.
 
Upvote 0
kweaver said:
Try this:

Excel 2010
ABC

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Unit 1[/TD]
[TD="align: center"]Unit 2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/11/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/12/2019[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/13/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/14/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1/15/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1/16/2019[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]0.2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1/17/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1/18/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1/19/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1.65[/TD]
[TD="align: right"]1.233333[/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B12[/TH]
[TD="align: left"]=SUM(B2:B10)/COUNTIF(B2:B10,1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C12[/TH]
[TD="align: left"]=SUM(C2:C10)/COUNTIF(C2:C10,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Yes it works on the sample data but the case I have it could be a longer sequence of ones then followed by either a value between (0.1 and 0.9) or zero.

I apologize for the confusion :( I have corrected the sample data to account for such cases)


[TABLE="width: 204"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Unit 1[/TD]
[TD]Unit 2[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/12/2019[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD="align: right"]1/13/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/15/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/16/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/17/2019[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]0.2[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/19/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
And, what would you expect the averages to be in each case of your new sample?
 
Upvote 0
Eric W said:
Try:

ABCDEFGH
DateUnit 1Unit 2BlocksAverage Operational Days
Unit 1Unit 2Unit 1Unit 2

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/11/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/12/2019[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1.65[/TD]
[TD="align: right"]1.233333[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/13/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/14/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1/15/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1/16/2019[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1/17/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1/18/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1/19/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5​

[TABLE="width: 1132"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 1120"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD]=SUM(B:B)/E3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD]=SUM(C:C)/F3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1132"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 1120"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD]{=SUM(SIGN(FREQUENCY(IF(B2:B10>0,ROW(B2:B10)),IF(B2:B10=0,ROW(B2:B10)))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD]{=SUM(SIGN(FREQUENCY(IF(C2:C10>0,ROW(C2:C10)),IF(C2:C10=0,ROW(C2:C10)))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Same basic idea as kweaver, but the formula I use to count the number of blocks doesn't assume that there is exactly one "1" per block.
Click to expand...


Thank you Eric, this works perfectly much appreciated :)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
365
STARL0RD
S
T
  • Question Question
Converting Time Start/Stop Ranges to Presence/Absence
Replies
6
Views
255
myall_blues
myall_blues
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
912
Legacy 352679
L
A
  • Question Question
Lookup Material Name Material Unit/Day Consumed Duration and accumulate and compact abstract to the destination
Replies
2
Views
87
abuharvey
A
GMC The Macro Man
  • Question Question
SEQUENCE formula
Replies
0
Views
122
GMC The Macro Man
GMC The Macro Man

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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