Suppose a customer has 60 unpaid invoices. The customer sends me a check
paying a certain number of the invoices but doesn't indicate how many
invoices or which ones. Let's assume I cannot contact the customer for more
information. Is there a way to put all 60 amounts in Excel along with the
amount of the check and have Excel try all possible combinations of those
invoices to determine which combination, if any, matches the amount of the
check?

Sounds like you need a better relationship with your customer..

--
Don Guillett
SalesAid Software

"Homeboy" wrote in message
...
Suppose a customer has 60 unpaid invoices. The customer sends me a check
paying a certain number of the invoices but doesn't indicate how many
invoices or which ones. Let's assume I cannot contact the customer for
more
information. Is there a way to put all 60 amounts in Excel along with the
amount of the check and have Excel try all possible combinations of those
invoices to determine which combination, if any, matches the amount of the
check?

This actual situation is not exactly as I described it. I described it this
way to make what I am looking for clear.

"Don Guillett" wrote:

Sounds like you need a better relationship with your customer..

On Sun, 24 Apr 2005 08:54:03 -0700, "Homeboy"
wrote:

Suppose a customer has 60 unpaid invoices. The customer sends me a check
paying a certain number of the invoices but doesn't indicate how many
invoices or which ones. Let's assume I cannot contact the customer for more
information. Is there a way to put all 60 amounts in Excel along with the
amount of the check and have Excel try all possible combinations of those
invoices to determine which combination, if any, matches the amount of the
check?

Not easily and there would be a lot of potential ambiguity.

You should probably have a policy to apply to the oldest first, although some
might argue to apply to the newest first, and make more money on late charges
:-).


--ron

Basically the only thing I know how to do in Excel is use basic arithmetic. I
don't know the formulas or functions at all. But if you have 60 numbers and
we call them n1, n2, n3 etc. couldn't you add n1+n2 and test the result
against the target number, then if it was wrong add n1+n2+n3 and test, etc.
until you added all 60 numbers. Then after that, start with n2, adding n2+n3,
then n2+n3+n4, etc. the last in this series being n2+n3...+n60+n1. I don't
know if this makes sense or would exhaust all the possibilities. And, in any
case, I don't know how to write the formula.

Another idea. Contine to add random combinations of the 60 numbers, testing
against the target figure each time, then let the formula run all night.
Wouldn't all combinations likely be exhausted by morning? But again, I don't
know how to write this?


"Ron Rosenfeld" wrote:

Not easily and there would be a lot of potential ambiguity.

On Sun, 24 Apr 2005 11:13:01 -0700, "Homeboy"
wrote:

Basically the only thing I know how to do in Excel is use basic arithmetic. I
don't know the formulas or functions at all. But if you have 60 numbers and
we call them n1, n2, n3 etc. couldn't you add n1+n2 and test the result
against the target number, then if it was wrong add n1+n2+n3 and test, etc.
until you added all 60 numbers. Then after that, start with n2, adding n2+n3,
then n2+n3+n4, etc. the last in this series being n2+n3...+n60+n1. I don't
know if this makes sense or would exhaust all the possibilities. And, in any
case, I don't know how to write the formula.

Another idea. Contine to add random combinations of the 60 numbers, testing
against the target figure each time, then let the formula run all night.
Wouldn't all combinations likely be exhausted by morning? But again, I don't
know how to write this?


"Ron Rosenfeld" wrote:

Not easily and there would be a lot of potential ambiguity.

Well, let's say your customer sends you a check for $112, and you have the
following outstanding invoices:

Invoice Amt
1 $15
2 $10
3 $12
4 $5
5 $7
6 $6
7 $15
8 $15
9 $15
10 $9
11 $13
12 $7
13 $6
14 $6
15 $9
16 $6

What rule will you use to decide whether to apply the payment to

1,3..11
2,4..10

or some other combination that adds up to $112?

And what if your customer is not applying the same rule, because he disagrees
with one of the invoices, but hasn't gotten around to informing you yet?

If you can develop clear rules to resolve the inevitable possible ambiguities,
then you should be able to develop a suitable algorithm. But it seems a lot
simpler to just apply the payments to the oldest invoices first, absent
specific instructions from your customer.


--ron

I wasn't sure what you meant by ambiguity, but now I see your point. As a
practial real world solution, I agree with everything you've said. BUT the
actual situation isn't exactly as I described it. I described it this way
simply to make what sort of formula/function/algorithm I am looking for
clear. In fact, either of the "ambigious" solutions in your example would
work for my situation. Plus the numbers are mostly (but not all) unique.

"Ron Rosenfeld" wrote:

Well, let's say your customer sends you a check for $112, and you have the
following outstanding invoices:

Invoice Amt
1 $15
2 $10
3 $12
4 $5
5 $7
6 $6
7 $15
8 $15
9 $15
10 $9
11 $13
12 $7
13 $6
14 $6
15 $9
16 $6

What rule will you use to decide whether to apply the payment to

1,3..11
2,4..10

or some other combination that adds up to $112?

And what if your customer is not applying the same rule, because he disagrees
with one of the invoices, but hasn't gotten around to informing you yet?

If you can develop clear rules to resolve the inevitable possible ambiguities,
then you should be able to develop a suitable algorithm. But it seems a lot
simpler to just apply the payments to the oldest invoices first, absent
specific instructions from your customer.


--ron

On Sun, 24 Apr 2005 14:13:01 -0700, "Homeboy"
wrote:

I wasn't sure what you meant by ambiguity, but now I see your point. As a
practial real world solution, I agree with everything you've said. BUT the
actual situation isn't exactly as I described it. I described it this way
simply to make what sort of formula/function/algorithm I am looking for
clear. In fact, either of the "ambigious" solutions in your example would
work for my situation. Plus the numbers are mostly (but not all) unique.

If ANY solution is acceptable, and there is no need to resolve ambiguities
(which can exist even if all the numbers are unique), you could use Excel's
Solver to find a solution.

For example:
In A1:A60 enter your values.
In B1:B60 enter the number '1'
In C1 enter the formula: =SUMPRODUCT(A1:A60,B1:B60)

Tools/Solver
Set Target Cell: D1
Equal to: Value of: 112 (or whatever)
By Changing Cells:
B1:B60

Subject to the Constraints:
Add
Cell Reference: B1:B60
bin (in the dropdown box)
OK
Solve

Depending on the numbers, you may have to select Options and change the number
of iterations.

After you do the above, the cells adjacent to where B1:B60=1 will sum up to
your payment value.

If you don't see the Solver item on the Tools menu, you may have to add it at
the Tools/Addins and select Solver Addin.



--ron