Nice one, Harlan! Very interesting...

In article .com,
"Harlan Grove" wrote:

Sam via OfficeKB.com wrote...
Is it possible to adapt both the Formulas below to include an "OR statement"
?


When there is NO further (PREVIOUS) instance of a numeric value - no more
INTERVALS can be calculated , the Formula has found the very LAST instance
of
a numeric value. Under such circumstances, the OR statement would facilitate
a SUBTRACTION: LAST - 1 ( LAST minus ONE).
...

Move to cell E9 in the result worksheet and change Domenic's MMULT3
defined name to refer to

=--(MMULT(--(Results=Sheet2!D$2),TRANSPOSE(COLUMN(Results)^0)) 0)

Then change the cell E9 array formula to

E9:
=CHOOSE(2+SIGN(ROWS(E$9:E9)-SUM(MMULT3)),SUM(LARGE(IF(MMULT3,ROW(Results)),
{0;1}+ROWS(E$9:E9))*{1;-1})-1,MATCH(1,MMULT3,0)-1,"")