Solved: Finding opposite entries.

[Marcster]

Hello people,

I have a list of amounts like:
124.69
108.83
59.4
120.52
41.49
41.09
19.8
70.51
168.3
-124.69
-108.83
In column I.

Is there a formula (to be placed in column J) which returns false
(or anything else) if there is no opposite entry amount in column I?.

For example, the first amount 124.69 has a minus -124.69
in the list, so this should return TRUE.
The amount 59.4 doesn't have a minus amount so should FALSE.

Any ideas please?,

Thanks,

Marcster.

[Bob Phillips]

=ISNUMBER(MATCH(-A1,I:I,0))

[Marcster]

Hi XLD,

That doesn't work.

I have data in columns A:I
But it's just the amounts in column I i'm interested in.
Ive tried =ISNUMBER(MATCH(-K1,I:I,0))

Marcster.

[Bob Phillips]

Hi XLD,

That doesn't work.

I have data in columns A:I
But it's just the amounts in column I i'm interested in.
Ive tried =ISNUMBER(MATCH(-K1,I:I,0))

Marcster.

Are you trying to identify any number in A:I that has a negative value. If so, I would use conditional formatting with a formula of

=COUNTIF(A$1:$H400,-A1)=0

[tpoynton]

what about =ISNUMBER(IF(I1>0,MATCH(-I1,I:I,0))) ? that way only positive #'s are evaluated...

[Marcster]

What I have is a list of data 1,192 rows covering columns A:I
In column I i have various amounts, postive and negative amounts.
Most of them will cancel each other out.
?124.69 and -?124.69
but there will be some with no negative or positive entry.
A ?59.40 doesn't have a -?59.40 in column I.
A -?41.59 doesn't have a ?41.59 in column I.
I need to find all these. A formula or validation in column J?.

Thanks for your help,

Marcster.

[tpoynton]

then I believe that XLD's original formula basically works:

=ISNUMBER(MATCH(-I1,I:I,0))

this returns false if the opposite of the # is not in column I...at least it does in the attachment!

[Marcster]

That works .

Don't know what I did earlier .

Thanks,

Marcster.