Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
Quote:
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing only positive or negative values | Excel Worksheet Functions | |||
Changing positive values to negative | New Users to Excel | |||
Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) | |||
... Count, <<< Positive Values minus Negative Values >>> ... | Excel Worksheet Functions | |||
switching values from positive to negative | Excel Worksheet Functions |