Offsetting that equals zero.

[Ltuckson]

Is there a way to create a formula that identifies one negative and one positive amount (offset) amount with in column G.? For example, 0.13 cents offset each other as well as other lines highlighted below. Is there a formula that can pull this information instead of me doing it manually?

 

[6StringJazzer]

What do you mean by "identifies"? You can use conditional formatting to do the highlighting that you've done manually. Use this rule:

=COUNTIF(G:G,-G1)>0

However, just based on your example, I do not see 100.13 or (100.00) which would offset the highlighted values.

 

[Ltuckson]

Hello, thank you for your response. Yes, I am currently using conditional formatting in another column to pull the amount together as seen in row G. I have more than 9 thousand lines that I have to sort and going down column G to identify the off sent line by line can be cumbersome.


The offset amounts that I am referring to are listed below.

0.13 cents off set.

100.13 off set.

 

[6StringJazzer]

Your screen shot does not show row/column headings so I could not tell which column is G. I made an educated guess.

Your problem is a lot more complicated because you are not looking for one-to-one matches. You are looking for a set of multiple cells with numbers that add up to a negative number in another cell. I'm not sure that can be done with formulas.

I'm not sure it can be done at all. How would you even do this on paper? You are going to get false matches. Suppose to you are trying to see if a series of payments completely pays of an amount owed. Let's the amount owed is ($100.00). You might have payments of $80, $20, $50, $40, $50, $60, and $40. There are several combinations that will give you $100 but you can't tell which combination is the one that matches that amount owed.

 

[Joe4]

Your screen shot does not show row/column headings so I could not tell which column is G. I made an educated guess.

Your problem is a lot more complicated because you are not looking for one-to-one matches. You are looking for a set of multiple cells with numbers that add up to a negative number in another cell. I'm not sure that can be done with formulas.

I'm not sure it can be done at all. How would you even do this on paper? You are going to get false matches. Suppose to you are trying to see if a series of payments completely pays of an amount owed. Let's the amount owed is ($100.00). You might have payments of $80, $20, $50, $40, $50, $60, and $40. There are several combinations that will give you $100 but you can't tell which combination is the one that matches that amount owed.

On top of that, the amount of combinations you are going to need to check is going to be huge!
For every positive value, you have to check every combination of negative values.
That is, unless there is some way to limit the records that need to be checked (i.e. you only check records that have a certain value in another field equal).

And then if you do use some numbers in combination to get what you want, you need some way of "marking" them, so they aren't used again.

And what if you have exactly three $100 values, but only two -$100 values? How do you know which $100 value is not matched with the negative values?

So unless you can involve another field in the comparison (like whatever is in that "Posting..." column to the left of your values), I do not see any good way to do what you want to do.
There will be almost an infinite number of possibilities.

 

[6StringJazzer]

almost an infinite number of possibilities.

To check all combinations of numbers picked from 10 numbers, you have to check 1,023 possibilities. The number of possibilities grows very fast. For 100 numbers it is a number that mathematicians call "freaking huge" and would take years to calculate in Excel.