Share this content
0
1369

Excel query

Excel query

Hi

I have always contented myself with using Goal Seek, which has served me proud. I have been told that Solver add-in is much more powerful, and it *may* perhaps be the right tool for this job.  Never having used Solver before I have no idea whether it would help with this problem.  Certainly in this case Goal Seek is not up to the task.

Problem:

I have an employee payslip.  It is a really complicated one.  "Gross" is made up of about half a dozen entries,  Some of them are negatives that contra some of the positives.  There is about another half dozen categories of deductions, over and above the tax and employee's NI. And at the bottom there are figures for "taxable pay" and "NIC-able pay" for the period. The individual entries that build up to make the taxable and NIC-able pay are not separately identified, but I need to work out which count and which do not (they are not very helpfully labelled, with acronyms and jargon all over the shop). The possible permutations are large in number but ideal for a computer to work out by brute force.

Any suggestions as to how to automate this?  Let's start with whether Solver would do the job.

With kind regards

Clint Westwood

Replies

Please login or register to join the discussion.

avatar
07th Mar 2016 13:38

It could well work

as long as there are not too many possible solutions.

In outline:

Get all your payslip values in a column Say A1 :A15

Enter 1 in the column next to them i.e. in each of B1:B15

In the next column (C), multiply the first two columns together. =A1*B1, etc.

Create a 3 cell area, say D1 to D3

In D1, put the sum of column C =SUM(C1:C15)

In D2, put the figure you are looking for

In D3, calculate the difference. =D2-D1

Now into Solver.

The Objective is D3 and you want a value of 0.

You will be changing variable cells B1:B15

You have at least 3 constraints:

B1:B15 <=1

B1:B15>=0

B1:b15=integer

You may wish to add further constraints. For instance setting cells to 1 which you know must be contained in the answer (or else construct the solution so that these are included de facto and only the unknowns are tested).

Choose Simplex LP. You can probably ignore the other options.

Hit Solve and keep your fingers crossed. It doesn't take as much effort as it may sound.

 

Thanks (1)
By shurst
07th Mar 2016 13:51

Solver

Hi Clint

Solver might be able to help. To try and simplify the problem, we have a set of positive and negative values that need to add up to a particular sum. If we listed all the values out, and multiplied them each by 1 or 0, then we could compare the original sum with the calculated sum. We want the difference to be zero, so we could add a cell that subtracts one total from the other and ask solver to try and set that cell to zero as the Objective by changing each of the multiplying cells. We could constrain each of these to a binary value to give us our ones and zeros. Once done, the ones that are allocated 1 (TRUE) are ones that would be included in the calculation:

2341234435-901-90-671-67800-6001-600-523 -523   Difference: 

I've probably oversimplified the problem and I wouldn't claim to have tested the approach exhaustively, but it seemed to work for a simple example where there was an exact answer.

Kind regards

Simon

 

Thanks (1)
By shurst
07th Mar 2016 13:53

..two minds

similar approach

Thanks (1)
avatar
07th Mar 2016 15:52

Something not working for me

I tried it out wth Simon's exact data table, and it came up with "Solver could not find a feasible solution", and it entered zeroes throughout the binary column

:(

EDIT: WORKING NOW

:)

Solver does NOT like ROUND() functions in the solution fields. Even if the solution is expected not to require rounding.  Should have thought of that.  I am in the habit of ROUNDing everything in sight to prevent those E-15 errors creeping into calcs due to floating point constraints in the chips.

 

Thanks (0)
07th Mar 2016 21:02

Brute force

Clint,

I have a brute force solution to the "knapsack" problem (I think that's the nature of what you've described) that I developed in Excel a number of years ago. It tests all possible combinations of up to 25 individual items and compares the result to a required total. Maximum time - three and a half minutes. The time would double for each additional item (i.e. 26 items - 7 minutes, 27 items 14 minutes etc). Happy to share if other solutions don't work out for you. Without giving anything away, would you be able to list all the values available and any total amounts you'd like to substantiate, post it here and invite solutions?

Regards,

Ian 

Thanks (0)
By tom123
07th Mar 2016 20:09

I use these approaches

I use a solver template almost exactly as shown above,

My problem is usually how to allocate a customer receipt against the many different open invoices on the account.

The maths is the same.

I think if there are more than 10 items it really struggles though.

Thanks (0)
Share this content