Formula for selecting 3 consecutive occurrences.

[championbowler]

Can someone please explain to me what formula is needed to acheive the below highlighted cells when there is 3 consecutive entries in the us or the them column. Any help would be greatly appreciated

 

[RobP]

Hi, welcome to the forum.

I think we might need a little more explanation to your problem. As the "End" column appears to just be a list of consecutive numbers ?

When you say 3 consecutive entries in US or THEM, if I look at ends 8-14, do you mean each row (eg. Them = 5, Them = 1, US = 1) so that would constitute three consecutive entries ?

Are the "End" columns blank until you get 3 entries, then you add another one ? (so you're just adding +1 to the one above ?

any clarifications might help us understand your need... do you talk about Red or Green highlights .. ?

cheers
Rob

 

[championbowler]

Can someone please explain to me what formula is needed to acheive the below highlighted cells when there is 3 consecutive entries in the us or the them column. Any help would be greatly appreciated

Thanks for replying so effectively i will have 2 formulas one for the us column where there is an entry of a number of 3 or more times i want this to be filled green as per example and the other will be the exact same argument but for the them column filled in red. I hope this clarifes.

 

[RobP]

I see, so you actually want to "colour" or actually Highlight the cells, rather than change data inside (sorry, I missed that from your original post). As far as I know there's no formula as such to achieve what you need, sorry.

Closest I I would suggest perhaps a TOTAL US and TOTAL THEM cell be added at the bottom of your list, with a COUNTA to give a total. Then conditional format each total based on the other.
Not really what you want to achieve .. but some kind of highlight

Book1
	J	K	L
1	ENDS 15 - 21
2
3	END	lJS	THEM
4	15	3
5	16		2
6	17		3
7	18		1
8	19	1
9	20	3
10	21	2
11
12		TOTAL	TOTAL
13		4	3
14
Sheet1

Cell Formulas
Range	Formula
K13:L13	K13	=COUNTA(K4:K10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L13	Cell Value	>$K$13	text	NO
K13	Cell Value	>$L$13	text	NO

 

[championbowler]

I see, so you actually want to "colour" or actually Highlight the cells, rather than change data inside (sorry, I missed that from your original post). As far as I know there's no formula as such to achieve what you need, sorry.

Closest I I would suggest perhaps a TOTAL US and TOTAL THEM cell be added at the bottom of your list, with a COUNTA to give a total. Then conditional format each total based on the other.
Not really what you want to achieve .. but some kind of highlight

Book1
	J	K	L
1	ENDS 15 - 21
2
3	END	lJS	THEM
4	15	3
5	16		2
6	17		3
7	18		1
8	19	1
9	20	3
10	21	2
11
12		TOTAL	TOTAL
13		4	3
14
Sheet1

Cell Formulas
Range	Formula
K13:L13	K13	=COUNTA(K4:K10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L13	Cell Value	>$K$13	text	NO
K13	Cell Value	>$L$13	text	NO

Thanks ill have a look into it appreciate your time.

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	ENDS 1 - 7				ENDS 8 - 14				ENDS 15 - 21
2	END	US	THEM		END	US	THEM		END	US	THEM
3	1	1			8		5		15	3
4	2		2		9		1		16		2
5	3		2		10	1			17		3
6	4		2		11		3		18		1
7	5		1		12		1		19	1
8	6	4			13		2		20	3
9	7	1			14	2			21	2
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K3:K9	Expression	=MAX(COUNT(K1:K3),COUNT(K2:K4),COUNT(K3:K5))>2	text	NO
G3:G9	Expression	=MAX(COUNT(G1:G3),COUNT(G2:G4),COUNT(G3:G5))>2	text	NO
C3:C9	Expression	=MAX(COUNT(C1:C3),COUNT(C2:C4),COUNT(C3:C5))>2	text	NO
B3:B9	Expression	=MAX(COUNT(B1:B3),COUNT(B2:B4),COUNT(B3:B5))>2	text	NO
F3:F9	Expression	=MAX(COUNT(F1:F3),COUNT(F2:F4),COUNT(F3:F5))>2	text	NO
J3:J9	Expression	=MAX(COUNT(J1:J3),COUNT(J2:J4),COUNT(J3:J5))>2	text	NO

 

[championbowler]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	ENDS 1 - 7				ENDS 8 - 14				ENDS 15 - 21
2	END	US	THEM		END	US	THEM		END	US	THEM
3	1	1			8		5		15	3
4	2		2		9		1		16		2
5	3		2		10	1			17		3
6	4		2		11		3		18		1
7	5		1		12		1		19	1
8	6	4			13		2		20	3
9	7	1			14	2			21	2
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K3:K9	Expression	=MAX(COUNT(K1:K3),COUNT(K2:K4),COUNT(K3:K5))>2	text	NO
G3:G9	Expression	=MAX(COUNT(G1:G3),COUNT(G2:G4),COUNT(G3:G5))>2	text	NO
C3:C9	Expression	=MAX(COUNT(C1:C3),COUNT(C2:C4),COUNT(C3:C5))>2	text	NO
B3:B9	Expression	=MAX(COUNT(B1:B3),COUNT(B2:B4),COUNT(B3:B5))>2	text	NO
F3:F9	Expression	=MAX(COUNT(F1:F3),COUNT(F2:F4),COUNT(F3:F5))>2	text	NO
J3:J9	Expression	=MAX(COUNT(J1:J3),COUNT(J2:J4),COUNT(J3:J5))>2	text	NO

Ill give this a try thank you for taking the time to help