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 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
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
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.
6 Replies
If with conditional formatting may apply the rule as
with formula
=SUM(--(C2=-$C$2:$C$18))- 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?
ThanksIt 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)))
