HOW DO I SEPERATE POSITIVE AND NEGATIVE VALUES FROM A COLUMN

F

FRANK

I HAVE A COLUMN OF FIGURES I WISH TO MAKE TWO MORE COLUMNS ONE SHOWING
POSITIVE VALUES ONE SHOWING NEGATIVE VALUES
 
J

Jacob Skaria

If you are looking for something like the below

In B1 and copy down as required
=IF(A1>0,A1,"")

In C1 and copy down as required
=IF(A1<0,A1,"")

Col A Col B Col C
-1 -1
1 1
2 2
-3 -3
-4 -4
-5 -5
3 3
3 3

If this post helps click Yes
 
M

Ms-Exl-Learner

Assume that you are having data in A Column like this.

Column A
66
-22
88
-44

For getting the Positive Numbers from A Column use this formula in B1 cell.
=IF(A1>=0,A1,"")

For getting the Negative Numbers from A Column use this formula in C1 cell.
=IF(A1<0,A1,"")

Just copy the B1 and C1 cells and apply it for the remaining cells
accordingly.

If this post helps, Click Yes!
 
L

L. Howard Kittle

For the negative numbers in column B.

=IF(LEFT(A1,1)="-",A1,"")

And the positive numbers in column C.

=IF(LEFT(A1,1)="-","",A1)

You may want to Select > Copy > Edit > Paste special > Values > OK. Returns
the results to numbers instead of formulas.

HTH
Regards,
Howard
 
T

T. Valko

Try this...

Assume the range of numbers is A2:A20. A1 is the column header. Let's assume
the column header is Nums.

In E1:F1 enter the column header Nums
In E2 enter <0
In F2 enter >=0

Select the range A1:A20
Goto the menu Data>Filter>Advanced filter
Select: Copy to a new location
The List range should already be filled in
Click in the Criteria range then select E1:E2
Click in the Copy to range then select E3
Click OK

That will extract all the negative numbers. Repeat the process for the
poistive numbers.

After you're done you can delete the stuff in E1:F2.

Also, Excel automatically creates defined names when you do the filter
operation. These names are no longer needed. You can delete them also.

Goto the menu Insert>Name>Define
Select the name Criteria>Delete
Select the name Extract>Delete
OK
 
P

Peo Sjoblom

Use autofilter, first filter custom and select greater than or equal 0,
select and copy and paste into positive column, change to less than 0 and
repeat for the negative values

--


Regards,


Peo Sjoblom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top