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

[techypk]

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:

=COUNTIF($F$4:$F$14,-1*F4)=1

(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

=COUNTIF

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

($F$4:$F$14,

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

,-1*F4)

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

=1

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.