Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Compare Negative & Positive Numbers
How to compare values in a colum that has several data of positive and negative numbers, of which most of them cancel one another. What I am trying to do is to delete all the numbers that cancel each other and have obly numbers that are not cancel left in that column. Column A 123 -123 -325 325 123 from the example above, after I successfully delete all numbers that offsetting each other, I should have only $123 as a total in column A Please help. Thanks Chom -- chom ------------------------------------------------------------------------ chom's Profile: http://www.excelforum.com/member.php...o&userid=25665 View this thread: http://www.excelforum.com/showthread...hreadid=390810 |
#2
|
|||
|
|||
"chom"
wrote in message ... How to compare values in a colum that has several data of positive and negative numbers, of which most of them cancel one another. What I am trying to do is to delete all the numbers that cancel each other and have obly numbers that are not cancel left in that column. Column A 123 -123 -325 325 123 from the example above, after I successfully delete all numbers that offsetting each other, I should have only $123 as a total in column A Please help. Thanks Chom Hi Chom, See my reply to your other post. If that doesn't work, reply there. Multiple posts are bad form - stick with one thread and follow a discussion of suggestions there. HTH, Alan. |
#3
|
|||
|
|||
Just posted this response to you in your earlier post in ..worksheet.functions (As Alan says, please refrain from multi-posting ..) " .. Think this revised set-up should deliver what we're after .. Assume data is in Sheet1's col A, A1 down: 123 -123 -123 124 -124 124 etc Put in B1: =IF(A1="","",COUNTIF($A$1:A1,A1)) Put in C1, and array-enter the formula, i.e. press CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(B1="","",IF(ISNUMBER(MATCH(-A1&"_"&B1,$A$1:$A$100&"_"&$B$1:$B$100,0)),"" ,ROW())) Select B1:C1, fill down to say, C100, to cover the max expected data in col A Note: Adapt the ranges $A$1:$A$100, $B$1:$B$100 in the formula in C1 to suit. We can't use entire col references (e.g.: A:A, B:B) in the array formula for col C. In a new Sheet2 -------------- Put in A1: =IF(ISERROR(SMALL(Sheet1!C:C,ROWS($A$1:A1))),"",IN DEX(Sheet1!A:A,MATCH(SMALL (Sheet1!C:C,ROWS($A$1:A1)),Sheet1!C:C,0))) (Normal ENTER will do) Note that the entire formula above should be in one line. You would need to rectify the inevitable line breaks / wraps [especially for long formulas] after you directly copy paste the formula from the post into the cell / formula bar. Copy A1 down to A100 (cover the same range as done in Sheet1's cols B & C) Sheet will return the desired results neatly bunched at the top, viz. for the sample data above, you'd get: -123 124 (blank rows below) ... " -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "chom" wrote in message ... How to compare values in a colum that has several data of positive and negative numbers, of which most of them cancel one another. What I am trying to do is to delete all the numbers that cancel each other and have obly numbers that are not cancel left in that column. Column A 123 -123 -325 325 123 from the example above, after I successfully delete all numbers that offsetting each other, I should have only $123 as a total in column A Please help. Thanks Chom -- chom ------------------------------------------------------------------------ chom's Profile: http://www.excelforum.com/member.php...o&userid=25665 View this thread: http://www.excelforum.com/showthread...hreadid=390810 |
#4
|
|||
|
|||
Here's a link to a sample file with the implemented construct:
http://www.savefile.com/files/4522483 File: Compare Neg n Pos Nos_ChomKrusopon_wksht.xls -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FORMULA TO ADD POSITIVE AND NEGATIVE NUMBERS, REGARDLESS OF SIGN | Excel Discussion (Misc queries) | |||
simple query changing set of numbers from positive to negative | Excel Discussion (Misc queries) | |||
how do I rank negative and positive numbers? | Excel Worksheet Functions | |||
How can I change positive numbers to negative, i.e. change 50 to - | Excel Discussion (Misc queries) | |||
Negative numbers turn positive automatically on data entry | Excel Discussion (Misc queries) |