Searching for an offsetting value in a range

Moose54

New Member
Joined
Feb 21, 2002
Messages
2
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?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Im not an accountant so you have to forgive me but is there going to be an exact corresponding number?
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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
:)
 
Upvote 0
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.
 
Upvote 0
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
:)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top