Hi All,

Dynamic Named Range "Results" spans 6 Columns and many Rows - starts at Row 2,
headings in Row 1.
One cell houses 6 Alpha-Numeric single-digit (entered with leading zero) or
double-digit values entered like 08-20-21-40-60-61 per row. Each value will
only appear once in any row and the values are listed in ascending order.

I require a Formula to calculate the INTERVALS (the number of Rows between
the LAST instance and the PREVIOUS instance in a column) of each individual
occurrence of any designated PAIR of values (single-digit/ double-digit) in
the same Row of the Named Range "Results" . The results of each calculated
INTERVAL of a designated PAIR returned across the same Row of the New Sheet
(i.e. each Row houses the Intervals for ONLY one designated pair of values) -
starting with the most recent (the LAST) occurrence.

For instance, each time 60 and 61 appear together in the same Row, return the
INTERVAL by calculating the number of Rows between the LAST instance and the
PREVIOUS instance in a column - locate when both values LAST appeared
together and Count back to their PREVIOUS appearance together to get the
required Count; i.e. count from the Row ABOVE LAST appearance to the Row
BEFORE PREVIOUS appearance.

The results are returned to a Table layout: I have the criterion vertically,
and the results are returned across the Row of each vertical criterion.

Sample Layout Results Table:
Column A Row4 to many Rows holds consecutive Crtieria eg: 60-61
Column E to across many Columns will hold the calculated individual Intervals
for each consecutive criteria pair:

Col A Row4 (2 consecutive criteria per Row) 60-61 Col E Row4 Return count of
Intervals across Row
Col A Row5 (2 consecutive criteria per Row) 61-62 Col E Row5 Return count of
Intervals across Row


Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200511/1