Results 1 to 9 of 9

20011015, 18:02 #1reickmannGuest
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

20011015, 19:19 #2
 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.

20011015, 19:48 #3reickmannGuest
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.

20011015, 20:11 #4
 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 Excelrelated 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?

20011016, 08:36 #5
 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

20011016, 20:14 #6
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,480
 Thanks
 32
 Thanked 63 Times in 59 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.

20011212, 02:48 #7
 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 searchfor 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

20011212, 20:04 #8
 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.

20011213, 15:44 #9
 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