find all employees whose total salaries equal a given amount

[Guest]

Hello,

I have a large data file in 2 columns:
1st column = Employee ID
2nd Column = Salary

I want to find combinations of salaries whose total equals a certain amount
and identify those employees. Please any suggestions on how should I proceed?
What formula to use?

Thanks for your assistance.

al

 

[Peo Sjoblom]

You need the solver add-in for this

"put the data set in let's say A2:A8, in B2:B8 put a set of ones {1,1,1 etc}
in the adjacent cells
in C2 put 8, in D2 put

=SUMPRODUCT(A2:A7,B2:B7)


select D2 and do tools>solver, set target cell $D$2 (should come up
automatically if selected)


Equal to a Value of 8, by changing cells $B$2:$B$7, click add under Subject
to the constraints of:
in Cell reference put


$B$2:$B$7


from dropdown select Bin, click OK and click Solve, Keep solver solution
and look at the table


2 1
4 0
5 0
6 1
9 0
13 0


there you can see that 4 ones have been replaced by zeros and the adjacent
cells to the 2 ones total 8 "



adapt to fit


--


Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

Thanks Peo,
Please let me clarify the question. You have the following

EE Name Salary
Peter 89
Paul 41
John 76
Richard 59
Santiago 10
Earl 52
Carl 22

I want to find the employees whose total salaries = 51.
Because the dataset here is not huge, I see that those employees are Paul &
Santiago. I am wondering how can I have the answer by using a formula in the
case of a large data set. Will your suggestion work in this case?

Thanks again.

al

 

[Peo Sjoblom]

Yes, I just did a quick test suing your example, I put your example in A1:B2
with the salaries in B2:B8, in C2:C8 I put 1, in D2 I put

=SUMPRODUCT(B2:B8,C2:C8)

set target cell $D$2, equal to value of 51, click add and select $C$2:$C$8,
then from dropdown binary, then OK and solve, the result looked like

Names Salaries
Peter 89 0 51
Paul 41 1
John 76 0
Richard 59 0
Santiago 10 1
Earl 52 0
Carl 22 0


hope it isn't too warped up, as you can see the 2 1s indicates where
solution is, before solver was applied there were only ones in C2 C8 now the
only ones are those for Paul and Santiago and if you apply an autofilter and
filter on 1 in column C you will get those 2 names visible only in column A
and their salaries in B. It might take a few minutes with a larger data set
but it certainly can work

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[R. Choate]

Just to be clear, you want one or more combinations of salaries which add up to exactly a given amount. You are not looking for
salaries which exceed a given threshhold, or when combined exceed a given threshhold or something like that? Sometimes the question
is worded such that the solution for the problem, as stated, is much more complicated than is what the person actually was looking
for. Miscommunication is a big player in the NGs. Believe me, I've miscommunicated lots of problems and I've caused much more brains
cells to be expended than were due.

Richard
--
RMC,CPA


Hello,

I have a large data file in 2 columns:
1st column = Employee ID
2nd Column = Salary

I want to find combinations of salaries whose total equals a certain amount
and identify those employees. Please any suggestions on how should I proceed?
What formula to use?

Thanks for your assistance.

al

 

[Guest]

Thanks Peo,

I followed your advice with a small data set and it did work, thanks a lot.
The problem, however, is that when I tried to replicate what you taught me
in a large data set (5000 rows), I got an error message that read: "Too many
adjustable cells". If you or somebody else has an alternate route, I would
love to know what it is. Otherwise I will thank you for your pointer. I
learned something new.

Have a terrific week-end!

Al

 

[Peo Sjoblom]

OK, I understand, I didn't know how big this task was, the solver that comes
with Excel is a less powerful one than what is available, here's a link

http://www.solver.com/exceluse.htm

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey