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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
340
STARL0RD
S
T
  • Question Question
Converting Time Start/Stop Ranges to Presence/Absence
Replies
6
Views
252
myall_blues
myall_blues
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
888
Legacy 352679
L
GMC The Macro Man
  • Question Question
SEQUENCE formula
Replies
0
Views
106
GMC The Macro Man
GMC The Macro Man
L
  • Solved
Looking for an sequence of consecutive days within an array of dates while ignoring weekends
Replies
6
Views
915
Anonymous1378
A

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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