+ Reply to Thread
Results 1 to 2 of 2

Count number of runs above or below average

  1. #1
    waxwing
    Guest

    Count number of runs above or below average

    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


  2. #2
    Harlan Grove
    Guest

    Re: Count number of runs above or below average

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1