Posted to microsoft.public.excel.worksheet.functions
|
|
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
|