Home 
Search 
Today's Posts 
#1




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 AlphaNumeric singledigit (entered with leading zero) or doubledigit values entered like 082021406061 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 (singledigit/ doubledigit) 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: 6061 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) 6061 Col E Row4 Return count of Intervals across Row Col A Row5 (2 consecutive criteria per Row) 6162 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




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 AlphaNumeric singledigit (entered with leading zero) or doubledigit values entered like 082021406061 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 (singledigit/ doubledigit) 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: 6061 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) 6061 Col E Row4 Return count of Intervals across Row Col A Row5 (2 consecutive criteria per Row) 6162 Col E Row5 Return count of Intervals across Row Thanks Sam 
#3




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




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




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'? 091836465666 (Sheet1) 2) Can you provide a sample of the value/criteria contained in A4? 0102 (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




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'? 091836465666 (Sheet1) Oh I see! This is one value, entered in one cell. I reread 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




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'? 091836465666 (Sheet1) Oh I see! This is one value, entered in one cell. I reread 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 
Display Modes  

