Assume a database where customers are listed from cell A6 down. From cell B5 to the right months are entered from April to March (B5:M5). In B6:M6 (Customer 1), a user enters 1’s and 0’s. A value of 1 respresents “Cheque bounced” and 0 represents “Cheque honoured”. Similar data is entered for other customers in B7:M500.
One may want to know the maximum number of consecutive “Cheque bounce events” for all customers listed in column A without using spare rows and columns.
In cell N6, enter the following array formula (Ctrl+Shift+Enter)