Sequence Without help column
- Thread starter bines53
- Start date
John Jairo V
Well-Known Member
Hi @bines53:
¿ How many times has a sequence of two number 10 ?
=SUMPRODUCT(--(A1:A30<>10),--(A2:A31=10),--(A3:A32=10),--(A4:A33<>10))
¿ Great sequence of number 10 ?
=MAX(INDEX(FREQUENCY(COUNTIF(OFFSET(A2,,,ROW(A1:INDEX(A2:A31,COUNT(A2:A31)))),"<>10"),ROW(A1:INDEX(A2:A31,COUNT(A2:A31)))),))-1
Please comment! Blessings!
¿ How many times has a sequence of two number 10 ?
=SUMPRODUCT(--(A1:A30<>10),--(A2:A31=10),--(A3:A32=10),--(A4:A33<>10))
¿ Great sequence of number 10 ?
=MAX(INDEX(FREQUENCY(COUNTIF(OFFSET(A2,,,ROW(A1:INDEX(A2:A31,COUNT(A2:A31)))),"<>10"),ROW(A1:INDEX(A2:A31,COUNT(A2:A31)))),))-1
Please comment! Blessings!
Somendra Misra
Excel Ninja
Hi David,
Here's my attempt for the formula:
How many times a number repeats two times in sequence.
=SUM(--(FREQUENCY(IF($A$2:$A$31=$E$8,ROW($A$2:$A$31)),($A$2:$A$31<>$E$8)*ROW($A$2:$A$31))=2))
Confirm with Ctrl+Shift+Enter. Here if you change =2 to =3 you will get number of times 3 10's repeated and so on.
Maximum number which repeated in sequence.
=MAX(FREQUENCY(IF($A$2:$A$31=$E$8,ROW($A$2:$A$31)),($A$2:$A$31<>$E$8)*ROW($A$2:$A$31)))
Confirm with Ctrl+Shift+Enter.
Regards,
Here's my attempt for the formula:
How many times a number repeats two times in sequence.
=SUM(--(FREQUENCY(IF($A$2:$A$31=$E$8,ROW($A$2:$A$31)),($A$2:$A$31<>$E$8)*ROW($A$2:$A$31))=2))
Confirm with Ctrl+Shift+Enter. Here if you change =2 to =3 you will get number of times 3 10's repeated and so on.
Maximum number which repeated in sequence.
=MAX(FREQUENCY(IF($A$2:$A$31=$E$8,ROW($A$2:$A$31)),($A$2:$A$31<>$E$8)*ROW($A$2:$A$31)))
Confirm with Ctrl+Shift+Enter.
Regards,
NARAYANK991
Excel Ninja
Hi John ,
You have considered only one case where the first two numbers are not 10s ; you need to consider the case where the first two numbers can be 10. And then take the MAX of these two.
Clearly this approach cannot be generalized , unlike the approach which uses the FREQUENCY function.
Narayan
You have considered only one case where the first two numbers are not 10s ; you need to consider the case where the first two numbers can be 10. And then take the MAX of these two.
Clearly this approach cannot be generalized , unlike the approach which uses the FREQUENCY function.
Narayan
Somendra Misra
Excel Ninja
@bines53
On a lighter note
people who know you try to give you solution which will not include OFFSET, INDIRECT and other similar functions.
But over a couple of days it seems you also want to exclude CSE usage also
I think you should give all these requirements in your signature line, so that all can give such a solution.
Regards,
On a lighter note
people who know you try to give you solution which will not include OFFSET, INDIRECT and other similar functions.But over a couple of days it seems you also want to exclude CSE usage also
I think you should give all these requirements in your signature line, so that all can give such a solution
.Regards,
Another option, enter this beautiful site,
http://www.get-digital-help.com/201...smallest-consecutive-sequence-of-a-value-vba/
=SUMPRODUCT(0+(A2:A31=E8)*(CountConsVal(A2:A31)=E9))
=AGGREGATE(14,6,(CountConsVal(A2:A31))/(A2:A31=E8),1)
Currently, it preferred choice on me, because I have a heavy workbook.
David
http://www.get-digital-help.com/201...smallest-consecutive-sequence-of-a-value-vba/
=SUMPRODUCT(0+(A2:A31=E8)*(CountConsVal(A2:A31)=E9))
=AGGREGATE(14,6,(CountConsVal(A2:A31))/(A2:A31=E8),1)
Currently, it preferred choice on me, because I have a heavy workbook.
David
NARAYANK991
Excel Ninja
Hi David ,
If you are willing to accept VBA , then your entire application could have been developed and you would have started using it profitably by now !
Narayan
If you are willing to accept VBA , then your entire application could have been developed and you would have started using it profitably by now !
Narayan
NARAYANK991
Excel Ninja
Hi David ,
I do not know what you are talking about ; you have posted more than 200 entries , all related to Excel formulae ; you have asked these in isolation , and got answers from probably a dozen members ; each of these answers has been provided in isolation , each member not knowing where the formula solution provided fits into the overall picture.
You are therefore confident of integrating all of this to give yourself a working application.
Where does the commerciality of all this come in ? Are you intending to put out the entire application as a commercial product ? If so , what is the security of formulae vis-a-vis the security of VBA ?
Anyway , my point was that whether you use a single line of code or a thousand lines of code , the objective is to develop a robust application which performs ; if at all VBA is acceptable to you , then using VBA you would have been up and running by now , since what you have been trying to do getting formulae from various members over the past several months , could have been done better using VBA in a matter of weeks.
Narayan
I do not know what you are talking about ; you have posted more than 200 entries , all related to Excel formulae ; you have asked these in isolation , and got answers from probably a dozen members ; each of these answers has been provided in isolation , each member not knowing where the formula solution provided fits into the overall picture.
You are therefore confident of integrating all of this to give yourself a working application.
Where does the commerciality of all this come in ? Are you intending to put out the entire application as a commercial product ? If so , what is the security of formulae vis-a-vis the security of VBA ?
Anyway , my point was that whether you use a single line of code or a thousand lines of code , the objective is to develop a robust application which performs ; if at all VBA is acceptable to you , then using VBA you would have been up and running by now , since what you have been trying to do getting formulae from various members over the past several months , could have been done better using VBA in a matter of weeks.
Narayan
Hi Narayan,
Actually, not really understand you, and apparently you do not understand me, though my English is not perfect as yours.
I work privately, in the stock market, I have no desire to develop applications !
Do not like the vba, I prefer add-in for Excel, XLL written in C ++, I use them.
CountConsVal ,I have not found like in xll !
I hope that now you understand me.
David
Actually, not really understand you, and apparently you do not understand me, though my English is not perfect as yours.
I work privately, in the stock market, I have no desire to develop applications !
Do not like the vba, I prefer add-in for Excel, XLL written in C ++, I use them.
CountConsVal ,I have not found like in xll !
I hope that now you understand me.
David