Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can Excel Choose from a List What Makes a Particular Total?
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? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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.. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
"Ron Rosenfeld" wrote:
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. |
#10
|
|||
|
|||
On Sun, 24 Apr 2005 20:46:01 -0700, "Homeboy"
wrote: 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 |
#11
|
|||
|
|||
"Ron Rosenfeld" wrote:
On Sun, 24 Apr 2005 20:46:01 -0700, "Homeboy" wrote: 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. 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. |
#12
|
|||
|
|||
On Mon, 25 Apr 2005 09:23:06 -0700, "Homeboy"
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel, how can I print a long list on 1 page in multiple colum. | Excel Discussion (Misc queries) | |||
Mailing list in Excel | Excel Discussion (Misc queries) | |||
how can I count distinct names in an excel list? | Excel Discussion (Misc queries) | |||
How do I put a list of names and e-mail addresses in excel so tha. | Excel Discussion (Misc queries) | |||
How do I import a Windows Explorer list into Excel? | Excel Discussion (Misc queries) |