Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 19th 09, 03:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 170
Default HOW DO I SEPERATE POSITIVE AND NEGATIVE VALUES FROM A COLUMN

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

  #2   Report Post  
Old November 19th 09, 04:08 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,520
Default HOW DO I SEPERATE POSITIVE AND NEGATIVE VALUES FROM A COLUMN

If you are looking for something like the below

In B1 and copy down as required
=IF(A10,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
---------------
Jacob Skaria


"FRANK" wrote:

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

  #3   Report Post  
Old November 19th 09, 04:12 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 506
Default HOW DO I SEPERATE POSITIVE AND NEGATIVE VALUES FROM A COLUMN

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!

--------------------
(Ms-Exl-Learner)
--------------------



"FRANK" wrote:

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

  #4   Report Post  
Old November 19th 09, 04:23 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 698
Default HOW DO I SEPERATE POSITIVE AND NEGATIVE VALUES FROM A COLUMN

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

"FRANK" wrote in message
...
I HAVE A COLUMN OF FIGURES I WISH TO MAKE TWO MORE COLUMNS ONE SHOWING
POSITIVE VALUES ONE SHOWING NEGATIVE VALUES



  #5   Report Post  
Old November 19th 09, 05:13 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default HOW DO I SEPERATE POSITIVE AND NEGATIVE VALUES FROM A COLUMN

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 DataFilterAdvanced 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 InsertNameDefine
Select the name CriteriaDelete
Select the name ExtractDelete
OK

--
Biff
Microsoft Excel MVP


"FRANK" wrote in message
...
I HAVE A COLUMN OF FIGURES I WISH TO MAKE TWO MORE COLUMNS ONE SHOWING
POSITIVE VALUES ONE SHOWING NEGATIVE VALUES





  #6   Report Post  
Old November 19th 09, 05:17 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 136
Default HOW DO I SEPERATE POSITIVE AND NEGATIVE VALUES FROM A COLUMN

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


"FRANK" wrote in message
...
I HAVE A COLUMN OF FIGURES I WISH TO MAKE TWO MORE COLUMNS ONE SHOWING
POSITIVE VALUES ONE SHOWING NEGATIVE VALUES



  #7   Report Post  
Old June 5th 16, 05:56 AM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2016
Posts: 1
Talking

Quote:
Originally Posted by FRANK View Post
I HAVE A COLUMN OF FIGURES I WISH TO MAKE TWO MORE COLUMNS ONE SHOWING
POSITIVE VALUES ONE SHOWING NEGATIVE VALUES
If you have Kutools, it is very simple. And actually you may be able to do this with an excel function also.
But with Kutools, it is like this:

Copy your column of figures into an extra column, so that you have 2 columns with exactly the same figures there.

Now all you have to do is remove all the negative numbers from the first column, and all the positive numbers from the second column, and your result is 2 columns, one with negs, one with positives.

So, with the first column, select the column contents fully and then open Kutools, and pick Select tab in the drop down, then pick Select Specific Cells.
Now in Selection Type, click the radio button next to Cell, then GREATER THAN or Equal to 0 (zero). So you are selecting all the cells that have a number in them which is greater than or equal to 0, meaning positive numbers.

Kutools will select all of the positive numbers, so when this is done, simply click Delete, and it will delete all the positive numbers from this list.

Then repeat the process in the other column, only this time you will choose, "LESS THAN or Equal to 0 (zero). And the result will be that all the negative numbers are selected, ready for you to delete. Delete them.

Now you are left with 2 columns, one is positive, one is negative, and you have retained your row positions for each one.

The above process may be possible using a filter in excel, without the need for Kutools, but I am not sure how.

Cheers!
  #8   Report Post  
Old January 13th 21, 08:31 AM
Junior Member
 
First recorded activity by ExcelBanter: Dec 2020
Posts: 27
Default

Separate Positive And Negative Numbers With Formula In Excel

Separate positive numbers from the list first.

Select a blank cell and type this formula =IF($A1=0,$A1,"") (A1 is the cell in your list), press Enter button and drag fill handle to fill range you want, you can see only positive values are separated into the column.

Then type this formula =IF($A1<0,$A1,"") into another column cell, and press Enter button, and drag fill handle to fill range you want. Now you can see the positive and negative values are separated into two columns.

I hope this is helpful!
Admin
O365CloudExperts


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
summing only positive or negative values Daniel Collison Excel Worksheet Functions 1 October 2nd 08 07:47 PM
Changing positive values to negative jay New Users to Excel 2 July 7th 07 01:34 AM
Formula to make Negative Values Positive & Positive Values Negative? mustard Excel Discussion (Misc queries) 4 September 26th 05 10:05 PM
... Count, <<< Positive Values minus Negative Values >>> ... Dr. Darrell Excel Worksheet Functions 4 September 8th 05 01:36 PM
switching values from positive to negative lob Excel Worksheet Functions 5 February 18th 05 11:11 PM


All times are GMT +1. The time now is 02:47 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017