Thanks:  0
Likes:  0

# Thread: Searching for an offsetting value in a range

1. 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?

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

3. 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.

4. 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...

5. 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 ]

6. 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

7. 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.

8. 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,

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•