find sum in list of of numbers

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.

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.

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!

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.

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.

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.

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.

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.

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.

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