Remember Me?

#1
November 28th 05, 05:44 PM posted to microsoft.public.excel.worksheet.functions
 Sam via OfficeKB.com Posts: n/a
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
November 28th 05, 11:03 PM posted to microsoft.public.excel.worksheet.functions
 Domenic Posts: n/a
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
November 29th 05, 12:25 AM posted to microsoft.public.excel.worksheet.functions
 Sam via OfficeKB.com Posts: n/a
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
November 29th 05, 03:42 AM posted to microsoft.public.excel.worksheet.functions
 Domenic Posts: n/a
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
November 29th 05, 04:50 AM posted to microsoft.public.excel.worksheet.functions
 Sam via OfficeKB.com Posts: n/a
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
November 29th 05, 11:47 AM posted to microsoft.public.excel.worksheet.functions
 Domenic Posts: n/a
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
November 29th 05, 03:27 PM posted to microsoft.public.excel.worksheet.functions
 Sam via OfficeKB.com Posts: n/a
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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

All times are GMT +1. The time now is 10:31 PM.