Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 28th 05, 05:44 PM posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default Count Intervals of 2 Consecutive Values in same Row and Return Count across Row

Hi All,

Dynamic Named Range "Results" spans 6 Columns and many Rows - starts at Row 2,
headings in Row 1.
One cell houses 6 Alpha-Numeric single-digit (entered with leading zero) or
double-digit values entered like 08-20-21-40-60-61 per row. Each value will
only appear once in any row and the values are listed in ascending order.

I require a Formula to calculate the INTERVALS (the number of Rows between
the LAST instance and the PREVIOUS instance in a column) of each individual
occurrence of any designated PAIR of values (single-digit/ double-digit) in
the same Row of the Named Range "Results" . The results of each calculated
INTERVAL of a designated PAIR returned across the same Row of the New Sheet
(i.e. each Row houses the Intervals for ONLY one designated pair of values) -
starting with the most recent (the LAST) occurrence.

For instance, each time 60 and 61 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 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 Table layout: I have the criterion vertically,
and the results are returned across the Row of each vertical criterion.

Sample Layout Results Table:
Column A Row4 to many Rows holds consecutive Crtieria eg: 60-61
Column E to across many Columns will hold the calculated individual Intervals
for each consecutive criteria pair:

Col A Row4 (2 consecutive criteria per Row) 60-61 Col E Row4 Return count of
Intervals across Row
Col A Row5 (2 consecutive criteria per Row) 61-62 Col E Row5 Return count of
Intervals across Row


Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200511/1

  #2   Report Post  
Old November 28th 05, 11:03 PM posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default Count Intervals of 2 Consecutive Values in same Row and Return Count across Row

First, define the following reference...

Select E4

Insert Name Define

Name: Array

Refers to:

=MMULT(((Results+0)=LEFT(Sheet1!$A4,2)+0)+((Result s+0)=RIGHT(Sheet1!$A4,2
)+0),TRANSPOSE(COLUMN(Results)^0))

Click Ok

Then, enter the following formula in E4, and copy across...

=CHOOSE(2+SIGN(COLUMNS($E4:E4)-SUM(--(Array1))),SUM(LARGE(IF(Array1,ROW
(Results)-MIN(ROW(Results))+1),{0,1}+COLUMNS($E4:E4))*{1,-1})-1,MATCH(TRU
E,Array1,0)-1,"")

....confirmed with CONTROL+SHIFT+ENTER. Change the sheet reference
accordingly. Also, if you format Column A and 'Results' as text, you
can eliminate the '+0' bit from the defined reference, 'Array'.

Hope this helps!

In article <[email protected], "Sam via OfficeKB.com" <[email protected]
wrote:

Hi All,

Dynamic Named Range "Results" spans 6 Columns and many Rows - starts at Row 2,
headings in Row 1.
One cell houses 6 Alpha-Numeric single-digit (entered with leading zero) or
double-digit values entered like 08-20-21-40-60-61 per row. Each value will
only appear once in any row and the values are listed in ascending order.

I require a Formula to calculate the INTERVALS (the number of Rows between
the LAST instance and the PREVIOUS instance in a column) of each individual
occurrence of any designated PAIR of values (single-digit/ double-digit) in
the same Row of the Named Range "Results" . The results of each calculated
INTERVAL of a designated PAIR returned across the same Row of the New Sheet
(i.e. each Row houses the Intervals for ONLY one designated pair of values) -
starting with the most recent (the LAST) occurrence.

For instance, each time 60 and 61 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 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 Table layout: I have the criterion vertically,
and the results are returned across the Row of each vertical criterion.

Sample Layout Results Table:
Column A Row4 to many Rows holds consecutive Crtieria eg: 60-61
Column E to across many Columns will hold the calculated individual Intervals
for each consecutive criteria pair:

Col A Row4 (2 consecutive criteria per Row) 60-61 Col E Row4 Return count of
Intervals across Row
Col A Row5 (2 consecutive criteria per Row) 61-62 Col E Row5 Return count of
Intervals across Row


Thanks
Sam

  #3   Report Post  
Old November 29th 05, 12:25 AM posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default Count Intervals of 2 Consecutive Values in same Row and Return Count across Row

Hi Domenic,

Thank you for reply. I'm receiving #Value error - not sure Why?

Cheers
Sam

Domenic wrote:
First, define the following reference...

Select E4

Insert Name Define

Name: Array

Refers to:

=MMULT(((Results+0)=LEFT(Sheet1!$A4,2)+0)+((Resul ts+0)=RIGHT(Sheet1!$A4,2
)+0),TRANSPOSE(COLUMN(Results)^0))

Click Ok

Then, enter the following formula in E4, and copy across...

