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 |
"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. |
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 |
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 ---- |
All times are GMT +1. The time now is 06:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com