Formula for < or >

[serge]

I'm looking for a formula that would separate numbers in 2 columns.

In column A are the numbers that need to be separated.

In C would be the numbers that are inferior of the previous one and in D the one that are superior.

Thank you. Serge.

Excel Workbook
	A	B	C	D	E
1	1		1
2	18			18
3	6		6
4	3		3
5	5			5
6	12			12
7	10		10
8	27			27
9	36			36
10	44			44
11	21		21
12	1		1
13	7			7
14	15			15
15
Sheet1

 

[T. Valko]

I'm looking for a formula that would separate numbers in 2 columns.

In column A are the numbers that need to be separated.

In C would be the numbers that are inferior of the previous one and in D the one that are superior.

Thank you. Serge.

Excel Workbook
	A	B	C	D	E
1	1		1
2	18			18
3	6		6
4	3		3
5	5			5
6	12			12
7	10		10
8	27			27
9	36			36
10	44			44
11	21		21
12	1		1
13	7			7
14	15			15
15
Sheet1

What if the numbers are equal? Is that a possibility?

 

[mikerickson]

C1 would be =A1
in C2 put the formula =IF(A1<=A2, A2, "")
and in D2, =IF(A1<=A2, "", A2)

(This puts the = case in column C, if you want that in D, use < in the above)

 

[serge]

Yes I forgot to mention it Thank you, It would look like this :

Excel Workbook
	A	B	C	D	E
1	1		1
2	18			18
3	6		6
4	3		3
5	3		3
6	12			12
7	10		10
8	27			27
9	36			36
10	44			44
11	44			44
12	1		1
13	7			7
14	15			15
15
Sheet1

 

[serge]

Hi mike,

Thank you for the formula, I forgot to mention that sometime I have numbers that repeat themselves.

 

[mikerickson]

This will keep the duplicates in the same column, as indicated in the example

In C2
=IF($A2 < $A1, $A2, IF(AND($A1=$A2,C1<>""),$A2,""))
in D2
=IF($A1 < $A2, $A2, IF(AND($A1=$A2,D1<>""),$A2,""))

 

[serge]

Thank you Mike there is just a small problem, the duplicate show up in both column !!!

 

[mikerickson]

What is currently posted is working for me. Did you try before or after I edited the <>0 to <>"" ?

 

[serge]

I just copy and paste the 2 formulas in post # 6, and the duplicate show in both column ?

 

[mikerickson]

Do the formulas use <>0 or <>"" ?