Forum Discussion
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 wor...
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.
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)))