differentials patterns

[excelNewbie22]

i have a file of thousands of lines with numbers, let's say:

5 11 25 26 28 40
2 20 22 23 30 31
6 7 10 11 19 20
10 12 15 17 20 22

and i want, by formula, to check for example if there is a pattern of differential of 2 (between 2 following numbers) then 3 then 2 then 3 and so on,
and like i say this is just an example, cause i want to check lot's of patterns like that,
so i want to be able to modify the formula each time to my needs, like to 3 and then 4 then 3 and so on....

and if possible for the formula to check the entire file at once (like c2:h2000) but to check only horizontal lines (c2:h2 then c3:h3 and etc)
and return how many matched, like 10 or 20...

thank u all for your time and expertise!

 

[Rick Rothstein]

If you put this formula in cell J2 and copy down, you will have a column with all your differential (separated by a space) in them... you can now write a simple formula to check any given pattern. For example, put your pattern in cell K1 (e.g., 2 3 2 3 2) and then put this formula in cell K2 and copy it down...

=IF(K$1=J2,1,"")

and every row whose differentials match your pattern will be shown with a 1 in it. To find out how many of these pattern matches there are, simply SUM column K...

=SUM(K:K)

You can now test other patterns by simply changing the differential pattern in cell K1.

 

[JamesCanale]

Mr Excel Playground 3.xlsm
	C	D	E	F	G	H	I
1	2	3	2	3	2
2	5	7	10	12	15	17	TRUE
3	3	4	5	6	7	8	FALSE
4	6	8	11	13	16	18	TRUE
5	0	5	6	11	12	17	FALSE
Sheet16

Cell Formulas
Range	Formula
E1:G1	E1	=C1
I2:I5	I2	=AND((D2:H2-C2:G2)=ROW(INDIRECT("1:5"))*0+1*$C$1:$G$1)

 

[excelNewbie22]

rick, if u'r willing to explain again please, maybe with an example?

22
	A	B	C	D	E	F
1	1	2	3	4	5	6
2	5	11	25	26	28	40
3	2	20	22	23	24	25
4	10	12	15	17	20	22
גיליון1

james, it's working good, thank u very much!
i just want to understand if rick's is easier for me

 

[Eric W]

Here's another option:

Book4
	C	D	E	F	G	H	I	J
1	2	3	2	3	2			# of matches
2	5	7	10	12	15	17	TRUE	3
3	3	4	5	6	7	8	FALSE
4	6	8	11	13	16	18	TRUE
5	0	5	6	11	12	17	FALSE
6							FALSE
7	2	4	7	9	12	14	TRUE
Sheet8

Cell Formulas
Range	Formula
J2	J2	=SUMPRODUCT(--(MMULT(--((D2:H12-C2:G12)=$C$1:$G$1),{1;1;1;1;1})=5))
I2:I7	I2	=AND((D2:H2-C2:G2)=$C$1:$G$1)

You can sum up the TRUE/FALSE values in column I with

=SUMPRODUCT(I2:I12+0)

But the J2 formula calculates the same total without needing column I.

 

[excelNewbie22]

excellent!
thank you!