Count # of Sequences in a range

[Innsbruck]

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.

Date	Unit 1	Unit 2
1/11/2019	1	0
1/12/2019	0.5	0.5
1/13/2019	0	1
1/14/2019	0	0
1/15/2019	1	0
1/16/2019	0.8	0.2
1/17/2019	0	1
1/18/2019	0	0
1/19/2019	0	1

<tbody>
</tbody>

 

[kweaver]

Is the average for Unit 2 = 1.2333?

 

[Innsbruck]

Is the average for Unit 2 = 1.2333?

Yes by averaging the result of summing each block then dividing by the # of blocks

 

[kweaver]

Try this:

Excel 2010
	A	B	C
1	Date	Unit 1	Unit 2
2	1/11/2019	1	0
3	1/12/2019	0.5	0.5
4	1/13/2019	0	1
5	1/14/2019	0	0
6	1/15/2019	1	0
7	1/16/2019	0.8	0.2
8	1/17/2019	0	1
9	1/18/2019	0	0
10	1/19/2019	0	1
11
12		1.65	1.233333
Sheet3

Cell Formulas
Range	Formula
B12	=SUM(B2:B10)/COUNTIF(B2:B10,1)
C12	=SUM(C2:C10)/COUNTIF(C2:C10,1)

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H
1	Date	Unit 1	Unit 2		Blocks		Average Operational Days
2	1/11/2019	1	0		Unit 1	Unit 2	Unit 1	Unit 2
3	1/12/2019	0.5	0.5		2	3	1.65	1.233333
4	1/13/2019	0	1
5	1/14/2019	0	0
6	1/15/2019	1	0
7	1/16/2019	0.8	0.2
8	1/17/2019	0	1
9	1/18/2019	0	0
10	1/19/2019	0	1
Sheet5

Cell Formulas
Range	Formula
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.

 

[Innsbruck]

Try this:

Excel 2010

	A	B	C
1	Date	Unit 1	Unit 2
2	1/11/2019	1	0
3	1/12/2019	0.5	0.5
4	1/13/2019	0	1
5	1/14/2019	0	0
6	1/15/2019	1	0
7	1/16/2019	0.8	0.2
8	1/17/2019	0	1
9	1/18/2019	0	0
10	1/19/2019	0	1
11
12		1.65	1.233333

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula B12 =SUM(B2:B10)/COUNTIF(B2:B10,1) C12 =SUM(C2:C10)/COUNTIF(C2:C10,1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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)

Date	Unit 1	Unit 2
1/11/2019	1	0
1/12/2019	0.5	0.5
1/13/2019	0	1
1/14/2019	0	0
1/15/2019	1	0
1/16/2019	1	0
1/17/2019	0.8	0.2
1/18/2019	0	1
1/19/2019	0	1

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[kweaver]

And, what would you expect the averages to be in each case of your new sample?

 

[Innsbruck]

Try:

	A	B	C	D	E	F	G	H
1	Date	Unit 1	Unit 2		Blocks		Average Operational Days
2	1/11/2019	1	0		Unit 1	Unit 2	Unit 1	Unit 2
3	1/12/2019	0.5	0.5		2	3	1.65	1.233333
4	1/13/2019	0	1
5	1/14/2019	0	0
6	1/15/2019	1	0
7	1/16/2019	0.8	0.2
8	1/17/2019	0	1
9	1/18/2019	0	0
10	1/19/2019	0	1

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula G3 =SUM(B:B)/E3 H3 =SUM(C:C)/F3 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula 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)))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.

Thank you Eric, this works perfectly much appreciated

 

[Innsbruck]

And, what would you expect the averages to be in each case of your new sample?

For Unit 1 : 2.15 and Unit 2: 1.85