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