Accountant needing help with a formula that deletes journal values that net to 0

[MJ1998]

Hi all,

I hope everyone is well.

I'm an acountant by trade, so you'd never assume i'm an excel rookie, however, that is the case unfortunately.

Does anyone know a formula that can spot all values in a row that net to 0. For E.G, an entry of 100 matches with another entry of -100.

Or another example would be, one entry that is 200, but that is netted off by two seperate journals of -100.

Many thanks.

 

[SanjayGMusafir]

Does anyone know a formula that can spot all values in a row that net to 0

Assuming Column B contains values, use following formula to test such values

=COUNTIFS(B:B,-B1)

Then you can highlight Column B using conditional format where Column C has value more than 0

Try it and revert

 

[Joe4]

Assuming Column B contains values, use following formula to test such values

=COUNTIFS(B:B,-B1)

Then you can highlight Column B using conditional format where Column C has value more than 0

Try it and revert

That will not handle this situation they mentioned:

Or another example would be, one entry that is 200, but that is netted off by two seperate journals of -100.

There are some big problems here, mostly in that you can have one-to-many and many-to-many relationships.

For example, what if you had a 100 value, but three -100 values?
How do you know which of the three -100 values match up to the 100 value?

Or what if you had values like this:
200
100
-50
-50
-100
-100
-25
-25
-25
-25

There are a whole bunch of different combinations of the negative values that can add up to 100 and 200, so how do you know which ones go there?

The point is, without some other gudiance/infortmation/matching fields to use in this problem, I think trying to do it strictly based on values is going to be very difficult.

 

[MJ1998]

Hi Sanjay,

I hope you are well. Thank you for the help.

This works perfectly with cases that the numbers are exactly equal to each other with a positive and negative and net to 0.

However, in cases where a combination of negative values make up a different value that nets to 0, Is there a formula for this scenario?

 

[MJ1998]

That will not handle this situation they mentioned:


There are some big problems here, mostly in that you can have one-to-many and many-to-many relationships.

For example, what if you had a 100 value, but three -100 values?
How do you know which of the three -100 values match up to the 100 value?

Or what if you had values like this:
200
100
-50
-50
-100
-100
-25
-25
-25
-25

There are a whole bunch of different combinations of the negative values that can add up to 100 and 200, so how do you know which ones go there?

The point is, without some other gudiance/infortmation/matching fields to use in this problem, I think trying to do it strictly based on values is going to be very difficult.

Hi Joe,

Exactly so.

With you on this one, a pain in the a** in analysing data to be honest but just thought there may be some wizaard out there that may know an excel hack in how to solve this problem.

 

[Joe4]

Hi Joe,

Exactly so.

With you on this one, a pain in the a** in analysing data to be honest but just thought there may be some wizaard out there that may know an excel hack in how to solve this problem.

The thing to understand is Excel is not magic -- it is a very literal program.
It can do most things where you can clearly define the rules. However, if you cannot even define clear rules (in plain English) on how it should operate, how you can program it?
You cannot program/build that which you cannot clearly define the rules.

 

[SanjayGMusafir]

However, in cases where a combination of negative values make up a different value that nets to 0, Is there a formula for this scenario?

As @Joe4 mentioned, such a scenario is difficult to address. It becomes a complicated scenario.