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

## Bookmarks