=CHOOSE(2+SIGN(COLUMNS($E4:E4)-SUM(--(Array1))),SUM(LARGE(IF(Array1,ROW
(Results)-MIN(ROW(Results))+1),{0,1}+COLUMNS($E4:E4))*{1,-1})-1,MATCH(TRU
E,Array1,0)-1,"")

...confirmed with CONTROL+SHIFT+ENTER. Change the sheet reference
accordingly. Also, if you format Column A and 'Results' as text, you
can eliminate the '+0' bit from the defined reference, 'Array'.

Hope this helps!

Hi All,

[quoted text clipped - 34 lines]
Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200511/1
  #4   Report Post  
Old November 29th 05, 03:42 AM posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default Count Intervals of 2 Consecutive Values in same Row and Return Count across Row

Just to be clear...

1) Can you provide a sample of the values for the first row in
'Results'?

2) Can you provide a sample of the value/criteria contained in A4?

3) Can you confirm whether Column A and 'Results' are formatted as
'Text' or 'General'?

In article <[email protected], "Sam via OfficeKB.com" <[email protected]
wrote:

Hi Domenic,

Thank you for reply. I'm receiving #Value error - not sure Why?

Cheers
Sam

  #5   Report Post  
Old November 29th 05, 04:50 AM posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default Count Intervals of 2 Consecutive Values in same Row and Return Count across Row

Hi Domenic,

1) Can you provide a sample of the values for the first row in 'Results'?


09-18-36-46-56-66 (Sheet1)

2) Can you provide a sample of the value/criteria contained in A4?


01-02 (Sheet2)

3) Can you confirm whether Column A and 'Results' are formatted as 'Text' or 'General'


Both General

Cheers,
Sam


Domenic wrote:
Just to be clear...

1) Can you provide a sample of the values for the first row in
'Results'?

2) Can you provide a sample of the value/criteria contained in A4?

3) Can you confirm whether Column A and 'Results' are formatted as
'Text' or 'General'?

Hi Domenic,

Thank you for reply. I'm receiving #Value error - not sure Why?

Cheers
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200511/1


  #6   Report Post  
Old November 29th 05, 11:47 AM posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default Count Intervals of 2 Consecutive Values in same Row and Return Count across Row

In article <[email protected], "Sam via OfficeKB.com" <[email protected]
wrote:

Hi Domenic,

1) Can you provide a sample of the values for the first row in 'Results'?


09-18-36-46-56-66 (Sheet1)


Oh I see! This is one value, entered in one cell. I re-read your
original post and I see I missed that point. Sorry about that, Sam! In
that case, change the defined reference to...

=--(MMULT(--(ISNUMBER(SEARCH(Sheet2!$A4,Results))),TRANSPOSE(C OLUMN(Resul
ts)^0))0)

....and enter the following formula in E4, copy across and down...

=CHOOSE(2+SIGN(COLUMNS($E4:E4)-SUM(Array)),SUM(LARGE(IF(Array,ROW(Results
)-MIN(ROW(Results))+1),{0,1}+COLUMNS($E4:E4))*{1,-1})-1,MATCH(1,Array,0)-
1,"")

....confirmed with CONTROL+SHIFT+ENTER. Does this help?
  #7   Report Post  
Old November 29th 05, 03:27 PM posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default Count Intervals of 2 Consecutive Values in same Row and Return Count across Row

Hi Domenic,

Thank you very much - that's Brilliant!

=--(MMULT(--(ISNUMBER(SEARCH(Sheet2!$A4,Results))),TRANSPOSE(C OLUMN(Results)^0))0)
...and enter the following formula in E4, copy across and down...


=CHOOSE(2+SIGN(COLUMNS($E4:E4)-SUM(Array)),SUM(LARGE(IF(Array,ROW(Results
)-MIN(ROW(Results))+1),{0,1}+COLUMNS($E4:E4))*{1,-1})-1,MATCH(1,Array,0)-1,"")


...confirmed with CONTROL+SHIFT+ENTER.


Cheers,
Sam




Domenic wrote:
Hi Domenic,

1) Can you provide a sample of the values for the first row in 'Results'?


09-18-36-46-56-66 (Sheet1)


Oh I see! This is one value, entered in one cell. I re-read your
original post and I see I missed that point. Sorry about that, Sam! In
that case, change the defined reference to...

=--(MMULT(--(ISNUMBER(SEARCH(Sheet2!$A4,Results))),TRANSPOSE(C OLUMN(Resul
ts)^0))0)

...and enter the following formula in E4, copy across and down...

=CHOOSE(2+SIGN(COLUMNS($E4:E4)-SUM(Array)),SUM(LARGE(IF(Array,ROW(Results
)-MIN(ROW(Results))+1),{0,1}+COLUMNS($E4:E4))*{1,-1})-1,MATCH(1,Array,0)-
1,"")

...confirmed with CONTROL+SHIFT+ENTER. Does this help?


--
Message posted via http://www.officekb.com


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 10:31 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017