Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Searching for an offsetting value in a range

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    SRC
    Posts
    165
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


    :: Pharma Z - Family drugstore ::

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    WA state
    Posts
    412
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    WA state
    Posts
    412
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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