 • 544K Members
• 4,487 Online
• 648K Conversations

Highlighted

# Excel Solver finding min value of the difference between budget and actual expenses

Hi there,

I have 38 receipts from a client funded travel, the budget was \$6000, and actual spending is \$6777.49, therefore I need to find a combination of receipts that the total amount is as close to \$6000 as possible to recover from the client and put the rest in company expenses.

I've tried Solver to solve this problem, but the solution is different every time I ran the Solver. There is once it came up with the perfect combination that total amount is exactly \$6000 while other times not. How do I set up the Solver case to make sure I have the best result every time?

Budget Cell B1=6000

Actual Cell B2 =SUMIF(Table1[Recoverable],1,Table1[Amount])

Difference Cell B3 = B1-B2

Table1[Recoverable] = 0 or 1, 1 means the expense will be recovered

Solver parameters:

Set Objective: \$B\$3 (Min)

Changing cells: \$E\$6:\$E\$43 (which is the Table1[Recoverable])

Constrains:

\$B\$2<=\$B\$1

\$B\$2>=0

\$B\$3>=0

\$E\$6:\$E\$43 = binary

and I used Evolutionary solve method.

I've also attached file with detailed data.

If any one can provide possible solution or let me know how do I improve the constrains is much appreciated.

Thank you.

Related Conversations