Counting ascending and falling numbers in a row

[bilbon]

Hi
I would like to count ascending and falling numbers in a row from a row of 13 numbers.
I want to count 3 numbers in a row and 4 numbers in a row.
3 numbers in a row can come in 11 different places and 4 numbers in a row in 10 different places. I just need the numbers and not the combinations.
[TABLE="width: 508"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]

</tbody>[/TABLE]



[TABLE="width: 1145"]
<colgroup><col span="13"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Rising numbers in a row[/TD]
[TD][/TD]
[TD="colspan: 2"]Falling numbers in a row[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3 in a row[/TD]
[TD]4 in a row[/TD]
[TD] [/TD]
[TD]3 in a row[/TD]
[TD]4 in a row[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]36[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]8[/TD]
[TD]15[/TD]
[TD]5[/TD]
[TD]20[/TD]
[TD]12[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
Rising	Falling
3 in a row	4 in a row

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O3[/TH]
[TD="align: left"]{=SUM(IF((A3:K3<B3:L3)*(B3:L3<C3:M3),1))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P3[/TH]
[TD="align: left"]{=SUM(IF((A3:J3<B3:K3)*(B3:K3<C3:L3)*(C3:L3<D3:M3),1))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]R3[/TH]
[TD="align: left"]{=SUM(IF((A3:K3>B3:L3)*(B3:L3>C3:M3),1))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S3[/TH]
[TD="align: left"]{=SUM(IF((A3:J3>B3:K3)*(B3:K3>C3:L3)*(C3:L3>D3:M3),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[bilbon]

Hi Eric


As usual, I am bad to explainin what I want help with. I am sorry for that.


What I want is the number of combinations that are 3 numbers long and rising and in a row.
In O3 the answer must be 3 and the combinations that exist are 1-3-10,3-10-23,10-23-24.


In P3 the answer must be 2 and the combinations that exist are 1-3-10-23 and 3-10-23-24.


Here I want the number of combinations that are 4 numbers long and falling and in a row.
In R3, the answer must be 1 and the combination that exists is 20-12-11


In S3, the answer is 0 since there is no combination.

/Bilbon

 

[Fluff]

The board has chewed up the first 2 formulae that Eric supplied. Try

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1															Rising numbers in a row		Falling numbers in a row
2															3 in a row	4 in a row	3 in a row	4 in a row
3	2	36	1	3	10	23	24	8	15	5	20	12	11		3	2	1	0
sheet1

Cell Formulas
Range	Formula
O3	{=SUM(IF((A3:K3)*(B3:L3),1))}
P3	{=SUM(IF((A3:J3< B3:K3)*(B3:K3< C3:L3)*(C3:L3< D3:M3),1))}
Q3	{=SUM(IF((A3:K3>B3:L3)*(B3:L3>C3:M3),1))}
R3	{=SUM(IF((A3:J3>B3:K3)*(B3:K3>C3:L3)*(C3:L3>D3:M3),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bilbon]

Hi Fluff and Eric


Thank you for your suggestions.
Fluff your suggestion works just like I wanted, very grateful.
Eric boring you had to put time because of my ambiguity but thankful for your help too


/Bilbon

 

[Fluff]

It's actually Eric's formula not mine.
Sometimes the software interprets < & > signs as HTML & therefore mucks the formula.

 

[Eric W]

Aargh! You'd think I'd learn by now. Maybe I'll take a look at the HTML Maker and see if it can automatically add the spaces as needed.

Bilbon, I'm glad that works for you.

Fluff, thanks for showing the complete formulas.

 

[MARK858]

Eric, it only affects the < symbol followed by a letter and another option is to replace the < symbol with &lt ; (without the space) which will display < and the rest of the formula correctly.

 

[James006]

Eric, it only affects the < symbol followed by a letter and another option is to replace the < symbol with &lt ; (without the space) which will display < and the rest of the formula correctly.

@MARK858

Thanks for this clear explanation ...