Excel: Consecutive appearance


From the chart below, I need a formula that return the maximum number of times 1&2 appears in this list consecutively, before another set of numbers appears. From this list the answer should be 3. The array formula that I’m using now however returns the value of 16. Any input would be greatly appreciated. =SUMPRODUCT(MAX(FREQUENCY((AZ1:AZ16=1),--(OFFSET(AZ1,1,0,ROWS(AZ1:AZ16))=2)),ROW(AZ1:AZ16),(AZ1:AZ16<>1),--(OFFSET(AZ1,1,0,ROWS(AZ1:AZ16))<>2))) 1. 1 2. 2 3. 1 4. 2 5. 1 6. 2 7. 1 8. 4 9. 1 10. 2 11. 1 12. 4 13. 1 14. 6 15. 1 16. 2


This question generated 20 answers. To proceed to the answers, click here.

This thread is current as of August 02, 2014.


For more resources for Microsoft Excel