Count number of runs above or below average

[waxwing]

I'm stumped. I have a column of data such as the simplified version
below. For the example, for the eleven numbers below, the average is
3.0. I want to count the number of runs. A run is a continous set of
values above or below the mean. I've marked the runs in the example.
As soon as the series crosses the mean, a new run starts. This example
has four runs. Any ideas on a formula to calculate it?

1 run 1
2 run 1
5 run 2
6 run 2
1 run 3
1 run 3
2 run 3
3 equal to mean/not part of a run
4 run 4
4 run 4
4 run 4

Thanks.

- John

 

[Harlan Grove]

waxwing wrote...
....

. . . I want to count the number of runs. A run is a continous set of
values above or below the mean. I've marked the runs in the example.
As soon as the series crosses the mean, a new run starts. This example
has four runs. Any ideas on a formula to calculate it?

1 run 1
2 run 1
5 run 2
6 run 2
1 run 3
1 run 3
2 run 3
3 equal to mean/not part of a run
4 run 4
4 run 4
4 run 4

You're already close. Use a second column along side your data column.
If your data were in A1:A11, enter the following formulas.

B1:
=IF(A1<>AVERAGE(A$1:A$11),1,"")

B2:
=IF(AND(SIGN(A2-AVERAGE(A$1:A$11))<>{0;1}*SIGN(A1-AVERAGE(A$1:A$11))),
MAX(B$1:B1)+1,"")

Select B2 and fill it down into B3:B11. The number of runs would be
given by both COUNT(B1:B11) and MAX(B1:B11).