Assign "1" to numbers in order

[ibmy]

Hi, need expert help.
Noob like me only managed to get the idea : =IF(A1+1=A3-1, "1", "0") but doest work ?

Column A --- Column B

7 ---
1 --- (no count since only number 1)
5 ---
1 ---
3 --- (no count since no number 2 for order)
1 ---
2 ---
3 --- 1
1 ---
2 --- 1
0 ---
8---
1 ---
2 ---
4 --- (no count since no number 3 for order)
1 ---
2 ---
3 ---
4 ---
5 ---
6 ---
7 --- 1

- Order starting from number 1
- Count restart when number is not in order and find next number 1 with order ( at least number 2)
- At least number 2 is counted, if there is only number 1- no count

 

[jasonb75]

Try

=IF(N(A2)=N(A1)+1,N(B1)+1,"")

 

[gaz_chops]

Try

=IF(AND(A2=A1+1,A3<>A2+1),1,"")

enter in B2

 

[Snakehips]

@ibmy Your result above suggests I may be wrong but, is this what you want?

Book1
	A	B
1	7
2	1
3	5
4	1
5	3
6	1
7	2
8	3	1
9	1
10	2	1
11	0
12	8
13	1
14	2	1
15	4
16	1
17	2
18	3
19	4
20	5
21	6
22	7	1
23
HRIS

Cell Formulas
Range	Formula
B2:B22	B2	=IF(AND(A2>1,A1=A2-1,A3<>A2+1),1,"")

 

[ibmy]

@jasonb75 @gaz_chops @Snakehips It work at some row and do not work at some row

File : Data 8-5-2020

Can I ask for a little bit change how do I want the outcome of result. Sorry for change of topic thread.

- Count the number in order increasingly at least 2 number -


Column A --- Column B


6
7 --- 1 order
7
8
9 --- 2 order
5
4
5
6 --- 2 order
4
5
6
7 --- 2 order
10
13
12
13 --- 1 order
13
14 --- 1 order
16
17
18 --- 2 order
18 ---
18 ---
16 ---

- Order can start from any number AND order must at least 2 number increasingly.
- Count reset if there is number lower than the current value OR there duplicate/repetitie number.

The tricky part is when the sequence are :-


1) Order are skip 1 number then reset to new order .

Example 1:

1 --
2 --- 1 order
4 ---
5 --- 1 order
7 ---
8 --- 1 order

Example 2:

1 ---
2 ---
3 --- 2 order
5 ---
6 ---
7 --- 2 order

2) Duplicate/repetitive are not count as long as no order found.

Example 1:

1 ---
2 ---
3 --- 2 order
3 ---
3 ---
3 ---
4 ---
5 ---
6 --- 3 order

Example 2:

1 --
2 ---
3 --- 2 order
3 ---
4 ---
5 ---
6 --- 3 order

 

[jasonb75]

Think I have this but not sure,

Book1
	A	B
1	7
2	1
3	5
4	1
5	3
6	1
7	2
8	3	2
9	1
10	2	1
11	0
12	8
13	1
14	2	1
15	4
16	1
17	2
18	3
19	4
20	5
21	6
22	7	6
Sheet7

Cell Formulas
Range	Formula
B2:B22	B2	=IF(AND(A2=A1+1,A3<>A2+1),A2-LOOKUP(2,1/(A$2:A2<>(A$1:A1+1)),A$2:A2),"")

 

[ibmy]

Thanks @jasonb75 It works. ?

@gaz_chops and @Snakehips Thank to you two. Your original formula help me to understand what I want actually achieve. ?

mrexcel forum is great!