Results 1 to 9 of 9
  1. #1
    reickmann
    Guest

    Finding Sum of X in a column (Excel 97)

    Reconciliation:
    For my job I do a lot of reconciling of invoices. For example if we invoice a customer for 504.02 and they pay only 420.00 I need to find out what they didn

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Finding Sum of X in a column (Excel 97)

    The attached file includes a utility that should help. As it stands there's a limit of 5 amounts, i.e., it will look for the target amount by combining up to 5 amounts.
    Attached Files Attached Files

  3. #3
    reickmann
    Guest

    Re: Finding Sum of X in a column (Excel 97)

    The code looks great, and almost exactly what I need, however the attached file, is missing a subroutine/function called round() //Called by Function GetRound()

    Any idea where I can get that from?

    P.S. Did you write this program yourself? Or is there another source that I can look for similar code at. Ala CPAN.org for perl code.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Finding Sum of X in a column (Excel 97)

    Yes, I did write the program myself. Don't know of any particular source to find similar code, although there are various Excel-related sites out there (John Walkenbach, Stephen Bullen, Chip Pearson) that you may find useful.

    Regarding the Round() function, that must be something that's available in Excel 2000 but not in Excel 97. You could try replacing it with Application.WorksheetFunction.Round, although I have a feeling that may only work in Excel 2000, also. Perhaps somebody else on the board has a solution/suggestion?

  5. #5
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Finding Sum of X in a column (Excel 97)

    Hmm, you've said that maybe Application.WorksheetFunction.Round may only work in Excel 2000; well, in Excel 5 and Excel 95 the way to call this was Application.Round, so maybe that will work in Excel 97.

    Glenn Bumford

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Re: Finding Sum of X in a column (Excel 97)

    Colin's solution is very good. I got curious and generated the combinations for 7 items taken 1, 2, etc. at a time (using another application where it was easier than trying to do it in Excel), pasted them into a sheet, and did some playing around with it.
    Just thought you might be curious, so I attached the workbook.
    Attached Files Attached Files

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Finding Sum of X in a column (Excel 97)

    Colin,

    Believe it or not, I'm just looking at your find utility from Oct (yes of 2001).

    It's a great tool - something I could have used a while ago. So I thought I'd give it a look.

    It worked great when I searched for 80 - finding the 2 cells of 40 and 44.02.

    But I tried a few other things with mixed results (setting degree of accuracy to within 10):
    - find 210 (with the 2 cells 200 and 220 in the range): no matches
    - incremented the find to 211, 212...214: also no matches
    - find 217: the utility found 220 but the search-for part of the resulting user form showed I was searching for 220 (whereas my seach on 80 above did show 80 in the search for). In fact, the input form changed my total to the number rounded before the search began. This might explain the 2nd item.

    Is this the intended behavior. I would think searching for 214 within an accuracy of 10 should result in 220 being found.

    Similarly, I tried searching for 210 to an accuracy of 100. It found 200 and 220 as single amounts and 200+40 and 200+44.02. But did not find 220+...

    I'm wondering if the rounding is part of the issue? Or maybe it's just the interpretation I give to "degree of accuracy".

    Fred

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Finding Sum of X in a column (Excel 97)

    For better or worse, it *IS* actually behaving as intended. As you guessed it's really down to the interpretation of 'degree of accuracy'. What I was anticipating was that you would be searching a list of dollar amounts, such as in a check register. Suppose you're looking for amounts totalling 128 dollars, give or take a few cents - i.e., you're not sure of the exact amount. Setting 128 as the target and 1 as the degree of accuracy will find any amount between 127.50 and 128.49. If that doesn't find anything, you could broaden the search by making the target 130 and the degree of accuracy 10. This will find amounts between 125.00 and 134.99.
    Given the above, that's why the target amount changes sometimes. If you're searching with the degree of accuracy set at 10, target amounts should be divisible by 10.
    Hope you follow what I mean. If not, let me know.

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Finding Sum of X in a column (Excel 97)

    Colin,

    Thanks for the reply. I kind of thought the answer would be what it was. I do intend to use your utility if the occasion arises again (which I think it would).

    So perhaps one small suggestion (I'm at home and don't have the utility in front of me): playback the selected parameters (what was entered to search for, the degree of accuracy,...) AND the search range that the utility will look for. I still think that a "Degree of accuracy of 10" should be interpreted as plus or minus 10 from the target but that's just me.

    And thanks for sharing.

    Fred

Posting Permissions

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