formula for checking specific difference

[excelNewbie22]

hi!
how can i check for certain difference between all numbers?
i need to check multiple numbers for several testings
the range is: c2:h2 with 6 numbers for example: 3-11-15-20-30-32
not only the difference between 32 and 30 but 32 to 20 and 15 and 11 and 3
and same goes for 20 to 15 to 11 and so on...
only positive numbers,
formula which i can simply change the one number=difference each time
and if possible to check even 2 or more numbers=differences


something else, is there an easy way to fill down a column except shift+ctrl+down_arrow ?
lot of times it takes me to the million row!!
so i had to manually select thousands of rows with pagedown+down_arrow

 

[RobP]

Hi,

your description of the problem might need a little work on your part.

I think what you might need to explain is what you then have to do with the differences between each numbers ? I mean what happens if there is a certain difference between these numbers ie. what constitutes a "good" result ? what do you need it to do ?

From what you explain, I assume that the difference between the number sequences are mostly different (ie. its not always 3, or not always 5 between each of the 6 numbers)

Cheers
Rob

 

[kvsrinivasamurthy]

Pl show how your result should be for the above data.

 

[excelNewbie22]

does that help?

for some reason the add-in xl2bb doesn't working right now

no "good" or "bad" result, just how many from which i'll define in the formula, that's it

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J
1	1	2	3	4	5	6				Differences to include:
2	1	15	17	25	29	38		2:1, 4:1, 8:1, 9:1, 10:1, 12:1, 13:1, 14:2, 16:1, 21:1, 23:1, 24:1, 28:1, 37:1		14
3	1	15	17	25	29	38		2:1, 5:0, 14:2, 37:1		2
4										5
5										37
6
Sheet1

Cell Formulas
Range	Formula
H2	H2	=LET(s,SEQUENCE(15),c,CHOOSE(s,B2-A2,C2-B2,C2-A2,D2-C2,D2-B2,D2-A2,E2-D2,E2-C2,E2-B2,E2-A2,F2-E2,F2-D2,F2-C2,F2-B2,F2-A2),sa,SEQUENCE(F2),sb,SEQUENCE(F2+1),f,FREQUENCY(c,sa),TEXTJOIN(", ",1,IF(f=0,"",sb&":"&f)))
H3	H3	=LET(s,SEQUENCE(15),c,CHOOSE(s,B2-A2,C2-B2,C2-A2,D2-C2,D2-B2,D2-A2,E2-D2,E2-C2,E2-B2,E2-A2,F2-E2,F2-D2,F2-C2,F2-B2,F2-A2),sa,SEQUENCE(F2),sb,SEQUENCE(F2+1),f,FREQUENCY(c,sa),TEXTJOIN(", ",1,IF(ISERROR(MATCH(sb,$J$2:$J$10,0)),"",sb&":"&f)))

The H2 formula shows all the differences. Each difference is shown followed by the number of times it occurs. So a difference of 2 occurs once (17-15), and 14 occurs twice (29-15 and 15-1). If you want to just see certain differences, use the H3 formula, and put the ones you want to see in J2:J10.

As far as using the Control+Shift+Down arrow, I've used that successfully a lot. You just need to choose a column that is populated, then switch to the column you want to put the formula in.

 

[excelNewbie22]

Eric, thanks a bunch !

 

[Eric W]

Glad we could help!

 

[excelNewbie22]

forgot something....
i need to fill down lots of rows and then count the results
and with this formula, which is great, for real, can use it for lots of ways...
but i can't count with it, or sum it up,
any way to change it to present only the number of times?
or maybe separate the 'number in question' with the result='number of times' in different cells?

test.xlsm
	C	D	E	F	G	H	I	J
2	4	12	14	19	27	44		10:1
3	1	2	12	22	32	42		10:4
4	12	15	30	40	41	42		10:1
5								can't count or sum the above rows
test

Cell Formulas
Range	Formula
J2:J4	J2	=LET(s,SEQUENCE(15),c,CHOOSE(s,D2-C2,E2-D2,E2-C2,F2-E2,F2-D2,F2-C2,G2-F2,G2-E2,G2-D2,G2-C2,H2-G2,H2-F2,H2-E2,H2-D2,H2-C2),sa,SEQUENCE(H2),sb,SEQUENCE(H2+1),f,FREQUENCY(c,sa),TEXTJOIN(", ",1,IF(ISERROR(MATCH(sb,$K$2:$K$20,0)),"",sb&":"&f)))

p.s.
xl2bb working again!

 

[Eric W]

A couple of options:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			1	2	3	4	5	6				Differences to include:	# of rows	Total
2			4	12	14	19	27	44		1:0, 4:0, 7:1, 10:1		10	3	6
3			1	2	12	22	32	42		1:1, 4:0, 7:0, 10:4		7	1	1
4			12	15	30	40	41	42		1:2, 4:0, 7:0, 10:1		1	2	3
5												4	0	0
6													0	0
7
Sheet1

Cell Formulas
Range	Formula
M2:M6	M2	=SUM(--ISNUMBER(FIND(" "&L2&":"," "&$J$2:$J$20)))-SUM(--ISNUMBER(FIND(" "&L2&":0,"," "&$J$2:$J$20)))
N2:N6	N2	=LET(a,MID($J$2:$J$20,FIND(" "&L2&":"," "&$J$2:$J$20)+LEN(L2)+1,99),b,LEFT(a,FIND(",",a&",")-1),SUM(IFERROR(b+0,0)))
J2:J4	J2	=LET(s,SEQUENCE(15),c,CHOOSE(s,D2-C2,E2-D2,E2-C2,F2-E2,F2-D2,F2-C2,G2-F2,G2-E2,G2-D2,G2-C2,H2-G2,H2-F2,H2-E2,H2-D2,H2-C2),sa,SEQUENCE(H2),sb,SEQUENCE(H2+1),f,FREQUENCY(c,sa),TEXTJOIN(", ",1,IF(ISERROR(MATCH(sb,$L$2:$L$10,0)),"",sb&":"&f)))

This uses the same output, but I added the M:N formulas to create the totals you're looking for. They're fairly complicated, so I came up with option 2:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			1	2	3	4	5	6		10	7	1	4
2			4	12	14	19	27	44		1	1	0	0
3			1	2	12	22	32	42		4	0	1	0
4			12	15	30	40	41	42		1	0	2	0
Sheet2

Cell Formulas
Range	Formula
J2:M4	J2	=LET(s,SEQUENCE(15),c,CHOOSE(s,$D2-$C2,$E2-$D2,$E2-$C2,$F2-$E2,$F2-$D2,$F2-$C2,$G2-$F2,$G2-$E2,$G2-$D2,$G2-$C2,$H2-$G2,$H2-$F2,$H2-$E2,$H2-$D2,$H2-$C2),sa,SEQUENCE($H2),f,FREQUENCY(c,sa),IFERROR(INDEX(f,J$1),0))

In this version, you put the differences you want in J1:M1, put the formula in J2 and drag it down and across as far as needed. The result is a list of how many times each difference occurs in each row. Then the sum is merely SUM(J2:J20) and the number of rows is COUNTIF(J2:J20,">0").

Hope this helps!

 

[excelNewbie22]