Hi Snowwy ,
Row 1 is necessary only to make a distinction between a cell which is blank , and a cell which contains 0.
Row 2 is the heart of the logic ; it tells us where the last 0 was encountered :
=MAX(IFERROR(1/($C5:C5=-999)*COLUMN($C5:C5)-2,0))
Since the data starts from column D , we start from column C ; the above formula is in D6. If we look at any intermediate cell , say L6 , we see that the evaluation of the portion :
($C5:C5=-999)
results in an array of values {FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE} ; the FALSE values are because the cells C5 through H5 do not have -999 in them ; I5 does have -999 , which results in a TRUE value ; J5 and K5 again give FALSE outputs.
Since a TRUE is equivalent to 1 , and FALSE equivalent to 0 , we divide 1 by this array to get the following result :
{#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,1,#DIV/0!,#DIV/0!} ----- Intermediate result 1
The next step is multiplying this array by the array resulting from the portion :
COLUMN($C5:K5)-2
The portion COLUMN($C5:K5) gives an array {3,4,5,6,7,8,9,10,11} ; subtracting 2 from this gives :
{1,2,3,4,5,6,7,8,9}
Multiplying this array by the array marked "Intermediate result 1" above gives us the array :
{#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,7,#DIV/0!,#DIV/0!}
The IFERROR wrapper substitutes 0 for all the error values , and the MAX function returns the value 7 ; this is the 7th value from the starting point in column C ; this means that the last 0 encountered was in the 7th column from column C i.e. column I.
Row 3 completes the logic by counting the number of non-zero values starting from the last 0 encountered ; let us look at the formula in cell M7 :
=IF(M4=0,0,MAX(1,SUM(OFFSET($D5,0,M6-1):M5)-1))
If the fine ( in cell M4 ) is 0 , this returns 0 ; if not , it returns the value :
SUM(OFFSET($D5,0,M6-1):M5)-1
where OFFSET($D5,0,M6-1) is the cell containing the last 0. The sum of values from that cell to the current cell tells us how many non-zero fines have been paid. The -1 and the MAX part are to have 2 1s ( 1,1,2,3,4,... ) since the normal sum will just return 1,2,3,4,5,... ; the -1 makes the above series 0,1,2,3,4 , and the MAX function replaces the 0 by 1 , giving the series 1,1,2,3,4,...
If you feel anything is unexplained , please let me know.
Narayan
