Finding Sum of X in a column (Excel 97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote