pair negative and positive numbers

[chomkay]

data
-300
300
-300
2
1
__
297 Totals

From examples above, I need a formula that would pair negative and positive numbers together, so I can delete them from my worksheet. Once I delete the pairs, my ending total should still be 297.

The real worksheet I am working with has over 3,000 lines. Can anyone help me please?

Thanks Chom

 

[sous2817]

hello and welcome to the board! If you're willing to use a helper column, you could do something like:

=ABS(A1) and copy down, then sort by the helper column....assuming your data is in column A.


*edit* maybe I don't understand what you're trying to do.....

 

[Jonmo1]

Welcome to the board..

Can you show before AND after?
Which lines got removed from your example, and how did you arrive at 297?
Did you mean -297

If by pair you mean -300 and -300 as a pair, then that leaves
1, 2, 300 = 303

If by pair you mean -300 and 300 as a pair, then that leaves
1, 2, -300 = -297

 

[chomkay]

First of all thank you for helping

from the sample data

data
-300
300
-300
2
1
__
297 Totals

What I would like to see is 300 pair with -300 and thus leaves -300+2+1 to give a total of 297. The worksheet I am working with have over 100 of negative and positive numbers that need to be paired up. But what important is if I have let say four of +300 and five of -300 (negative $300), only the ones that can pair with each other (negative pairs with positive) are to be identified or deleted. Thus in this example at the end total is -$300 (-300*5 -=-$1500; +$300*4 =+$1200; toal is -$300)

Hope you can help me. Thanks again.

 

[sous2817]

I'm a bit confused...if you sum the total data set, regardless of deleting pairs, you should get your intended results, correct?

 

[Scott Huish]

leaves -300+2+1 to give a total of 297

this doesn't = 297, it equals -297.

In order for it to equal 297, you would have to reverse the signs of all the values, which you can use the following formula to get the result you want. A formula can not actually remove the values, you would need VBA for that.

Excel Workbook
	A	B	C
1	-300		297
2	300
3	-300
4	2
5	1
Sheet1

 

[Scott Huish]

or you could just negate the sum = -SUM(A1:A5)

 

[xenou]

To go with sous2187's post, using ABS() with a second helper column:

In Cell A2: Beginning of value list
In Cell B2: =ABS(A2)
In Cell C2: =(OR(A2=-A3,A2=-A1))
Then sort on Column B and Column C.

Note however that the first value (in C2) is spurious since it is the first row. This doesn't strike me as very elegant - you'd have to attend to the item at the top of the list since it won't be a good match. After that you should have a lot of matches, though - all the trues in column C.


| -300 | 0300 | false (*)
| -300 | 0300 | true
| 0300 | 0300 | true
| 0300 | 0300 | true
| -300 | 0300 | true
| -250 | 0250 | true
| 0250 | 0250 | true
| 0008 | 0008 | false
| 0002 | 0002 | false
| 0001 | 0001 | false

In finance, this is a common problem - it's not always the total sum but the unmatched items that are of interest (such as bank transactions where some items aren't accounted for).