Generic selectors
Exact matches only
Search in title
Search in content
Search Posts
Search in pages
Search Products
Filter by Categories
B-BBEE
Blog
Company Secretarial
Featured
General Business
General Interest
Investing in Property
Marketing
Personal Growth
Personal Wealth
Tax
Trusts and Estate Planning

For my sins, I’m the one who has to allocate debtor payments as we are sticklers for payment and it’s worth running this at partner level. But so often a debtor makes a lump payment against an account with lots of open items and without giving us a remittance advice. So which invoices did they pay?

I love a challenge and I tried all sorts of techniques to determine the combination of invoices that fits the total paid, but I fell flat.

Then I Googled “How can I find a subset of numbers that add up to a particular total” and Bingo!

This is what came up http://superuser.com/questions/204925/excel-find-a-subset-of-numbers-that-add-to-a-given-total 

Now, when you try it, it seems a bit complicated, but it is well worth the effort. The writer missed out just two little things that gave me trouble.

1) When entering the formula in the Objective (Target) cell, you type  (using his example) =SUM(A1:A100*B1:B100) then to get the curly brackets you must press <Ctrl><Shift><Enter>. You can’t type them in as he suggests. This sets up an Array formula, something that I still haven’t fully got my head around in Excel, but it amounts to a shorthand way of writing =SUM(A1*B1,A2*B2,A3*B3,………..A100*B100)

2) To add the constraints, you first click on Add, Then in the cell reference select the range that he indicates (or your equivalent), then from the <= dropdown select bin and click OK

Everything else is explained very well (the conditional formatting is a nice touch) and you can set up a master solver for use over and over again.

If you get stuck, give me a call on 011 656 2945 or drop me an email and I’ll send you the master that I set up for my use.

Should you wish to make an appointment, please feel free to visit derek’s diary  and book a time that suits you.

 

Need help with your Investments?

Contact us today or set up a free meeting with our CEO, Derek Springett, to discuss your options. We have been offering expert financial advice and business services since 1971. You can also visit our online store to see our list of financial services.