LinkBack URL
About LinkBacks
hi to all, i would like a formula to count at E1 from repeating numbers 1 2 and 3 at A:A a certain event 123 or 231 or 213.please see exaple at the attached workbook.Thanks !!
hi to all, i would like a formula to count at E1 from repeating numbers 1 2 and 3 at A:A a certain event 123 or 231 or 213.please see exaple at the attached workbook.Thanks !!
=sum(--(--(n(offset(a1,row(1:7)-1,))&n(offset(a1,row(1:7),))&n(offset(a1,row(1:7)+1,)))=k1:m1)). Cse
in k1:m1: 123 231 213
dear tim thanks ! i try to put the formula to cell E1 but it is not do it. it must count according to the results in A:A
do you know why? please!
formula works
sorry tim but it does not .maybe i did not make it clear:i dont want a formula to count that 3 events only but to cant the serie for the event of three repeating numbers and count ,where is stop count 0.
for example( serie: 21321) 213 count 1 if next number is 2 (2132 )count 2 if the next number is 1(2132) count 3 .
if 21321 if the next is 1 or 2 like: 213211 count 0, if the next is 3 count 4 .
a big sorry if i was not clear!!
Is this right? With CSE
=MATCH(0,INDEX(--(MMULT(--(COUNTIF(OFFSET(A1,ROW(INDIRECT("1:"&COUNT(A:A)-3)),,3),{1,2,3})=1),{1;1;1})=3),N(IF(1,COUNT(A:A)-2-ROW(INDIRECT("1:"&COUNT(A:A)-3))))),)-1
DEAR Bo iam glad that you help me!!!!.is almost working ,i attached an example so you can see where i want to be zero!!
I use Count(H:H) H1 is blank so the count is wrong, you need to fill H1 with any number.
great! i wish you the best!
bo, can it the start working count from H1 not from H13?
Last edited by louis128; 01-09-2019 at 09:06 AM.
please see the attachement , there is an error and it should count 1.thanks
Please try
I1
=MATCH(0,INDEX(--(MMULT(--(COUNTIF(OFFSET(H1,ROW(INDIRECT("1:"&COUNT(H:H)-3))-1,,3),{1,2,3})=1),{1;1;1})=3),),)-1
DANG!!!!! Sweet looking formula! I wish I was that good! lol Trying but far from there!Originally Posted by tim201110
I think this should count 2
dear Bo please see example in attachement.if the last third comes in a serie 123 or 213 or 321 ,312 count 1
if the last third comes in a serie 123 or 213 or 321 ,312 count 1.please see attachemnt.thanks bo
This is for Post#12
For count from last row, please try with CSE
Formula:
Please Login or Register to view this content.
bo, the excel program says:that the formula use more levels of nesting.do you know what means ,can we change something maybe?
Please check file from Post#16
yes .is done!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks