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).
Bookmarks