Assuming that Sheet1 contains your 'Chart/Matrix Layout', try the
following...

1) Select B2 (your first intercept in your 'Chart/Matrix Layout')

2) Define the following references...

Insert Name Define

Name: Last

Refers to:

=MATCH(2,1/(MMULT(--(Results=Sheet1!$A2),TRANSPOSE(COLUMN(Results)^0)) *MM
ULT(--(Results=Sheet1!B$1),TRANSPOSE(COLUMN(Results)^0)) ))

Click Add

Name: Previous

Refers to:

=LARGE(IF(MMULT(--(Results=Sheet1!$A2),TRANSPOSE(COLUMN(Results)^0)) *MMUL
T(--(Results=Sheet1!B$1),TRANSPOSE(COLUMN(Results)^0)) ,ROW(Results)-MIN(R
OW(Results))+1),2)

Click Ok

3) Then enter the following formula in B2, copy down and across:

=IF($A2<B$1,Last-Previous-1,"")

Note that you'll get an error value under the following circumstance...

#N/A - when a pair does not exist

#NUM! - when a second pair does not exist

You can always choose to use conditional formatting to hide these
errors, if you prefer. Post back if you need help.

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi All,

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