Need a formula can count distance between groups.

[motilulla]

Using Excel 2010

Hello,

I need a formula which can count a distance between groups. IN the column “C” I have some lottery numbers lottery is divided in the 4 groups of 5-numbers

I need a formula…
In cells E7:F12 for Group1
In cells H7:I12 for Group2
In cells K7:L12 for Group3
In cells N7:O12 for Group4

Now for example…in the cell C7 first number come up this is 3 and placed under the group1 in the cell S7…

Cell E7:F7 I need a formula which tells me if next coming up number (adding or subtracting from 3) will be -2 or +2 will be remain in the group1

Cell H7:I7 I need a formula which tells me if next coming up number (adding or subtracting from 3) will be 3 or 7 will be remain in the group2

Cell K7:L7 I need a formula which tells me if next coming up number (adding or subtracting from 3) will be 8 or 12 will be remain in the group3

Cell N7:O7 I need a formula which tells me if next coming up number (adding or subtracting from 3) will be 13 or 17 will be remain in the group4

This is how I want to count the distance of each number between groups same as for next number 13, 8, 19, 1 and 14 hope I have explained if any question please I will try to explain it again.

Excel Question.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1					Group1			Group2			Group3			Group4
2					1	1		6	6		11	11		16	16
3					To	To		To	To		To	To		To	To
4					5	5		10	10		15	15		20	20
5			Lottery		Move			Move			Move			Move			Group1					Group2					Group3					Group4
6			Number		Min	Max		Min	Max		Min	Max		Min	Max		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
7			3		-2	2		3	7		8	12		13	17				3
8			12		-7	-11		-2	-6		-1	3		4	8													12
9			8		-3	-7		-2	2		3	7		8	12									8
10			19		-14	-18		-9	-13		-4	-8		-3	1																				19
11			1		0	4		5	9		10	14		15	19		1
12			14		-9	-13		-4	-8		-3	1		2	6															14
13
14
15
16
17
18
19
20
Sheet4

Thank you all.

Regards,
Moti

 

[Snakehips]

@motilulla I think this might do it?
Formulas in E7 & F7 can be pulled down. Then E7:F12 can be Copy Pasted to the other three ranges.
If my understanding of Max and Min is that Min is the shorter distance then I think you have your example results the wrong way round in K12:L12 and N10:O10?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					Group1			Group2			Group3			Group4
2					1	1		6	6		11	11		16	16
3					To	To		To	To		To	To		To	To
4					5	5		10	10		15	15		20	20
5			Lottery		Move			Move			Move			Move
6			Number		Min	Max		Min	Max		Min	Max		Min	Max
7			3		-2	2		3	7		8	12		13	17
8			12		-7	-11		-2	-6		-1	3		4	8
9			8		-3	-7		-2	2		3	7		8	12
10			19		-14	-18		-9	-13		-4	-8		1	-3
11			1		0	4		5	9		10	14		15	19
12			14		-9	-13		-4	-8		1	-3		2	6
13
Sheet1

Cell Formulas
Range	Formula
E7:E12,N7:N12,K7:K12,H7:H12	E7	=IF(E$4-$C7=2,$C7-E$4,IF(ABS(E$2-$C7)<ABS(E$4-$C7),E$2-$C7,E$4-$C7))
F7:F12,O7:O12,L7:L12,I7:I12	F7	=IF(ABS(E$2-$C7)>ABS(E$4-$C7),E$2-$C7,E$4-$C7)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7:O12	Expression	=E7>=0	text	NO
E7:O12	Expression	=E7<0	text	NO

Hope that helps.

 

[motilulla]

@motilulla I think this might do it?
Formulas in E7 & F7 can be pulled down. Then E7:F12 can be Copy Pasted to the other three ranges.
If my understanding of Max and Min is that Min is the shorter distance then I think you have your example results the wrong way round in K12:L12 and N10:O10?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					Group1			Group2			Group3			Group4
2					1	1		6	6		11	11		16	16
3					To	To		To	To		To	To		To	To
4					5	5		10	10		15	15		20	20
5			Lottery		Move			Move			Move			Move
6			Number		Min	Max		Min	Max		Min	Max		Min	Max
7			3		-2	2		3	7		8	12		13	17
8			12		-7	-11		-2	-6		-1	3		4	8
9			8		-3	-7		-2	2		3	7		8	12
10			19		-14	-18		-9	-13		-4	-8		1	-3
11			1		0	4		5	9		10	14		15	19
12			14		-9	-13		-4	-8		1	-3		2	6
13
Sheet1

Cell Formulas
Range	Formula
E7:E12,N7:N12,K7:K12,H7:H12	E7	=IF(E$4-$C7=2,$C7-E$4,IF(ABS(E$2-$C7)<ABS(E$4-$C7),E$2-$C7,E$4-$C7))
F7:F12,O7:O12,L7:L12,I7:I12	F7	=IF(ABS(E$2-$C7)>ABS(E$4-$C7),E$2-$C7,E$4-$C7)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7:O12	Expression	=E7>=0	text	NO
E7:O12	Expression	=E7<0	text	NO

Hope that helps.

Snakehips, just applied the formulas its result are as I needed amazing!

Thank you for your kind help it has been very valuable for me.

Good Luck

Kind Regards,
Moti