Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
Have you tried using Excel Solver
First a little prep work.... A1:A1 (your list of values) B1:B10 (leave blank) C1: =A1*B1 (copy that fomula down through C10 C11: =SUM(C1:C10) Now to use Solver.... ToolsSolver Set Cell: C11 Equal to the Value of: 1173.76 By Changing Cells: B1:B10 Subject to the Constraints.... (click the add button and constrain B1:B10 to Binary) Click [OK] Click [Solve] Excel will toggle cells B1:B10 between 1 and 0 until it comes up with a combination that sums to 1,173.76 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Dave O" wrote: The answer is: $725.90 $240.16 $207.70 I've written a program that applies a brute force approach to the task- it checks every possible combination of the "pool" of numbers to arrive at the target total. The brute force idea works for comparatively small pools, but since the number of possible combinations doubles with each additional pool member the processing time increases commensurately. One poster to this newsgroup wanted to process a list of 100 numbers, which amounts to 1,267,650,600,228,230,000,000,000,000,000 possible combinations and would require the resources of a major government (or maybe just the NSA) to process. How many of these do you have? I don't mind doing a few for you. |
#2
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
I do like your Solver approach -- I hadn't thought of that. Given that this is
an Accounting problem though, how would one get Solver to identify multiple solutions to the problem when they exist? If you're trying to match invoices you'd like to know that you're matching them correctly -- not just *possibly* correctly. Which requires a person to stare at *all* the various possible solutions and decide which one is most likely given some knowledge of the customers involved and what they've ordered in the past, etc. For example, take the list of 10 values that Dza provided and use them all twice to make 20 entries. Now there are 8 valid solutions, but Solver only seems to find one and stops. Personally, I think you need VBA for this problem but I'm open to education... Bill ---------------------------------- Ron Coderre wrote: Have you tried using Excel Solver First a little prep work.... A1:A1 (your list of values) B1:B10 (leave blank) C1: =A1*B1 (copy that fomula down through C10 C11: =SUM(C1:C10) Now to use Solver.... ToolsSolver Set Cell: C11 Equal to the Value of: 1173.76 By Changing Cells: B1:B10 Subject to the Constraints.... (click the add button and constrain B1:B10 to Binary) Click [OK] Click [Solve] Excel will toggle cells B1:B10 between 1 and 0 until it comes up with a combination that sums to 1,173.76 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Dave O" wrote: The answer is: $725.90 $240.16 $207.70 I've written a program that applies a brute force approach to the task- it checks every possible combination of the "pool" of numbers to arrive at the target total. The brute force idea works for comparatively small pools, but since the number of possible combinations doubles with each additional pool member the processing time increases commensurately. One poster to this newsgroup wanted to process a list of 100 numbers, which amounts to 1,267,650,600,228,230,000,000,000,000,000 possible combinations and would require the resources of a major government (or maybe just the NSA) to process. How many of these do you have? I don't mind doing a few for you. |
#3
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
Bill makes a good point- I wrote my original program to find "the"
solution and then stop, but one day on a whim I allowed it to cycle through the rest of the combinations and found multiple answers to the problem. Since then re-wrote my program to show all possible solutions, and frequently find more than one correct answer to the problem. Still haven't heard from the OP yet! |
#4
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
Solver isn't a panacea....It's just a nice shortcut for relatively simple
situations without having to find or write code. However, if solver finds one acceptable solution....couldn't we just create another "flag" field to prevent the same value from being used more than once? Regarding professional accounting/financial environments, I would hope that proper internal controls would prevent the situation where a large number of invoices/checks/whatever would have to be matched (trial and error) against an amount. Of course there's always the customer who sends a massive check paying some unknown combination of invoices. Consequently, for those instances, a phone call to the payee should clear up the confusion definitively. You wouldn't want to just guess, right? If a large, multi-solution, iteratave approach cannot be avoided though....You're right, a vba program would be the way to go. *********** Regards, Ron XL2002, WinXP-Pro "Bill Martin" wrote: I do like your Solver approach -- I hadn't thought of that. Given that this is an Accounting problem though, how would one get Solver to identify multiple solutions to the problem when they exist? If you're trying to match invoices you'd like to know that you're matching them correctly -- not just *possibly* correctly. Which requires a person to stare at *all* the various possible solutions and decide which one is most likely given some knowledge of the customers involved and what they've ordered in the past, etc. For example, take the list of 10 values that Dza provided and use them all twice to make 20 entries. Now there are 8 valid solutions, but Solver only seems to find one and stops. Personally, I think you need VBA for this problem but I'm open to education... Bill ---------------------------------- Ron Coderre wrote: Have you tried using Excel Solver First a little prep work.... A1:A1 (your list of values) B1:B10 (leave blank) C1: =A1*B1 (copy that fomula down through C10 C11: =SUM(C1:C10) Now to use Solver.... ToolsSolver Set Cell: C11 Equal to the Value of: 1173.76 By Changing Cells: B1:B10 Subject to the Constraints.... (click the add button and constrain B1:B10 to Binary) Click [OK] Click [Solve] Excel will toggle cells B1:B10 between 1 and 0 until it comes up with a combination that sums to 1,173.76 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Dave O" wrote: The answer is: $725.90 $240.16 $207.70 I've written a program that applies a brute force approach to the task- it checks every possible combination of the "pool" of numbers to arrive at the target total. The brute force idea works for comparatively small pools, but since the number of possible combinations doubles with each additional pool member the processing time increases commensurately. One poster to this newsgroup wanted to process a list of 100 numbers, which amounts to 1,267,650,600,228,230,000,000,000,000,000 possible combinations and would require the resources of a major government (or maybe just the NSA) to process. How many of these do you have? I don't mind doing a few for you. |
#5
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
They do presumably have controls. And I don't think people routinely match
invoices like this. However, contols fail, data gets lost, people do stupid things. And them somehow you've got to clean up the mess. Bill ------------------------ Ron Coderre wrote: ...snip... Regarding professional accounting/financial environments, I would hope that proper internal controls would prevent the situation where a large number of invoices/checks/whatever would have to be matched (trial and error) against an amount. |
#6
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
Ron Coderre wrote...
Solver isn't a panacea....It's just a nice shortcut for relatively simple situations without having to find or write code. However, if solver finds one acceptable solution....couldn't we just create another "flag" field to prevent the same value from being used more than once? Perhaps. How would you do that since it's not one value but one combination of values (OK, a vector of 1s and 0s that could be considered a single vector value in {0,1}^N) that'd need to be excluded. As I see it, you'd need to use a kludge like SUMPRODUCT of the vector of 1s and 0s against 2^(ROW(INDIRECT("1:"&N))-1) to produce unique identifiers for each solution, save them in a list, then use a COUNTIF = 0 expression on that list with criteria equal to the current SUMPRODUCT value. And you'd need to automate storing the idenifiers for previous solutions, so VBA is unavoidable. Regarding professional accounting/financial environments, I would hope that proper internal controls would prevent the situation where a large number of invoices/checks/whatever would have to be matched (trial and error) against an amount. Of course there's always the customer who sends a massive check paying some unknown combination of invoices. Consequently, for those instances, a phone call to the payee should clear up the confusion definitively. You wouldn't want to just guess, right? .... In the real world, reconcilliation of different data sources that should produce the same results is an unfortunate recurring problem. And there's often no one to call to get a quick, simple answer. If a large, multi-solution, iteratave approach cannot be avoided though....You're right, a vba program would be the way to go. Yup. |
#7
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
When a customer sends you a check for $1,173.76 with no backup then you match
it the best you can. Been there and done that. They will need all possible solutions because they will want to match to the oldest stuff first. This kind of code is also very handy for doing year end working papers where you need to reconcile the ending amount of a Balance Sheet account. Usually you can match off the vast majority of the debits and credits but very often you end up with a few entries that (because of reversels, reclassifications and just plain weirdness) don't match easily. That is another place where this kind of thing thing is handy. -- HTH... Jim Thomlinson "Ron Coderre" wrote: Solver isn't a panacea....It's just a nice shortcut for relatively simple situations without having to find or write code. However, if solver finds one acceptable solution....couldn't we just create another "flag" field to prevent the same value from being used more than once? Regarding professional accounting/financial environments, I would hope that proper internal controls would prevent the situation where a large number of invoices/checks/whatever would have to be matched (trial and error) against an amount. Of course there's always the customer who sends a massive check paying some unknown combination of invoices. Consequently, for those instances, a phone call to the payee should clear up the confusion definitively. You wouldn't want to just guess, right? If a large, multi-solution, iteratave approach cannot be avoided though....You're right, a vba program would be the way to go. *********** Regards, Ron XL2002, WinXP-Pro "Bill Martin" wrote: I do like your Solver approach -- I hadn't thought of that. Given that this is an Accounting problem though, how would one get Solver to identify multiple solutions to the problem when they exist? If you're trying to match invoices you'd like to know that you're matching them correctly -- not just *possibly* correctly. Which requires a person to stare at *all* the various possible solutions and decide which one is most likely given some knowledge of the customers involved and what they've ordered in the past, etc. For example, take the list of 10 values that Dza provided and use them all twice to make 20 entries. Now there are 8 valid solutions, but Solver only seems to find one and stops. Personally, I think you need VBA for this problem but I'm open to education... Bill ---------------------------------- Ron Coderre wrote: Have you tried using Excel Solver First a little prep work.... A1:A1 (your list of values) B1:B10 (leave blank) C1: =A1*B1 (copy that fomula down through C10 C11: =SUM(C1:C10) Now to use Solver.... ToolsSolver Set Cell: C11 Equal to the Value of: 1173.76 By Changing Cells: B1:B10 Subject to the Constraints.... (click the add button and constrain B1:B10 to Binary) Click [OK] Click [Solve] Excel will toggle cells B1:B10 between 1 and 0 until it comes up with a combination that sums to 1,173.76 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Dave O" wrote: The answer is: $725.90 $240.16 $207.70 I've written a program that applies a brute force approach to the task- it checks every possible combination of the "pool" of numbers to arrive at the target total. The brute force idea works for comparatively small pools, but since the number of possible combinations doubles with each additional pool member the processing time increases commensurately. One poster to this newsgroup wanted to process a list of 100 numbers, which amounts to 1,267,650,600,228,230,000,000,000,000,000 possible combinations and would require the resources of a major government (or maybe just the NSA) to process. How many of these do you have? I don't mind doing a few for you. |
#8
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
My appologies for not documenting where I had made modifications to your
code... As a professional courtesy I should have done that and I will endevour to make the necessary notations at my end. Thanks for sharing your work and once again I appoligize. As for long variable names I have always favoured them purely from a readability standpoint. I have debugged too much code written by others that was almost impossible to follow. Not to mention it keeps things straight in my head when I am writing it. Probably more the latter than the former... :-) -- HTH... Jim Thomlinson "Harlan Grove" wrote: Ron Coderre wrote... Solver isn't a panacea....It's just a nice shortcut for relatively simple situations without having to find or write code. However, if solver finds one acceptable solution....couldn't we just create another "flag" field to prevent the same value from being used more than once? Perhaps. How would you do that since it's not one value but one combination of values (OK, a vector of 1s and 0s that could be considered a single vector value in {0,1}^N) that'd need to be excluded. As I see it, you'd need to use a kludge like SUMPRODUCT of the vector of 1s and 0s against 2^(ROW(INDIRECT("1:"&N))-1) to produce unique identifiers for each solution, save them in a list, then use a COUNTIF = 0 expression on that list with criteria equal to the current SUMPRODUCT value. And you'd need to automate storing the idenifiers for previous solutions, so VBA is unavoidable. Regarding professional accounting/financial environments, I would hope that proper internal controls would prevent the situation where a large number of invoices/checks/whatever would have to be matched (trial and error) against an amount. Of course there's always the customer who sends a massive check paying some unknown combination of invoices. Consequently, for those instances, a phone call to the payee should clear up the confusion definitively. You wouldn't want to just guess, right? .... In the real world, reconcilliation of different data sources that should produce the same results is an unfortunate recurring problem. And there's often no one to call to get a quick, simple answer. If a large, multi-solution, iteratave approach cannot be avoided though....You're right, a vba program would be the way to go. Yup. |
#9
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
Just for the record, Harlan, I wholeheartedly agree with everything you et
al have mentioned regarding cross-matching details to totals. These scenarios rarely have an elegant solution....having been there and done that during the 12 years I spent as a financial/accounting manager. I never once considered Solver more than a shot-in-the-dark approach to try first, just in case. The basic problem is huge! After all, it took someone with your intellectual horsepower to finally come up with code that has a reasonable shot at dealing with the issue. Unfortunately, the best defense is to try to do everything feasible to avoid the situation and hope the worst case never happens. Regards, Ron "Harlan Grove" wrote in message ups.com... Ron Coderre wrote... Solver isn't a panacea....It's just a nice shortcut for relatively simple situations without having to find or write code. However, if solver finds one acceptable solution....couldn't we just create another "flag" field to prevent the same value from being used more than once? Perhaps. How would you do that since it's not one value but one combination of values (OK, a vector of 1s and 0s that could be considered a single vector value in {0,1}^N) that'd need to be excluded. As I see it, you'd need to use a kludge like SUMPRODUCT of the vector of 1s and 0s against 2^(ROW(INDIRECT("1:"&N))-1) to produce unique identifiers for each solution, save them in a list, then use a COUNTIF = 0 expression on that list with criteria equal to the current SUMPRODUCT value. And you'd need to automate storing the idenifiers for previous solutions, so VBA is unavoidable. Regarding professional accounting/financial environments, I would hope that proper internal controls would prevent the situation where a large number of invoices/checks/whatever would have to be matched (trial and error) against an amount. Of course there's always the customer who sends a massive check paying some unknown combination of invoices. Consequently, for those instances, a phone call to the payee should clear up the confusion definitively. You wouldn't want to just guess, right? ... In the real world, reconcilliation of different data sources that should produce the same results is an unfortunate recurring problem. And there's often no one to call to get a quick, simple answer. If a large, multi-solution, iteratave approach cannot be avoided though....You're right, a vba program would be the way to go. Yup. |
#10
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
Wow, thank you so very much for all the responses. I've gone blind
looking at these amounts over the past few days so I really appreciate the responses. First off, amongst some of my accountant co workers I know alot about excel but the VBE stuff is a bit shady for me. I've done some macros but the code Mr. Thomlinson provided is a bit out of my league. I'd need a more thorough explanation as to how to set up the code for me to make that work. I pasted it into VBE and turned on those options but I don't know what to do thereafter. I also want to try out the solver option but I need to have it installed. I'll have to try it at work tomorrow morning, man I really didn't expect all these responses this quickly, guess it helps posting in more than one group. For the record it seems that a few of the invoice amounts were missing from my list hence the huge problem with matching them up to payments. Even with that i still have some matching issues. I also forgot to mention that an invoice amount once used when matched to a payment cannot be used again. The combinations of course must match up to the payments to the penny. Again I really would like to try the VBE FindSum program but I'll need some newbie introduction into how to write a code like that and then how to use it. Thanks again! Dza the accountant |
#11
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
Look he
http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Kind regards, Niek Otten wrote in message oups.com... Wow, thank you so very much for all the responses. I've gone blind looking at these amounts over the past few days so I really appreciate the responses. First off, amongst some of my accountant co workers I know alot about excel but the VBE stuff is a bit shady for me. I've done some macros but the code Mr. Thomlinson provided is a bit out of my league. I'd need a more thorough explanation as to how to set up the code for me to make that work. I pasted it into VBE and turned on those options but I don't know what to do thereafter. I also want to try out the solver option but I need to have it installed. I'll have to try it at work tomorrow morning, man I really didn't expect all these responses this quickly, guess it helps posting in more than one group. For the record it seems that a few of the invoice amounts were missing from my list hence the huge problem with matching them up to payments. Even with that i still have some matching issues. I also forgot to mention that an invoice amount once used when matched to a payment cannot be used again. The combinations of course must match up to the payments to the penny. Again I really would like to try the VBE FindSum program but I'll need some newbie introduction into how to write a code like that and then how to use it. Thanks again! Dza the accountant |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
find sum in list of of numbers | Excel Discussion (Misc queries) | |||
find next number in list | Excel Discussion (Misc queries) | |||
Find in a list | Excel Discussion (Misc queries) | |||
Find the combination of numbers that when added equal a reqired total?? | Excel Worksheet Functions | |||
How do I filter a number list by numbers to the right of the decim | Excel Worksheet Functions |