 SOLVED

# Symmetrical values

Hello,
I would like to know if there is a way, through formulas or conditional formatting to identify symmetrical values. For instance, 1 and -1, 100 and -100.
Calculate the absolute value won't work because there is a risk to identify and remove repeated values, for instance if I have three cells with 5 but only one cell with -5.
Thanks and regards
6 Replies

# Re: Symmetrical values

The sort of formulas that might help are

``````= COUNTIFS(values, values)
and compare with
= COUNTIFS(values,-values)``````

If you use MS365 then you can filter the numbers and compare the count of positives and negatives

``````= COUNTIFS(values, UNIQUE(ABS(values)))
and
= COUNTIFS(values,-UNIQUE(ABS(values)))``````

# Re: Symmetrical values

If with conditional formatting may apply the rule as with formula

``=SUM(--(C2=-\$C\$2:\$C\$18))``

# Re: Symmetrical values

Thank you for the solution.
However it gets a limitation: when the database has multiples entries with the same positive value (as per attachment) it automatically highlight all the entries and I just need to highlight t one of them in order to clear the negative one.
Do you have any workaround?
Thanks
best response confirmed by Inesmateus (New Contributor)
Solution

# Re: Symmetrical values

It could be

``=SUM(--(C2=-\$C\$2:\$C\$18))*IF(C2>0,COUNTIF(\$C\$2:\$C2,C2)=1,1)``

which highlight only FIRST met positive number and ALL symmetric negative numbers for each pair. If only first negative is needed, when instead of IF use only COUYNTIF within it.

# Re: Symmetrical values

The first workaround works perfectly.
Thank you very much.
Best regards

# Re: Symmetrical values

@Inesmateus , you are welcome