SOLVED

Symmetrical values

%3CLINGO-SUB%20id%3D%22lingo-sub-2054718%22%20slang%3D%22en-US%22%3ESymmetrical%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2054718%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3EI%20would%20like%20to%20know%20if%20there%20is%20a%20way%2C%20through%20formulas%20or%20conditional%20formatting%20to%20identify%20symmetrical%20values.%20For%20instance%2C%201%20and%20-1%2C%20100%20and%20-100.%3CBR%20%2F%3ECalculate%20the%20absolute%20value%20won't%20work%20because%20there%20is%20a%20risk%20to%20identify%20and%20remove%20repeated%20values%2C%20for%20instance%20if%20I%20have%20three%20cells%20with%205%20but%20only%20one%20cell%20with%20-5.%3CBR%20%2F%3EThanks%20and%20regards%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2054718%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2055027%22%20slang%3D%22en-US%22%3ERe%3A%20Symmetrical%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2055027%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F926863%22%20target%3D%22_blank%22%3E%40Inesmateus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20sort%20of%20formulas%20that%20might%20help%20are%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20COUNTIFS(values%2C%20values)%0Aand%20compare%20with%0A%3D%20COUNTIFS(values%2C-values)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIf%20you%20use%20MS365%20then%20you%20can%20filter%20the%20numbers%20and%20compare%20the%20count%20of%20positives%20and%20negatives%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20COUNTIFS(values%2C%20UNIQUE(ABS(values)))%0Aand%0A%3D%20COUNTIFS(values%2C-UNIQUE(ABS(values)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2056160%22%20slang%3D%22en-US%22%3ERe%3A%20Symmetrical%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2056160%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F926863%22%20target%3D%22_blank%22%3E%40Inesmateus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20with%20conditional%20formatting%20may%20apply%20the%20rule%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20632px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246069iD9774C2ED7552DA7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(--(C2%3D-%24C%242%3A%24C%2418))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2065688%22%20slang%3D%22en-US%22%3ERe%3A%20Symmetrical%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2065688%22%20slang%3D%22en-US%22%3EThank%20you%20for%20the%20solution.%3CBR%20%2F%3EHowever%20it%20gets%20a%20limitation%3A%20when%20the%20database%20has%20multiples%20entries%20with%20the%20same%20positive%20value%20(as%20per%20attachment)%20it%20automatically%20highlight%20all%20the%20entries%20and%20I%20just%20need%20to%20highlight%20t%20one%20of%20them%20in%20order%20to%20clear%20the%20negative%20one.%3CBR%20%2F%3EDo%20you%20have%20any%20workaround%3F%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2065841%22%20slang%3D%22en-US%22%3ERe%3A%20Symmetrical%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2065841%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F926863%22%20target%3D%22_blank%22%3E%40Inesmateus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(--(C2%3D-%24C%242%3A%24C%2418))*IF(C2%26gt%3B0%2CCOUNTIF(%24C%242%3A%24C2%2CC2)%3D1%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewhich%20highlight%20only%20FIRST%20met%20positive%20number%20and%20ALL%20symmetric%20negative%20numbers%20for%20each%20pair.%20If%20only%20first%20negative%20is%20needed%2C%20when%20instead%20of%20IF%20use%20only%20COUYNTIF%20within%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
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

@Inesmateus 

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)))

@Inesmateus 

If with conditional formatting may apply the rule as

image.png

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?
Thanks
best response confirmed by Inesmateus (New Contributor)
Solution

@Inesmateus 

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 first workaround works perfectly.
Thank you very much.
Best regards

@Inesmateus , you are welcome