Allocating Values

Hello, I have a problem that I would like some help on.
In column "A" I have a list of numbers (Invoices). In Column "B" I have one large number (Lump Sum Payment).
I need to figure out how to split up the number in column "B" so that I can identify what in column "A" has not been paid for.
Is there some sort of an algorithm that can be used to determine all possible combinations that would get as close as possible to be equal to the lump sum?
If it is not exact, I would like to see the difference.

In the attached spreadsheet I have the problem that I am trying to solve on the left, and the expected result on the right.
When adding all the "Paid" numbers, they come out to be 20 cents short of the total sum. (the spreadsheet has a typo)

Thank you

This is actually my first venture into the Excel add-in "Solver" so there may be a more efficient approach but this does seem to work for the provided example.

• Add a sum of col-A in A11
• Add col-E to be 1 if this invoice is paid and 0 otherwise. Populate initially with zeros
• Add col-F with the following formula in F3 and copied down:
  
  Formula:

  Please Login or Register  to view this content.

• This simply gives the invoice value if col-E is 1 (paid) and zero otherwise.
• Add a sum of col-F in F11.
• In G11:
  
  Formula:

  Please Login or Register  to view this content.

Now in "Solver"

• Set objective: $G$11 to "Min"
• Constraints:
  $E$3 = integer
  $E$3 <= 1
• Repeat for E4:E10
• Choose solving method: GRG Nonlinear
• Click Solve

You will find that your expected results have been matched.

See the attached workbook.

If you need to load Solver, see: https://support.office.com/en-gb/art...c-e24772f078ca

Perhaps you could spend some portion of the 4E22 years to persuade your clients to say what invoices they are paying

Seriously, I think you will find José Augusto's solution much more efficient than mine. For 8 invoices his solution is fast enough to not be stopwatch measurable, my "solver" solution takes around 5 seconds.

Thanks anyway for the feedback and rep.

Hi GeoffW

For constraints = 1 and integer it's sometimes easier to use the binary constraint i.e

b_solv_setup.jpg

Alf

Ps Not sure if uploaded jpeg shows, have had problems with this before. If so just replace constraints with line "$E$3:$E$10=binary"