 # 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!