Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default Count Intervals of 1 Numeric value in a Row and Return Count down Column

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
  #2   Report Post  
Domenic
 
Posts: n/a
Default

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!

In article <553e7fa377ecd@uwe, "Sam via OfficeKB.com" <u4102@uwe
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 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

  #3   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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!

Domenic wrote:
*Change the sheet reference accordingly.


Cheers,
Sam

Domenic wrote:
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



--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1
  #4   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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:


=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!


Thanks
Sam


--
Message posted via http://www.officekb.com
  #5   Report Post  
Harlan Grove
 
Posts: n/a
Default

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,"")



  #6   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.

  #7   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.


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)

  #8   Report Post  
Domenic
 
Posts: n/a
Default

Nice one, Harlan! Very interesting...

In article .com,
"Harlan Grove" wrote:

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,"")

  #9   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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


=--(MMULT(--(Results=Sheet2!E$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.



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


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 08:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"