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 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
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1