Highlighting numbers with corresponding negatives. (Tut - Please add to it)

Hi everyone. Just a quick how-to on the subject. Im no expert, just sharing my two cents so it might help somebody out. If you have a better way of doing this, by all means,
please link below.

Now, I work in accounting, so sometimes I need to find out if there are any numbers in my data that net to Zero (mostly these are reversals that I do not need in my sheets).

So here, we have an example set of data with negatives and positives; that I created for who knows what reason. (You can download it below)


Now, what we want is to highlight the numbers that have a net value of Zero.

(1) Select your values:



(2) Go to conditional formatting under Home, and select "New Rule":



(3) Select Formula and Type in the formula that follows, and select what you'd like to do with it. I merely wanted it to highlight the cells in question to red:

Please Login or Register  to view this content.

(4) Press OK Twice (i.e. once on both windows) and the cells that net to zero should take on the formatting you desired:



FORMULA ANALYSIS

The Function

Please Login or Register  to view this content.

We have used the COUNTIF function. This is one of excel's statistical functions and it will count the number of cells based on criteria that you specify.

The First argument

Please Login or Register  to view this content.

This part of our formula tells excel to look in a strictly limited range. You could, however change the range to "F:F" and excel will then look in the entire F range.

The Middle argument

Please Login or Register  to view this content.

This part of our formula will multiply -1 with the value in the specified cell, i.e F4. (The last part is incremented to go with the corresponding cell i.e F5, F6, F7 etc). This is done to match ABSOLUTE (For example, if you have 30 and -30.4, these values will NOT be selected) opposites.

The Ending argument

Please Login or Register  to view this content.

This part of the formula will help us format duplicate values / returning "TRUE". The opposite (=0) would format values that arent duplicates / return "FALSE"

Additional Information

Forum Guru TMS has pointed out the following:

if you have two positive values and one negative, for example, 117, 117, -117, it won't highlight the negative number. And,if you have two or more matching pairs, it won't highlight any of the values.

So you might want to keep that in mind.

-----------------------

Thats about it. I do understand that this is not exhaustive, but it gets the job done. If anyone has better explanations for what the different arguments in the formula do, please post them below and I will append them with credit to you.

Cheers.

^ That is a very valid point, Pete. Replaced all PNGs with JPEGs.
Cheers for your reply!

Nice idea, well explained. However, being a little picky, if you have two positive values and one negative, for example, 117, 117, -117, it won't highlight the negative number. And,if you have two or more matching pairs, it won't highlight any of the values.

^ Thankyou for your valuable input, TMS. Is there a work around for that?

Not tested, but I suspect you could get away with two separate conditions. The one you already have, which says there is one positive value and one negative value. And a second one that just counts identical values, plus or minus. You could use colour green where there is a single match and amber where there are multiple matches ... up to the user to decide what to do with them.

I think if you put in a conditional formatting that highlights duplicates, it should overcome that, no?