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]

in other words:
formula checking for any 3 or 4 or 5 or 6 identical subtractions between two following/neighboring cells
and the number (3 to 6) of identicals will be adjustable

 

[6StringJazzer]

This array formula will tell you the number of matching subtractions but it includes all pairs, it does not exclude E2-D2. I'm unclear on that requirement because it is included in your own formula.

If you just want to know if there are at least three matches, instead of the number, then I showed that too. Note that these are array formulas.

$scratch.xlsm
	C	D	E	F	G	H	I	J	K
1	1	2	3	4	5	6	7	identical subtractions	>=3
2	2	4	6	8	10	12	14	5	1
3	1	5	22	55	98	99	100	1	0
4	10	15	20	25	30	35	40	5	1
Subtractions

Cell Formulas
Range	Formula
J2:J4	J2	=SUM(IF(E2:I2-D2:H2=D2:H2-C2:G2,1,0))
K2:K4	K2	=IF(SUM(IF(E2:I2-D2:H2=D2:H2-C2:G2,1,0))>=3,1,0)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[excelNewbie22]

your way is better, just knowing how many identical subtractions, J2:J4,
but something is missing,
and i think my way before was confusing, (even to me)
right now there's 6 identical subtractions,
and if i change g2 to 9
the formula won't calculate into account the subtraction of i2-h2

i think what i'm trying to say is i want to know just how many identical subtractions there is?
which for that example is 6

 

[6StringJazzer]

I think you need to clarify your requirements. You said

identical subtractions between two following/neighboring cells

But there are only 5 pairs of neighboring subtractions, even though there are 6 subtractions.

 

[excelNewbie22]

you are right,
i want to know just how many identical subtractions there is in a range in of neighboring cells but without comparing them

but now i thought,
what if there's two subtractions of 2's
and two of 5's?
i don't have an idea how to present it
do you have an idea to help me?

test.xlsx
	A	B	C	D	E	F	G	H
1	1	2	3	4	5	6	7	identical subtractions
2	2	4	6	8	10	12	14	6
3	1	5	22	55	98	99	100	2
4	10	15	20	22	32	38	40	2 of 5's and 2 of 2's
test

 

[6StringJazzer]

I think it would be helpful for you to explain why you need to do this. We need to look at a better overall solution. We need to look at information, rather than cells.

 

[excelNewbie22]

kinda private project of mine,
thanks for bearing
think there's a right formula?
just for identical subtractions in a range of neighboring cells without comparing them
as for the mention problem above, i'll place it on hold

 

[6StringJazzer]

what if there's two subtractions of 2's
and two of 5's?

Well that's exactly the problem and it can't be answered by Excel until it can be answered by a human.

 

[Eric W]

I'm taking a stab at this, but I'm also a bit confused, but see if this might be a bit closer:

Book1
	C	D	E	F	G	H	I	J	K	L	M
1	1	2	3	4	5	6	7			Most common difference	# of MCD
2	2	4	6	8	10	12	14			2	6
3	1	5	22	55	98	99	100			1	2
4	10	15	20	25	30	35	40			5	6
5	2	4	6	9	10	12	14			2	4
6	2	4	6	11	16	21	88			5	3
Sheet8

Cell Formulas
Range	Formula
L2:L6	L2	=MODE((D2:I2-C2:H2)*{1;1})
M2:M6	M2	=SUMPRODUCT(--(D2:I2-C2:H2=L2))
Press CTRL+SHIFT+ENTER to enter array formulas.

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?