# Maximum frequency in a range, but with an outlier

#### Rich N

##### New Member
The greatest brains of the Excel Universe having unfortunately been unable to solve my conundrum of yesterday http://www.mrexcel.com/forum/excel-questions/981047-identifying-range-consecutive-values-plotting-where-these-occur.html, may I be so cheeky as to ask for help with another question.

I have successfully pinched and amended the following
array formula to meet my needs - in a row of numbers between 1 and 10, this lists the maximum number of times there is a consecutive run of numbers below a certain value (in this case 7):

=MAX(FREQUENCY(IF(C2:AN2>0,IF(C2:AN2<7,COLUMN(C2:AN2))),IF(C2:AN2>=7,COLUMN(C2:AN2))))

Please could someone suggest a way of amending this formula so that it will handle one (and only one) occurrence of a value being outside this range (e.g. 8 or more).

So in a row of numbers reading thus: 9,3,2,3,8,1,4,5,6,3,2,1,9,2,3,4,5,8,9,10,2,3,5 it would return a value that ignores the underlined 9 and counts the figures either side of it in the run. I am easily pleased, so a satisfactory answer would either be 12 (including the 9 in the total) or 11, which would exclude the 9.

Thanks again.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Replies
6
Views
271
Replies
0
Views
143
Replies
0
Views
137
Replies
2
Views
187
Replies
1
Views
314

1,190,833
Messages
5,983,146
Members
439,825
Latest member
glen3265

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back