Hi Domenic,

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).

Domenic wrote:
Assuming that there will 'always' be only one occurrence of the criteria
for each row, try...

E9, copied across and down:

=IF(E$2<"",LARGE(IF(Results=E$2,ROW(Results)-MIN(ROW(Results))+1),ROWS(E
$9:E9))-LARGE(IF(Results=E$2,ROW(Results)-MIN(ROW(Results))+1),ROWS(E$9:E
9)+1)-1,"")

...confirmed with CONTROL+SHIFT+ENTER. If there can be more than one
occurrence of the criteria for a row, try the following instead...

1) Select/highlight E9

2) Insert Name Define

Name: MMULT3

Refers to:

=MMULT(--(Results=Sheet1!E$2),TRANSPOSE(COLUMN(Results)^0))

*Change the sheet reference accordingly.

Click Ok

3) E9, copied across and down:

=IF(E$2<"",LARGE(IF(MMULT3,ROW(Results)-MIN(ROW(Results))+1),ROWS(E$9:E9
))-LARGE(IF(MMULT3,ROW(Results)-MIN(ROW(Results))+1),ROWS(E$9:E9)+1)-1,""
)

...confirmed with CONTROL+SHIFT+ENTER.

4) Use conditional formatting to hide error values.

Hope this helps!

Thanks
Sam


--
Message posted via http://www.officekb.com