Searching for an offsetting value in a range

[Moose54]

I'm doing an account reconciliation where I have a bunch of negatives and positives in a column and the sum of the column should be zero. Each negative doesn't always have a matching positive. For example, -1000 could be offset by +499 and +501. If there are many numbers in the column, you can see where it would be time consuming to try to find a match. Is there a way for Excel to find the offsets for me?

 

[JohnG]

Im not an accountant so you have to forgive me but is there going to be an exact corresponding number?

 

[Moose54]

Ideally, since the sum of all of the numbers should equal zero, each negative number will be zeroed by a combination of positives and vice versa. Often this isn't true, as is happening this morning. I'm looking for a quick way to determine which numbers don't have an offset.

 

[Chris Davison]

Hey Moose,

I was born to reconcile, alas.

using Excel's =combin function to see the number of possible combinations of x numbers from y :

any 2 from 100 = 4,950 possibilities
any 3 from 100 = 161,700 possibilities
any 4 from 100 = 3,921,225 possibilities
any 5 from 100 = 75,287,520 possibilities
any 6 from 100 = 1,192,052,400 possibilities

you can appreciate the complexity of the problem if your data range adds up to 0 with 6 of the numbers from your list !

I use VBA code provided from this board to do this - it checks any combinations from 1 to 6 variables in any sized range - give me a shout and I'll email it to you

I have to point out though, I did not write the VBA code, it was provided by some much cleverer bod from this board (I can't remember who though) some months ago.

Don't forget, a million combinations means a million calculations : if your 0 is made up of 6 numbers in a range of 200 numbers, you are talking 82 billion calculations Excel has to perform : maybe a few hours worth of the hourglass on centrestage...

 

[Scott R]

I think the short answer is no.
If I can't come up with a systematic process by hand, how would I expect Excel to.
Good luck
This message was edited by Scott R on 2002-02-22 17:24

 

[Chris Davison]

On 2002-02-22 17:22, Scott R wrote:
I think the short answer is no.
If I can't come up with a systematic process by hand, how would I expect Excel to.
Good luck
This message was edited by Scott R on 2002-02-22 17:24

Scott, am I reading this right ?!

I couldn't come up with a systematic process by hand for calculating the cosine of the cubed root of a 12-digit integer, but I'd sure as dammit *expect* Excel to be able to do it !

cheers mate
Chris

 

[Scott R]

Chris-
Moose54 is "looking for which #'s don't have an offset" because I'm assuming Moose54 wants to take action on the out of balance entries.

If the sample data is +1000, +1000, -499, and -501, which +1000 can we eliminate? There's not enough information. A mathematical approach would blindly eliminate one of the +1000's and Moose54 could possibly be left investigating the wrong issue.

 

[Chris Davison]

On 2002-02-25 10:11, Scott R wrote:
Chris-
Moose54 is "looking for which #'s don't have an offset" because I'm assuming Moose54 wants to take action on the out of balance entries.

If the sample data is +1000, +1000, -499, and -501, which +1000 can we eliminate? There's not enough information. A mathematical approach would blindly eliminate one of the +1000's and Moose54 could possibly be left investigating the wrong issue.

Hey Scott,

Yep, I see your point.

The question though, purely at face value, is what offsets to what in a single column. So if there's two +1000s isn't it mathematically irrelevant which one we pick, or proceed to eliminate ?

Like I said though, I do reconcile in about 50% of my worktime, so can fully appreciate the dangers of just matching off like-amounts without taking into account other criteria like narratives or transaction numbers. The original question isn't clear on whether these may also be a factor, so I didn't just assume they would be.

You are correct though, given two or more options, which do you pick, systematically and manually ? (It's the donkey and two bales of hay) so how would we expect excel to do it ?

Interesting