Count Intervals of 1 Numeric value in a Row and Return Count down Column

[Sam via OfficeKB.com]

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

 

[Domenic]

Assuming that there will 'always' be only one occurrence of the criteria
for each row, try...

E9, copied across and down:

=IF(E$2<>"",LARGE(IF(Results=E$2,ROW(Results)-MIN(ROW(Results))+1),ROWS(E
$9:E9))-LARGE(IF(Results=E$2,ROW(Results)-MIN(ROW(Results))+1),ROWS(E$9:E
9)+1)-1,"")

....confirmed with CONTROL+SHIFT+ENTER. If there can be more than one
occurrence of the criteria for a row, try the following instead...

1) Select/highlight E9

2) Insert > Name > Define

Name: MMULT3

Refers to:

=MMULT(--(Results=Sheet1!E$2),TRANSPOSE(COLUMN(Results)^0))

*Change the sheet reference accordingly.

Click Ok

3) E9, copied across and down:

=IF(E$2<>"",LARGE(IF(MMULT3,ROW(Results)-MIN(ROW(Results))+1),ROWS(E$9:E9
))-LARGE(IF(MMULT3,ROW(Results)-MIN(ROW(Results))+1),ROWS(E$9:E9)+1)-1,""
)

....confirmed with CONTROL+SHIFT+ENTER.

4) Use conditional formatting to hide error values.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much once again. Formula does the job Brilliantly!

Before Posting I tried to adapt one of your earlier Formulas and spent ages
trying to get it to work; reading through your Post I now realise the very
reason my adaptation did not work: I forgot to change the Sheet Reference!

*Change the sheet reference accordingly.

Cheers,
Sam

Assuming that there will 'always' be only one occurrence of the criteria
for each row, try...

E9, copied across and down:

=IF(E$2<>"",LARGE(IF(Results=E$2,ROW(Results)-MIN(ROW(Results))+1),ROWS(E
$9:E9))-LARGE(IF(Results=E$2,ROW(Results)-MIN(ROW(Results))+1),ROWS(E$9:E
9)+1)-1,"")

...confirmed with CONTROL+SHIFT+ENTER. If there can be more than one
occurrence of the criteria for a row, try the following instead...

1) Select/highlight E9

2) Insert > Name > Define

Name: MMULT3

Refers to:

=MMULT(--(Results=Sheet1!E$2),TRANSPOSE(COLUMN(Results)^0))

*Change the sheet reference accordingly.

Click Ok

3) E9, copied across and down:

=IF(E$2<>"",LARGE(IF(MMULT3,ROW(Results)-MIN(ROW(Results))+1),ROWS(E$9:E9
))-LARGE(IF(MMULT3,ROW(Results)-MIN(ROW(Results))+1),ROWS(E$9:E9)+1)-1,""
)

...confirmed with CONTROL+SHIFT+ENTER.

4) Use conditional formatting to hide error values.

Hope this helps!

Hi All,

[quoted text clipped - 46 lines]

Thanks
Sam

 

[Sam via OfficeKB.com]

Hi Domenic,

Is it possible to adapt both the Formulas below to include an "OR statement" ?


When there is NO further (PREVIOUS) instance of a numeric value - no more
INTERVALS can be calculated , the Formula has found the very LAST instance of
a numeric value. Under such circumstances, the OR statement would facilitate
a SUBTRACTION: LAST - 1 ( LAST minus ONE).

Domenic wrote:
Assuming that there will 'always' be only one occurrence of the criteria
for each row, try...

E9, copied across and down:

...confirmed with CONTROL+SHIFT+ENTER. If there can be more than one
occurrence of the criteria for a row, try the following instead...

1) Select/highlight E9

2) Insert > Name > Define

Name: MMULT3

Refers to:

*Change the sheet reference accordingly.

Click Ok

3) E9, copied across and down:

...confirmed with CONTROL+SHIFT+ENTER.

4) Use conditional formatting to hide error values.

Hope this helps!

Thanks
Sam

 

[Harlan Grove]

Sam via OfficeKB.com wrote...

Is it possible to adapt both the Formulas below to include an "OR statement" ?


When there is NO further (PREVIOUS) instance of a numeric value - no more
INTERVALS can be calculated , the Formula has found the very LAST instance of
a numeric value. Under such circumstances, the OR statement would facilitate
a SUBTRACTION: LAST - 1 ( LAST minus ONE).

....

Move to cell E9 in the result worksheet and change Domenic's MMULT3
defined name to refer to

=--(MMULT(--(Results=Sheet2!D$2),TRANSPOSE(COLUMN(Results)^0))>0)

Then change the cell E9 array formula to

E9:
=CHOOSE(2+SIGN(ROWS(E$9:E9)-SUM(MMULT3)),SUM(LARGE(IF(MMULT3,ROW(Results)),
{0;1}+ROWS(E$9:E9))*{1;-1})-1,MATCH(1,MMULT3,0)-1,"")

 

[Harlan Grove]

Harlan Grove wrote...
....

Move to cell E9 in the result worksheet and change Domenic's MMULT3
defined name to refer to

=--(MMULT(--(Results=Sheet2!D$2),TRANSPOSE(COLUMN(Results)^0))>0)

....

Actually make MMULT3 refer to

=--(MMULT(--(Results=D$2),TRANSPOSE(COLUMN(Results)^0))>0)

i.e., don't include a worksheet name when defining MMULT3. Excel will
refer to the active worksheet automatically.

 

[Harlan Grove]

Harlan Grove wrote...
....

...

Actually make MMULT3 refer to

=--(MMULT(--(Results=D$2),TRANSPOSE(COLUMN(Results)^0))>0)

i.e., don't include a worksheet name when defining MMULT3. Excel will
refer to the active worksheet automatically.

Maybe this time I'll get it right. With cell E9 the active cell, make
MMULT3 refer to

=--(MMULT(--(Results=E$2),TRANSPOSE(COLUMN(Results)^0))>0)

 

[Domenic]

Nice one, Harlan! Very interesting...

Sam via OfficeKB.com wrote...
...

Move to cell E9 in the result worksheet and change Domenic's MMULT3
defined name to refer to

=--(MMULT(--(Results=Sheet2!D$2),TRANSPOSE(COLUMN(Results)^0))>0)

Then change the cell E9 array formula to

E9:
=CHOOSE(2+SIGN(ROWS(E$9:E9)-SUM(MMULT3)),SUM(LARGE(IF(MMULT3,ROW(Results)),
{0;1}+ROWS(E$9:E9))*{1;-1})-1,MATCH(1,MMULT3,0)-1,"")

 

[Sam via OfficeKB.com]

Hi Harlan,

Thank you very much for that amazing Formula. It works great!

Harlan Grove wrote...
Move to cell E9 in the result worksheet and change Domenic's MMULT3
defined name to refer to

i.e., don't include a worksheet name when defining MMULT3. Excel will
refer to the active worksheet automatically.

Then change the cell E9 array formula to

E9:
=CHOOSE(2+SIGN(ROWS(E$9:E9)-SUM(MMULT3)),SUM(LARGE(IF(MMULT3,ROW(Results)),
{0;1}+ROWS(E$9:E9))*{1;-1})-1,MATCH(1,MMULT3,0)-1,"")

Cheers,
Sam