S

#### Sam via OfficeKB.com

Dynamic Named Range "Results" spans 5 Columns and many Rows (starts at Row 19)

.. Each cell

houses Numeric single-digit or double-digit values.

I require a Formula to calculate the INTERVALS (the number of Rows between

the LAST instance and the PREVIOUS instance in a column) between each

individual occurrence of any designated PAIR of Numeric values (single-digit

/ double-digit) in the same Row of the Named Range "Results" and return each

calculated INTERVAL result to a separate Column on the same Row of a New

Sheet - starting with the most recent ( the LAST) occurrence.

For instance, each time 80 and 87 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 Numeric 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 chart / matrix layout: I have the criterion

vertically and horizontally and they are referenced using the horizontal and

vertical cell address that houses each criterion, and the results are

returned across the Row of the intercept of the vertical and horizontal

criterion. At some point both criterion values being referenced will be the

same, can the Formula return empty text "" when this occurs?

Example Chart / Matrix Layout:

Cell Ref. A2 and B1 criterion 80 and 80

Cell Ref. A3 and B1 criterion 81 and 80

Cell Ref. A4 and B1 criterion 82 and 80

Criteria B1 houses 80

A2 houses 80

A3 houses 81

A4 houses 82

A5 houses 83

Thanks

Sam