Help requested for analysing batches

[BRAD4]

Can anyone advise an easy way to analyse the number of "groups" or batches, min batch size, max batch size and average batch size? For example, if you were counting widgets going past you, in colour a, b or c.

Sequence Col Col Col
# a b c
----------------------------
1 a
2 b
3 a
4 a
5 a
6 b
7 b
8 c
9 a
10 a
11 b
12 b
13 c
14 a
15 c

Analysis:
Colour a b c
No of items 7 5 3
No Batches 4 3 3
Ave Batch Size 1.75 1.66 1
Min Batch Size 1 1 1
Max Batch Size 3 2 1

Thanks in advance!

 

[StephenCrump]

Welcome to the Forum!

Try this:

B18: =COUNTIF($B1:$B15,B17)
B19: =SUMPRODUCT(--($B1:$B15=B17),--($B2:$B16<>B17))
B20: =IFERROR(B18/B19,"N/A")
B21: {=MIN(IF(FREQUENCY(IF($B1:$B15=B17,ROW($B1:$B15)), IF(1-($B1:$B15=B17),ROW($B1:$B15))),
FREQUENCY(IF($B1:$B15=B17,ROW($B1:$B15)),IF(1-($B1:$B15=B17),
ROW($B1:$B15)))))} Array-entered
B22: {=MAX(FREQUENCY(IF($B1:$B15=B17,ROW($B1:$B15)),IF($B1:$B15<>B17,ROW($B1:$B15))))} Array-entered

Excel 2010

	A	B	C	D
1	1	a
2	2	b
3	3	a
4	4	a
5	5	a
6	6	b
7	7	b
8	8	c
9	9	a
10	10	a
11	11	b
12	12	b
13	13	c
14	14	a
15	15	c
16
17		a	b	c
18	Number	7	5	3
19	Batches	4	3	3
20	Av Batch	1.75	1.67	1.00
21	Min Batch	1	1	1
22	Max Batch	3	2	1

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

​

 

[BRAD4]

StephenCrump: Absolutely perfect (and very neat). Many thanks.

 

[StephenCrump]

You're welcome. Thanks for the feedback.