LibreOffice 4.1 Solver example

While going through my accounting I found some discrepancies. So I needed to go through a long list of numbers and check whether they have been included in the totals I was checking.
Of course I would like not having to spend hours doing this, so I decided to find out how I could do this using LibreOffice Calc an save myself a lot of time. The time spent making the calculation is now about 1 second. (The time spent figuring this out... Let's just say hours....)

In my accounting software I have the ability to export to Excel. So I just had to export the numbers into Excel / LibreOffice Calc. This gave me a long list of numbers, which I had to check against a total sum. What I needed to know was which of these numbers have not been included into the total sum.

I figured out this solution after someone pointed me to LibreOficce Calc's Solver function and some trial end error.

First of all I had to setup the solver correctly. The Solver is found at Tools -> Solver from the main menu. The solver will then show up as a separate window. At the bottom left of this window you'll find an Options... -button. Click this button and make sure LibreOffice Linear Solver is selected as the solver engine.

Now we'll look at the spreadsheet we are using. This spreadsheet is just an example, but the logic behind it should be easy to grasp.

We have one total sum, and a list of numbers.
Which numbers are included in this total, and which numbers are missing?

Column A and B contains our desired result and the values that makes this result and some more values.

With only 6 values this is not a very big problem to calculate manually, but when we have 50 or more values; it becomes very tedious work to figure it out.

These numbers needs to be set "on or off" or we could say multiplied by 0 or 1.

So we'll set up our spreadsheet with formulas that can do this.

Column D is simply multiplying columns B by columns C.

At the bottom of column D we'll get the total for the values included. (Those multiplied by 1.)


When we set all values in column C to 0 we will notice that Used Values is 0.

Now we'll start the LibreOffice Solver and insert the Target Cell, Desired result, the Cells that needs  changing and the Limiting Conditions.

Cell D14 will change as the solver changes to 0 or 1 in cell range C8:C13.
Changes in the cell range C8:C13 makes Solver check if D14 has the same value as B2.
Remember that Solver is only allowed to use Binary values, (Zero or One according to our limiting condition.)
Have a look at the following figure to see the logic of the solver.

The solver needs to know some values to make the calculation.

After making solver "Solve" this problem we find that our desired result of 53.7 is comprised by the values 12.5+14.2+13.5+13.5
And we know that we are missing a total of 29.3 which is the numbers: 13.7+15.6.

Now I'll go back to accounting and try to find out where the 2 missing numbers have gone. Failing to deliever my annual report will make the IRS make an audit, and they are likely to figure this out, but I prefer to do it myself....