Comparing negative and positive values

Microsoft Office excel 2007 home & stude...
July 19, 2010 at 22:01:20
Specs: Windows XP
Please help. I have a large spreadsheet about 1500 rows. This is how the amount column looks like
123
-123
554
-333
-554
145
-145
234
My question is how can I match the numbers that look the same, except one is a positive number, and the other is a negative number ( ex:123 and -123) Once these numbers are identified I want to delete both of them and at the end my total will be left with only numbers that can't pair with other numbers.

Your help in this matter is greatly appreciated.

Chom


See More: Comparing negative and positive values

Report •


#1
July 20, 2010 at 06:14:43
Hi,

The Excel function you want is ABS()

To compare two numbers in cells A1 and A2 irrespective of sign, use this formula:
=IF(ABS(A1)=ABS(A2),"Equal","Not equal")

If you want help on the rest of the issue, some more information would help.
Are there ever more than one match, say:

123
-123
-123

If so how are these handled.
Do you want your list of unmatched values in a different location, or do you want them in the same place.

Is this a one time or occasional process where it is OK to go through a few steps to get the results,
or
Is this a frequent process that would benefit from automation with a macro (with it's associated overhead of entering code, and managing it if circumstances change)

Regards


Report •

#2
July 20, 2010 at 08:51:43
Your help is greatly appreciated, but I think I didn't make it clear on my questions.

Here's the sample of my worksheet:
Column A
123
-123
554
-333
-554
145
-145
234

Added all numbers above give me a total of - 99

I need a formula that goes look for numbers that are the same irrespective of sign so I can total this column to give me -99. When I use your function, it doesn't give me the result that I am looking for. Please help me further with this problem. Your help is greatly appreciated


Report •

#3
July 20, 2010 at 10:43:50
Hi,

Your response is very confusing.

You say that your column of numbers comes to -99, which is correct.
You then say I need a formula that goes look for numbers that are the same irrespective of sign so I can total this column to give me -99

Well, as the column totals -99 anyway, what is the point of finding 'matching' numbers such as 123 and -123

If you add 123 to -123 the result is zero, so there is no point in matching them or removing them before creating the total.

If you values are in cells A2 to A9,
then enter this formula in any cell:
=sum(A2:A9)
It returns -99

Regards


Report •

Related Solutions

#4
November 24, 2010 at 21:21:41
Hi Humar,

I am after a similar macro....

I have a sheet of data, looks like the following (although i have sorted it to date range and usually its totally out of order.) - the sub job and description vary too.


Sub Job Task Date Description Qty Billable Cost

050 0 05/11/10 E166 (1.00) AUD (106.50) (78.00)
050 0 05/11/10 E166 1.00 AUD 106.50 78.00


I would like a macro that matches the negative values to the identical positive values - (like the data above) and delete them, as they cancel each other out...

If you could help, that would be wonderful!

Thanks,
Sam


Report •

#5
November 25, 2010 at 01:43:44
Please repost your data after reading the How To referenced below. That should make it easier for us the read.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Ask Question