Highlight consecutive(sequential) numbers in excel

[Samgraphics]

Hi, can someone please help me? I'm trying to highlight combinations of numbers if they increase incrementally, example 1,2,3,4,5 or combination where the interval is increasing at the same rate like 5,12,20,29,39 or combinations with equal intervals like 2,4,6,8,10. So any combinations like the ones I highlighted below would be highlighted.

Thank you in advance

All combinations 2023-24.xlsm
	C	D	E	F	G
1	1	2	3	4	5
2	1	2	3	4	6
3	1	2	3	4	7
4	1	2	3	4	8
5	1	2	3	4	9
6	2	4	6	8	10
DA LUO TO

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=IF(AND(C1=D1-1, D1=E1-1, E1=F1-1, F1=G1-1), "Consecutive", "Not Consecutive")	text	NO

 

[Peter_SSs]

Could this work for you?

23 12 17.xlsm
	C	D	E	F	G
1	1	2	3	4	5
2	1	2	3	4	6
3	1	2	3	4	7
4	1	2	3	4	8
5	1	2	3	4	9
6	2	4	6	8	10
Consistent Change

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:G6	Expression	=MIN($D1:$G1-$C1:$F1)=MAX($D1:$G1-$C1:$F1)	text	NO

 

[Samgraphics]

Could this work for you?

23 12 17.xlsm
	C	D	E	F	G
1	1	2	3	4	5
2	1	2	3	4	6
3	1	2	3	4	7
4	1	2	3	4	8
5	1	2	3	4	9
6	2	4	6	8	10
Consistent Change

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:G6	Expression	=MIN($D1:$G1-$C1:$F1)=MAX($D1:$G1-$C1:$F1)	text	NO

HI, Thank you so much. This works fine for combinations that increase incrementally. Can you explain how it works or me please? Also is there one that can highlight it if it increases at the same rate?

 

[Peter_SSs]

Can you explain how it works or me please?

The formula works out the difference between every pair of consecutive cells. For row 6 it works out
4-2=2
6-4=2
8-6=2
10-8=2

The formula then compares the smallest of those 4 answers (2) and the largest of those 4 answers (2) and if they are the same as each other (they are) then there is a consistent difference so highlight.

Also is there one that can highlight it if it increases at the same rate?

Like this?

23 12 17.xlsm
	C	D	E	F	G
1	5	12	20	29	39
2	5	10	16	23	30
3	3	10	20	33	49
Consistent Increase

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:G3	Expression	=MIN(($E1:$G1-$D1:$F1)-($D1:$F1-$C1:$E1))=MAX(($E1:$G1-$D1:$F1)-($D1:$F1-$C1:$E1))	text	NO

 

[Samgraphics]

Really thank you very much for your help.

The formula works out the difference between every pair of consecutive cells. For row 6 it works out
4-2=2
6-4=2
8-6=2
10-8=2

The formula then compares the smallest of those 4 answers (2) and the largest of those 4 answers (2) and if they are the same as each other (they are) then there is a consistent difference so highlight.


Like this?

23 12 17.xlsm
	C	D	E	F	G
1	5	12	20	29	39
2	5	10	16	23	30
3	3	10	20	33	49
Consistent Increase

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:G3	Expression	=MIN(($E1:$G1-$D1:$F1)-($D1:$F1-$C1:$E1))=MAX(($E1:$G1-$D1:$F1)-($D1:$F1-$C1:$E1))	text	NO

 

[Samgraphics]

Currently I'm using this formula to check if a previously drawn number is in the group,the countifs, but it's long and it's really slowing down excel. Is there another way to do it? A less cumbersome way?

All combinations 2023-24.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N
1	1	2	3	4	5			1	2	3	4	30
2	1	2	3	4	6			9	20	23	29	33
3	1	2	3	4	7			4	13	15	17	32
4	1	2	3	4	8			4	22	25	30	31
5	1	2	3	4	9			1	2	9	19	30
6	1	2	3	4	10			15	16	25	31	34
7	1	2	3	4	11			7	12	20	28	31
8	1	2	3	4	12			4	19	21	30	31
9	1	2	3	4	13			13	23	27	30	34
10	1	2	3	4	14			5	18	22	28	29
11	1	2	3	4	15			10	15	22	27	33
12	1	2	3	4	16			10	18	25	28	33
13	1	2	3	4	17			3	4	12	15	23
14	1	2	3	4	18			3	7	21	22	24
15	1	2	3	4	19			13	20	27	29	30
16	1	2	3	4	20			9	23	25	27	33
17	1	2	3	4	21			1	5	7	12	13
18	1	2	3	4	22			4	7	8	18	19
19	1	2	3	4	23			7	12	17	26	34
20	1	2	3	4	24			7	25	26	29	32
21	1	2	3	4	25			6	18	23	27	32
22	1	2	3	4	26			2	14	16	28	30
23	1	2	3	4	27			1	7	9	25	29
24	1	2	3	4	28			5	11	15	23	33
25	1	2	3	4	29			2	8	16	31	34
26	1	2	3	4	30			4	8	10	14	21
DA LUO TO

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:G98280	Expression	=COUNTIFS($J$1:$J$98280,$C1,$K$1:$K$98280,$D1,$L$1:$L$98280,$E1,$M$1:$M$98280,$F1,$N$1:$N$98280,$G1)	text	NO
C1:G98280	Expression	=MIN($D1:$G1-$C1:$F1)=MAX($D1:$G1-$C1:$F1)	text	NO
C1:G98280	Expression	=MIN(($E1:$G1-$D1:$F1)-($D1:$F1-$C1:$E1))=MAX(($E1:$G1-$D1:$F1)-($D1:$F1-$C1:$E1))	text	NO

Thank you.

 

[Peter_SSs]

Really thank you very much for your help.

You're welcome. Glad to help.

Currently I'm using this formula to check if a previously drawn number is in the group, ..

This appears to be a completely different question. Please start a new thread for it.

 

[Samgraphics]

You're welcome. Glad to help.


This appears to be a completely different question. Please start a new thread for it.

sure thing. Sorry about that.

 

[Samgraphics]

The formula works out the difference between every pair of consecutive cells. For row 6 it works out
4-2=2
6-4=2
8-6=2
10-8=2

The formula then compares the smallest of those 4 answers (2) and the largest of those 4 answers (2) and if they are the same as each other (they are) then there is a consistent difference so highlight.


Like this?

23 12 17.xlsm
	C	D	E	F	G
1	5	12	20	29	39
2	5	10	16	23	30
3	3	10	20	33	49
Consistent Increase

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:G3	Expression	=MIN(($E1:$G1-$D1:$F1)-($D1:$F1-$C1:$E1))=MAX(($E1:$G1-$D1:$F1)-($D1:$F1-$C1:$E1))	text	NO

Hi, is there any way to modify this to find 3 consecutive numbers in any position? For example 1,2,3 or 2,3,4 or 3,4,5?

Thank you.

 

[Peter_SSs]

Please be careful with your 'quotes' and only quote relevant posts or parts of them. I have just removed three quotes from your last post that, as far as I can see, had nothing to do with this follow-up question.

Hi, is there any way to modify this to find 3 consecutive numbers in any position? For example 1,2,3 or 2,3,4 or 3,4,5?

Could you give some sample data and expected results with XL2BB so that we can see what range(s) you are working with and how you would want the results presented? Make sure that there are some examples where there are 3 (or more?) consecutive numbers and some examples where there are no sets of 3 consecutive numbers.