Count Longest Run Of Numbers Above Mean

    Hi,


    I'm attempting to summarise several hundred control charts.
    One thing I'd like to do is be able to put in a formula to count the maximum number of successive entries that are all the same side of the mean.
    Another related thing is to be able to count the longest run where successive values are the same.


    Could anyone suggest a couple of formulas, please?


    Thanks


    Dave

    Re: Count Longest Run Of Numbers Above Mean


    Hi


    this formula should help with the first part of your question


    =COUNTIF(A1:A100,">"&(ROUND(AVERAGE(A1:A100),0))) amending the range, of course


    HTH


    Robert

    Re: Count Longest Run Of Numbers Above Mean


    Hi Robert,


    Thanks for that, but doesn't quite do the job. It just counts all the points above a rounded mean. I really need something to count the longest "streak" one side of the mean.


    Dave

    Re: Count Longest Run Of Numbers Above Mean


    For the longest streak above the mean try


    =MAX(FREQUENCY(IF(A1:A100>AVERAGE(A1:A100),ROW(A1:A100)-ROW(A1)+1),IF(A1:A100<=AVERAGE(A1:A100),ROW(A1:A100)-ROW(A1)+1)))


    confirmed with CTRL+SHIFT+ENTER


    For longest streak below the mean swap the < and > signs

    Re: Count Longest Run Of Numbers Above Mean


    Thanks dll,


    That's ideal. I don't quite understand how the whole formula works, but I'll investigate it.


    Regards


    Dave

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login