Can Excel Choose from a List What Makes a Particular Total?

[Guest]

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?

 

[Don Guillett]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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

 

[Guest]

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]

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?

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

 

[Guest]

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]

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

 

[Guest]

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, you de man. You response was incredibly complete including being
proactive about the fact that the solver add-in might not have been
installed. (It wasn't.) This was exactly what I needed. I made one change
though. The solver thingie seem to want C1 rather than D1.

Thanks again.

 

[Ron Rosenfeld]

The solver thingie seem to want C1 rather than D1.

Huh? I don't understand that.

You should be able to define any unused cell on the sheet to be the target, and
place the formula in that cell.

But I'm glad you've got a solution you can live with. Thanks for the feedback.


--ron

 

[Guest]

Huh? I don't understand that.

You should be able to define any unused cell on the sheet to be the target, and
place the formula in that cell.

But I'm glad you've got a solution you can live with. Thanks for the feedback.

In your original solution you said to put the formula in C1 (which I did),
but 2 lines later in the solver instructions you said to use D1. I did that
and got an error message from solver. I tried a few things and putting C1
rather than D1 worked. I assumed that D1 was a clerical error and the only
reason I mentioned it was in case others were reading this and needed a
similar solution. I actually hesitated to point it out because you have been
so kind in helping me with this that I didn't want to seem ungrateful.

 

[Ron Rosenfeld]

In your original solution you said to put the formula in C1 (which I did),
but 2 lines later in the solver instructions you said to use D1. I did that
and got an error message from solver. I tried a few things and putting C1
rather than D1 worked. I assumed that D1 was a clerical error and the only
reason I mentioned it was in case others were reading this and needed a
similar solution. I actually hesitated to point it out because you have been
so kind in helping me with this that I didn't want to seem ungrateful.

Ah. Now I understand. It's just a matter of having the formula in the same
cell as is specified in the Target section of Solver. And yes, one of those
location entries was a typo.


--ron