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.

<colgroup><col><col><col><col><col></colgroup><tbody>

</tbody>

														Rising numbers in a row		Falling numbers in a row
														3 in a row	4 in a row		3 in a row	4 in a row
2	36	1	3	10	23	24	8	15	5	20	12	11		3	2		1

<colgroup><col span="13"><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

Try:

Excel 2012

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

<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>
</tbody>

Sheet7​

Array Formulas Cell Formula O3 {=SUM(IF((A3:K3<B3:L3)*(B3:L3<C3:M3),1))} P3 {=SUM(IF((A3:J3<B3:K3)*(B3:K3<C3:L3)*(C3:L3<D3:M3),1))} R3 {=SUM(IF((A3:K3>B3:L3)*(B3:L3>C3:M3),1))} S3 {=SUM(IF((A3:J3>B3:K3)*(B3:K3>C3:L3)*(C3:L3>D3:M3),1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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 ...