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 Single Numeric value (single-digit/

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

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

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

For instance, each time 80 appears in a Row, return its INTERVAL count by

calculating the number of Rows between the LAST instance and the PREVIOUS

instance in a column - locate when the Numeric value LAST appeared and Count

back to its PREVIOUS appearance to get the required Count; i.e. count from

the Row ABOVE LAST appearance to the Row BEFORE PREVIOUS appearance to return

all individual INTERVALS for each appearance (of say, criterion number 80).

The results are returned to a chart layout: I have the criteria horizontally

and they are referenced using the cell address that houses each criterion,

and the results are returned down the Column of the horizontal criterion.

Example Chart Layout:

Cell Ref. E2 F2 G2 H2

Criterion 80 81 82 83

Calculated INTERVALS of criterion number to be displayed down its own

individual Column. I would have numerous criteria horizontally. In the

example below, Criterion 80 should return various Intervals down its Column

(on separate Rows) and the same for the others such as 82, and 83.

Example Expected Chart Layout Return: Leading zero and dashes for alignment

purpose only

Criteria:

Cell Ref. E2----F2----G2----H2 etc

Criterion 80----81----82----83

Returned Intervals for each Criterion

Row09-----16----00----01----08

Row10-----08----04----20----07

Row11-----02----08----30----16

Row12-----12----02----08----08

Row13-----20----01----07----20

Etc

Thanks

Sam