Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
find sum in list of of numbers
You're right, Jim, the OP could reduce his solution space by
disregarding the numbers greater than his "target" number. In an accounting environment, however, debits and credits (positive as well as negative) may need to be considered- the negative numbers may react with the positive larger numbers to arrive at the correct solution. |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
find sum in list of of numbers
I do agree that Harlan's code looks good. I haven't tried to compile and run
it, but it looks like a good approach. Bill ------------------------------ Jim Thomlinson wrote: I am with you on the brute force requirement, but there are a couple of tricks to minimize the permiutations and combinations. By sorting the list of input values you can determine to stop testing certain combinations knowing that certain solutions can not be possible because they are going to be too large. That is where the code that I posted is very good. I had some other code that did almost exactly what you were suggesting but it was far slower. From what I have seen Harlan's code is hard to beat. That being said the list you are searching should be at most 25 or 30 entries. |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
find sum in list of of numbers
Use Google's group search: http://groups.google.com/advanced_group_search
, look in Groups *excel*, with all of the words Add up numbers, Author Harlan (yes, indeed, Harlan Grove) and you'll find a discussion and very advanced solutions about this subject -- Kind regards, Niek Otten "Dave O" wrote in message ups.com... You're right, Jim, the OP could reduce his solution space by disregarding the numbers greater than his "target" number. In an accounting environment, however, debits and credits (positive as well as negative) may need to be considered- the negative numbers may react with the positive larger numbers to arrive at the correct solution. |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
find sum in list of of numbers
Sorry, I missed that it had already been mentioned here
-- Kind regards, Niek Otten "Niek Otten" wrote in message ... Use Google's group search: http://groups.google.com/advanced_group_search , look in Groups *excel*, with all of the words Add up numbers, Author Harlan (yes, indeed, Harlan Grove) and you'll find a discussion and very advanced solutions about this subject -- Kind regards, Niek Otten "Dave O" wrote in message ups.com... You're right, Jim, the OP could reduce his solution space by disregarding the numbers greater than his "target" number. In an accounting environment, however, debits and credits (positive as well as negative) may need to be considered- the negative numbers may react with the positive larger numbers to arrive at the correct solution. |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
find sum in list of of numbers
That is not quite what the code does. What it does is it sorts the original
values lowest to highest. Negatives will obviously be the lowest values. When it is doing the combinations it moves in the direction of adding the next highest number. If the combination exceeds the target value then it abandons moving to the following next highest value because it obviously is not a possible solution. I am not sure that I explained that very well but sufice it to say that it works and it speeds up the execution by potentially a few orders of magnitude. -- HTH... Jim Thomlinson "Dave O" wrote: You're right, Jim, the OP could reduce his solution space by disregarding the numbers greater than his "target" number. In an accounting environment, however, debits and credits (positive as well as negative) may need to be considered- the negative numbers may react with the positive larger numbers to arrive at the correct solution. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
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 |