compare subtraction in following numbers

[excelNewbie22]

hello!
is there a better formula for checking ALL subtractions between two following/neighboring
(e2:d2 and h2:g2 and so on)
numbers if they are identical?

cause i want to modify the wanted formula for checking all kinds of rangers and it's tedious the way i wrote it
for example: i want to check if the subtraction of every two cells, i2-h2 and h2-g2 and etc are the same BUT without the subtraction of e2-d2
so i need a more flexible formula to adujst

test
	C	D	E	F	G	H	I	J
1	1	2	3	4	5	6	7	identical subtractions
2	2	4	6	8	10	12	14	1
3	1	5	22	55	98	99	100	0
4	10	15	20	25	30	35	40	1
test

Cell Formulas
Range	Formula
J2:J4	J2	=IF(AND(I2-H2=H2-G2,H2-G2=G2-F2,G2-F2=F2-E2,F2-E2=E2-D2,E2-D2=D2-C2),1,0)

 

[excelNewbie22]

6StringJazzer - sincerely... i'm agreeing with you, i'm doing my best to explain (eng isn't my native lang)

Eric W - thank you for joining in and trying to help!
column L is great!!
and M even greater!

thanks a bunch!!!!

you offered/asked:
"Notice that on the last row, there were 3 differences of 2, and 3 differences of 5, but only 5 showed up. Do you want both to show up somehow?"
i'll mostly working with 6 cells (and sometimes 4) so the number of diff's will be uneven, so there will not be a problem of equal diff's, but if there's way to show both MCD i'll gladly accept your offer!

 

[excelNewbie22]

stand corrected, even with 6 cells (or 4) there's can be even diff's
like in 1-3-5-8-11-15 there's diff's of 2 of 2's and 2 of 3's and 1 of 4

 

[Eric W]

Try this:

Book1
	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4	5	6		# of MCD	Most common difference	MCD 2 (if more than 1)
2	2	4	6	8	10	12		5	2
3	1	5	22	55	98	99		1	4	17
4	10	15	20	25	30	35		5	5
5	2	4	6	9	10	12		3	2
6	2	4	6	11	16	21		3	5
7	1	3	5	8	11	15		2	2	3
Sheet8

Cell Formulas
Range	Formula
J2:J7	J2	=SUMPRODUCT(--(D2:H2-C2:G2=K2))
K2:K7	K2	=MODE((D2:H2-C2:G2)*{1;1})
L2:L7	L2	=IFERROR(INDEX(MODE.MULT((D2:H2-C2:G2)*{1;1}),2),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

I moved the columns to make it a bit easier. Your example is on row 7. Also look at row 3. It only shows 4 and 17, but since every difference is different (4,17,33,43,1) it only shows the first 2. You can just copy the formula to the right and change the index from 2 to 3 or more to get the rest. There's a way to show all of them with one formula, but it's probably not worth explaining. Hope this helps!

 

[excelNewbie22]

perfect!
just one little thing, when there's 3 diff's and then 2's, the formula in col L don't give the second diff
see row 7

test
	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4	5	6		# of MCD	Most common difference	MCD 2 (if more than 1)
2	2	4	6	8	10	12		5	2
3	1	5	22	55	98	99		1	4	17
4	10	15	20	25	30	35		5	5
5	2	4	6	9	10	12		3	2
6	2	4	6	11	16	21		3	5
7	1	3	5	7	11	15		3	2
test

Cell Formulas
Range	Formula
J2:J7	J2	=SUMPRODUCT(--(D2:H2-C2:G2=K2))
K2:K7	K2	=MODE((D2:H2-C2:G2)*{1;1})
L2:L7	L2	=IFERROR(INDEX(MODE.MULT((D2:H2-C2:G2)*{1;1}),2),"")

 

[Eric W]

The formula only shows the differences that are the most frequent, or are tied for the most frequent. If you want 2 or more of the differences sorted by frequency, that's much tougher. I could do it with the newer Excel functions, but it's a challenge with the 2013 functions.

 

[excelNewbie22]

no need,
i'll have to do with what you already helped me
thanks! again!