Hello, I've got the formula to identify that largest group of consecutive cells that fall below the threshold in a row of data and would like to show the column header of the first cell. Example data is below.
I'm using a CSE (array) formula: =MAX(FREQUENCY(IF(A3:N3<P3,COLUMN(A3:N3)),IF(A3:N3>P3,COLUMN(A3:N3)))) to identify the 'longest consecutive event' and would like cell S3 to show '9', which is the column header for the first cell in the longest consecutive event; and cell S4 to show '2', which is the first cell in the longest consecutive event on that row. Is this possible?
[IMG2=JSON]{"data-align":"none","data-size":"full","src":"https:\/\/www.ozgrid.com\/forum\/core\/image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAA4UAAAB9CAYAAAAV+cZJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAABTzSURBVHhe7d1NkuQ2kobhrLmA9rJeatEH6UXPiWcWfZBeaCnTXieozi8zXe0FASCIIAEH8T5mqAwS\/HEC4I9XREZ+e3t7+\/5eAAAAAACb+f79+9tHUqgXEX379u0jyIiIrQ+x9SG2PsTWJ2psikuixkZ\/lkVunzvtetyy87GfRVu9hvZ7jbXf\/3xNAwAAAAA2RFIIAAAAABsjKQQAAACAjU1LCvX51SgUS61EEC2uXDxWIsjFlZaZcvH4Mlu0eOQojihx5hBb3gp9GqXvWuMYEe8K\/XYnHZ8vO0iP2Rfk0U590nbzBe162m9KUqjA9AuNLQGOonhyJQJrr7TMbr9cTCpR+jUXmy+z5WKyMlNuvEU6V7EHG4f4r6PzcOR5uus1wcbljtfH9Lit7DoWanYeJ1dI284KbdgmN\/5a2o+Pjx5oacQ7WcfmzI6tJGpcOFYab\/QpRqpd9xDDjtcEro953B9+xDi5D214rDT+5Kj9SAoDq3WsOaqfhRN3PUfjLepYw7O0XPd2Vbuu0m73ark+lvoG+2CcILra+ByeFPoTZoWT4+gEBwBcg+vtWla4hwPATl65LvNO4Rc1YK7wgLKuXH9awZpyfWkFa6MP2+Ru+Jqeda8iMcTM8Ye9MNba2HXZSiuSwi9qwFw505iIJdefViLwJ6wvkUSLL9eXVrAujSv6EIgtvRdY4dzF1XLjTIWx1s6ejVR8G9YMTQotmDS4oyBnssYEruZPWF8iiRwbnkHXVxtb+sn19phvJ99+s9Bve1A\/5wpwtdwYY6z1821Zu1YPf6fQB2YFAHC\/iA\/u6T1A0yQY66HfANyF68sYfHw0sJaTIOpJorjShz3EdjTe6FMAu+L6iBa5caJpm1cbQ8AVXhljw5JCBVm6YB5dbGeqxT1CrW1mx1YSNS4cK403+hQj1a57+GRtFOm8fHq\/lY6P6yO8dJxo2s9jrPRJ2xV5tXY6ulbxTuEXNVSuRDh5rYOjxZaLSSVCm0kuNl+QlxtvUfoUr1mpby3WCNI2s4K\/evq1YqVzCPPUxoleo4+1K+py40\/l6Fqlln1fJuYFreUAZiG2PsTWh9j6EFufqLEpLokaG\/1ZFrl97rTrccvOx34WbfUa2u811n68UwgAAAAAGyMpBAAAAICNfXx89PMlAAAAAGAn+vjoR1L46+9\/fM4J5peff3ojtvOIrQ+x9SG2PsR2nuKSqLHRn2WKYcff+eF3CnnfoYXa6t+\/\/etrCmf9\/W\/\/YKy9gN8pBAAAAACQFAIAAADAzkgKAQAAcCt9RC2nNP8Vd2zziPY5Y7\/AVUgKAQAAcLunJk06Lv1OFr\/XhpWRFAIAAOB2SppqiWFa56fttX6m8\/20V6qz+WmdTefWkdx6R+sAqyApBAAAQHhKvOwdOUvO\/LRXqvPza+ulSuvZsrl1gJWQFAIAAGAIn1CdlSZetUSsN0kjucOuSAoBAAAwzCuJ4RW0bysAPpEUAgAAYBtKSn0BQFIIAACAwe5+t7B127xbCHwiKQQAAMBw6bt0lihaeYXflt9Puo80hpLe9YBVkBQCAADgVqUkKp2vaV9Mbjkvt6x+psuJzU\/rcst6vesBKyApBAAAAICNkRQCAAAAwMZICgEAAABgYySFAAAAALAxkkIAAAAA2BhJIQAAAABsjKQQAAAAt9Lf9jP+9WytsYyM+e9\/+8fXq9gix6n+8sXr7cve9VZBUggAAIBh+Lt+uJOSN\/83JVWentBdgaQQAAAAt7EH8vSn+HnpfD9tbP7ZOq+2TG4b9jqdly43it6hs+LZdK5O\/Py03urS+ZKrs9e55SM705d+Wf\/6qUgKAQAAcBt7Z9B+pvSg7d\/RSaeNn3+mzvPLpcuUtqHX\/mdpuRGUhP37t3\/9WdKkzNf7utJ88XW19XydXvuf0aV9aD+P+tLXi\/18IpJCAAAATJM+aF\/x4N2yjd79XhHfXVqStFcSuRWSQPWPkjlfSo76MnJfX42kEAAAAEsoPeiniUCv1m20LrcKvQNoxVMSWKqLTOPBl1o\/Pa0ve5EUAgAAYAnpw77n5\/c84Gsdv42S1uVWouTPF8\/PXyExPNP3T+zLXiSFAAAAWI5\/+D+TCLRo3d7V+x3hKLHz9Su9O\/iqFfvySiSFAAAAuN2rD916J0fbsOLf2anVeelynq\/LrW\/LHy13BSVjaRF7t85K+q5eiV8vXae2zVqdaF40aR\/n+knz5Gxf2npPpCP7\/uvvf3xOBfPLzz+9Edt5xNaH2PoQWx9iO09xSdTY6M8yxXDXw3NkrQ+aT7TzsZ+ltkqTrbvlErxV6VgYa\/3sXOWdQgAAAODB7J0+K09JCHEdkkIAAADgwZQE+gKkSAoBAAAAYGMkhQAAAACwMZJCAAAAANgYSSEAAAAAbIykEAAADKevQbeSKtX56dx6WMfM\/uvdN2PumL7Z1PjXM6T9pWkrZ+zS7ySFAABgKD1k6e9iWfEPXbU6AOuY+S2ndh0xXFeOkRQCAIDw7CEufZjTNA94ayj1lc3zdbZsurxfNq0Tm5+rS726jdxy6TpH0yX29wRTNj+ts+lcndj83jqvNH300\/hpvbZyF7W5TxAllxhq2kpJbh1jr9NtpNNSWnYmkkIAABCePdT5hzs9TGk694CHWI76yur969LypW0dree9uo3W5XooQbK\/J5gmUDY\/rZOe9Vrr\/PwSLed\/Hqnt+xXWN0f8Mlf1p9+OXqfTXq1uBpJCAAAwzSsPQ3qYwnpy\/XamL1uXrS1319jRdm1M20N\/On1Ga4Ilrcue2abpWcf4hM8SwadK+7fW32fHwt1ICgEAwFD2oNzzkAyU+HGl0uuKbURiSZmVVK3ubjP3jR+RFAIAgOH0AE9CiKvZuLIEsYffhsoTKDG0kiZgvk5lpJn7fkrSfxWSQgAAMBQPY3hFafycGVety\/aMVUtILaFMp8+44h20M9soLftqHEr4tI1a4nfFseZY+3u9\/fFkJIUAAGAoe0iz4h\/OanWieVhP2q81R2PA1\/u6o\/W8V7fRulwPS6DSJMrPT+tqauu11tXWSeXm5dT2\/QrrG++ov1r7M13uSXQ033\/9\/Y\/PqWB++fmnN2I7j9j6EFsfYutDbOcpLokaG\/1ZphhKD1lPVnu4fLq7jv2Jbapjuioh2pESynRM7HzunWVtxTuFAAAAAB5DSY6SHbQjKQQAAMASePcHrRgr55AUAgAAAMDGSAoBAAAAYGMkhQAAAACwsRBJob6VzL5VLgqLidj6RIzNt1n0+KLwMfkSRdS4JEpsuf37uNK6kWr7jxibzcvVjZLbt48prbtDbT+tMegLINIShY+l9LqHHWeuWP3devdRW691m365px\/rKlr\/dMRqSv10R\/\/1bDPqOJqeFI64gZ1lMekrvO1rvKPEGTk2E7FPPWs7a78I1GYR+9S3lcUWReRzIUpsuX1Gjs3MiMeL2m4R4qpt++x+9SUQvkR8ULryiyr8seamAexB53zE693UpPDOG9cr\/M01msixReYfnKJJz4OofRy5DfFXUa+vUottdtyl\/c8+LyPENbpv9NBkxbNpPz+3nNj8tM6m0zo\/3\/\/MsXVry\/TIbdOm\/XxbLl1WanXSu55pWcazZdN1ctvJLWvLpctKrU561zMty9zB\/1F3k76zl9aly4tNp3V+\/q5qY6ClLqe0nk2X1otg+juFPGD20c3ZbtCR2lAxRe5Tazdru0gix4Y1RT4Xa7HNjjtqu9Xi8teOO+MvbVv7vnK\/enDy76zlHrDsHTa\/rF+udRu+Tq\/9z5KjbffKxWSszr\/OLVurE19\/Zj1TWr9Gy\/qfUtuO1fnXuWVrdeLrz6xnSuvfTYma\/pi9laPE7Wh5X291eu1\/7qY2Blrr\/HyprSdWL7n62aYmhVFvvN6Im2wPxWMxkUS0i9xukWOLeB74tooY32yR26IW2+y4W\/Y\/Y7wd7Uv1tsxd14+rj1cPRL7Yw1KL0rJXbGOmWky98abrXXncr2xrp2O9ik\/qLNFrsWPil15fVFq19vXZMRFhDNWE+KKZqHjQPEftFbWt\/AMTnsGfn9a3dz0Mn+XjiRITruHHHT6pTXraQw9IvqR6HuZSV2yj5M5ttyjtX21ZqltV6XieeKxGSZ+VFmeXfzp\/bbGSqo2dWl1N73oRkBQWcOPvkz4E+9dYE+dCH7WXFTwD50LZHdf+owe6Fldso+TObbeo7d\/PX\/HhNOWPR8Xz859wrEbv7vly5OzyKI8rjaPc\/BZ+vbPrzkZSmBH5xp\/eeCPJPQRHacPI7YbnyY23iNcTtLP+jNaPEa5tdt33bXN1O7U+7NeWuzNhmJ2M+P3PjuVuOx2r8e\/+KeHTdC3x493C80pj6ZUxttr4JCmssJtthJuusRutj+nqm+8TRW43+rTPSn0aqT8jt9sKrN18+8301P7U\/7DrgcpK7X\/c\/bJ+uTPbSGn5mle2fYXa\/ntja10vXe6Ms8tLLa7WmFOt66XLjWKJn5Wjd\/7OLu9p+R3VxoCvS8dGbUzUtpk6qp9BR\/M96g1ENzhiO4\/Y+hBbH2LrQ2znKS6JGhv9WaYYoj0AjRDxwW+UnY\/9LLXVmUQOP1Jiu9pYi3R+WCy8UwgAAAAAA0T9DxOSQgAAAAAYIOq7mh8fH\/18CQAAAADYiRLVj6QwbMYa+PPoxNaH2PoQWx\/FFvX3NM5+GcBIUWOzL0SIGhvnaNlnDF8TG3k\/7Lff\/\/\/\/vqb28vM\/\/zfsORFN5HvVClb8ncJI7B7Bx0cBAAAAYGMkhQAAAACwMZJCAAAAANgYSSEAAAAAbIykEAAAAAA2RlIIAAAAABsbkhTqq0598UrzR8vt38c2I77a\/kvzR6ntv1Y3gt9\/GkNp\/igt+48QWxpDaf4oLfufFZu+CttKytfl6u9W23etboTa\/n1drv5utX3X6kbJjfWWc+QKfj\/pvkrzn0CH5ItXmv8U+tMSVlK1OuQ98fwYaea19+mGvVOov39hxejE8PNnnSi1\/fr4Zsjtf3a71fY\/OzbjYzCzY2vZ\/6z2Mj4+Q7uV6eakvy1lJXez8vUj1WJriftOLfv39SPVYmuJ+26l8X90jlzJ78uMjmEGHa4Vo8P085922Er29HcWrfjkr1aHvCeeFyPNuObu5Pak0G4Uq5kdd+R2i9yftFsf2q2PEoMSSyBmmbnvI7TbtUadI6vez1\/1ftjvx\/01sRklewD2MPzjo9HUbnKz4569\/1Wt2m6Kd+YDF+12PSU4VtCOdsuLMNZXvU68SodrBegR+V61At0PVvxPu5UMSQp1ElhZ6UYyO+7Z+z+imGoXuVkxr95us9Bur8ndsDRtZWaCE\/lmulK7WTxWfF0Ud58j0a8Td1GTWtnosP+kj4bax0UBPNPtSWHUBzip3Txnxx253YxiVPEPBjZ994NJyartNqu9DO12vYgJwwoit5slglY0PUqEsb7CdeIOmx72D\/i9wddEvletIOp\/wj3NsC+aiUonqj1k2k+8Rhc+Ln7nMRb7RGi3yDcsYusTMbajsa55XHuBmI7OX9Tpmqxir3G9IV80E5UlL3YT9TfT2XFHbrdabLRbWS222lgcgXbrV0oeIty0SrFFQLudczTWdZ7cPf4jXyfutOlhf+CdwWscnb+o0\/XYik3jerrUvY\/NcTeSdF9HdaNOnNy+Zsd2tH9DbD9aNTaTi2N2bEd1M2MzuTg0786bRy6B8fvz9WkcdyceT4zN5keN7e7zwKRj3Z8fJq2\/Kja\/r9z5ZnJ1F4UwhW\/i9DiO6lb\/PTyfGKbHclR31bh7ktz5qHl3XteeRNfatK1GXn+fyMakLmXvP2M2ZO7EiYLY+hBbH2Lro9ii3mhzN7YoosamuCRqbJyjZZ8xfE1s5P2wl08Ke5EUtot8r1oBSeFr7B6x\/e8UAgAAAMDOSAoBAAAAYGMfHx\/9fAkAAAAA2Ik+Phr+dwr5nZvziK0PvxPUh9j6ENt5ikuixkZ\/ln3G8DWxEQ1ZfqcQR3R+8DuF\/fidwtfYPYKPjwIAAADAxkgKAQAAAGBjJIUAAAAAsDGSQgAAAADYGEkhAAAAAGyMpBAAAAAANjY0KdRXnqY0z8oM+hpbKyW1ujvVYvN1ufq71fbt63L1d6vtu1Y3gh\/v6ZgvzR\/F7z+NoTR\/lNr+fV2u\/m61ffu6XP3davv2dbn6u9X2X5o\/it9\/GkNp\/gi1ffu6XP1VavspzX8CHZIvXmn+U+hPS1hJ1erwoyefHyPNen7bwbCkMHcSaJ7+LoaV0SeKBpb+LoyV3ECbNfhaYvP1I60aW0vcI\/gxb2afC8bHYGbH1rJ\/Xz8Ssb0mt\/+WuEfwMZiZsbXs29ffKbefmW0zig7XitFh+vlPO2wle\/o7i1Z88lerw492OD9GmPXctotQHx\/ViTKSEoOoarFZcjPLqrHNjEvsphBR5Nhqcc2Om9j6cC6cF6E\/I\/fbnd4P+\/24vyY2o2QPwB6GJIVHNxLVq0QzO8GpUWxWookc22w21iOO98ix1USOm9jKZu+\/JnJsJaNiHrWfaHS4VgCMp2fKqM\/kTzH9nULdWJQwqsy8yUQebLnYNG1lZvK1Wmyi+aW6u9lYnz3ecyLHZux64UWJm9jOmb3\/msixycz+HLWfaN4P98+y0WH\/SR8NtY+L4jw7X6xoGojm9qTwaPBHPTFmJQ0tosYlkWMzilFldMIa+Saw6g2KNu0zOzba5lqjYl6xba6w6WH\/gN8bfI09C1vRNNpFfiZ\/kmEfH7UTIOKJUBpsmm+Jw+gEwkQ+EYgNI9lNNSJiwyj0J4Ad6bnOnsXtJ651e1Kom5cVm46klDxonhWbHq0UW4STgdjOi\/gfIiZybFJ6EI4QN7GdF6FtSqLHNrM\/I7fNnTY97A+8M4gI9ExnxaZxPV3q3u8xYxK13A3N32RydXd2fC5JyO0vl5zl5l3pKDZfT2z\/9Wpsd58LR+Pd5Op2jc3v2\/j9EVte5NjkaP8mF3PU2O6My+\/X5NpGcjFfFdvRfkw+hq+JBblD+8txHNWt\/nt4PjFMj+Wo7qpx9wRH58edz0ZPknuOHPH89mSf1+fvY5PCsyKfJLlBGQWx9Yl8UbETNiJi60Ns5ykuiRob\/Vn2GcPXxEY0ZFdPCnuRFLbT+RH12WgFJIWvsXvE9G8fBQAAAADMQ1IIAAAAABv7+Pjo50sAAAAAwE708dFv7\/+QFAIAAADAlt7e\/gN3KveQwE2YtwAAAABJRU5ErkJggg=="}[/IMG2] [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 896"]
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
[TD="align: right"] 1
[/TD]
[TD="align: right"] 2
[/TD]
[TD="align: right"] 3
[/TD]
[TD="align: right"] 4
[/TD]
[TD="align: right"] 5
[/TD]
[TD="align: right"] 6
[/TD]
[TD="align: right"] 7
[/TD]
[TD="align: right"] 8
[/TD]
[TD="align: right"] 9
[/TD]
[TD="align: right"] 10
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 12
[/TD]
[TD="align: right"] 13
[/TD]
[TD="align: right"] 14
[/TD]
90th Percentile
Threshold
Number of times data drops below threshold
Longest consecutive event
Start (Column Header)
[TD="align: right"] 50
[/TD]
[TD="align: right"] 45
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 39
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 34
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 34
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 20
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 28
[/TD]
[TD="align: right"] 42
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 30
[/TD]
[TD="align: right"] 4
[/TD]
[TD="align: right"] 4
[/TD]
[TD="align: right"] 46
[/TD]
[TD="align: right"] 24
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 23
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 30
[/TD]
[TD="align: right"] 8
[/TD]
[TD="align: right"] 4
[/TD]
[/TABLE]