What cells in a array equal my value?
 

Hi all.

I have a column of cells that have many different values.

These values are numerical costs. ie 150.54, 5,541.00 ect

I have a numerical value that comprises of several of these cells added
together. However I do not know which ones need to be added to get the value
I'm looking for... Is there a function I can use that will take all the
cells and advise which ones need to be added to equal my value? I think this
might be a tough one..

Many thanks in advance Stephen.

Use Solver to do this:

Make a new column (column B for now) of zero beside your cost. (This
is the cells you want to change)
Do a sumproduct in column C of columnA&B.
Make a cell = sum(column C) (This is your target cell)

In solver, set constraints to binary and run it.


Hope it helps




Stephen Wrote:
Hi all.

I have a column of cells that have many different values.

These values are numerical costs. ie 150.54, 5,541.00 ect

I have a numerical value that comprises of several of these cells
added
together. However I do not know which ones need to be added to get the
value
I'm looking for... Is there a function I can use that will take all
the
cells and advise which ones need to be added to equal my value? I
think this
might be a tough one..

Many thanks in advance Stephen.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=387543

Thanks Morrigan.

I've never used 'solver' before, but I've loaded it in my 'add-ins' and
tried to do what you described.

However.. I'm not sure how to set constraints to binary.. This does not
appear to be in the 'options' of 'solver'. I'm using MS Excel 2003.

Stephen.

"Morrigan" wrote:


Use Solver to do this:

Make a new column (column B for now) of zero beside your cost. (This
is the cells you want to change)
Do a sumproduct in column C of columnA&B.
Make a cell = sum(column C) (This is your target cell)

In solver, set constraints to binary and run it.


Hope it helps




Stephen Wrote:
Hi all.

I have a column of cells that have many different values.

These values are numerical costs. ie 150.54, 5,541.00 ect

I have a numerical value that comprises of several of these cells
added
together. However I do not know which ones need to be added to get the
value
I'm looking for... Is there a function I can use that will take all
the
cells and advise which ones need to be added to equal my value? I
think this
might be a tough one..

Many thanks in advance Stephen.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=387543

Hi Morrigan
Just wanted to confirm that you understand what I'm trying to achieve here.
I'm not the best at explaining myself and what I need..

I've got several cells, sometimes hundreds, and only a few of these cells
add up to the value that I'm given. My problem is finding these particular
cells that add-up to equal my value... The cells could also be duplicated..

Your solution below might be what I'm looking for, but I'm not sure I know
how it works..

Thanks for your patience. Stephen

"Stephen" wrote:

Thanks Morrigan.

I've never used 'solver' before, but I've loaded it in my 'add-ins' and
tried to do what you described.

However.. I'm not sure how to set constraints to binary.. This does not
appear to be in the 'options' of 'solver'. I'm using MS Excel 2003.

Stephen.

"Morrigan" wrote:


Use Solver to do this:

Make a new column (column B for now) of zero beside your cost. (This
is the cells you want to change)
Do a sumproduct in column C of columnA&B.
Make a cell = sum(column C) (This is your target cell)

In solver, set constraints to binary and run it.


Hope it helps




Stephen Wrote:
Hi all.

I have a column of cells that have many different values.

These values are numerical costs. ie 150.54, 5,541.00 ect

I have a numerical value that comprises of several of these cells
added
together. However I do not know which ones need to be added to get the
value
I'm looking for... Is there a function I can use that will take all
the
cells and advise which ones need to be added to equal my value? I
think this
might be a tough one..

Many thanks in advance Stephen.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=387543

Let's say A1:A20 is your data. Do the following:

B1:B20 = 0
C1:C20 = SUMPRODUCT(A1:A20,B1:B20)
C21 = SUM(C1:C20)

In Solver, target cell is C21 equals to value of <your target value by
changing cells B1:B20. There should also be another window that reads
"Subject to the contraints". In here add a constraint B1:B20 = binary.
This limit B1:B20 to be either 1 or 0. Run solver.

Here is the catch, if some numbers you know they can appear more than
once, those numbers need to appear more than once in your data (ie.
A1:A20 in the above example). Another catch is that there may be more
than one combination that yields the same target value and solver will
not give you all the results.


Hope it helps




Stephen Wrote:
Hi Morrigan
Just wanted to confirm that you understand what I'm trying to achieve
here.
I'm not the best at explaining myself and what I need..

I've got several cells, sometimes hundreds, and only a few of these
cells
add up to the value that I'm given. My problem is finding these
particular
cells that add-up to equal my value... The cells could also be
duplicated..

Your solution below might be what I'm looking for, but I'm not sure I
know
how it works..

Thanks for your patience. Stephen


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=387543

Hi Morrigan.

I'm confident that I'm following your instructions however I'm either not
getting a unexpected result or advised that the 'Solver could not find a
feasible solution'

Where am I going wrong here? I'm not sure what the Answer report is telling
me or how/why Column B gets updated..

Thanks for all your help,

A B C E
1 2.50 0.00 2.9
2 1.75 1.00 2.9
3 1.65 0.00 2.9
4 8.00 0.00 2.9
5 9.45 0.00 2.9
6 2.20 0.00 2.9
7 6.90 0.00 2.9 26.10
8 7.00 0.00 2.9
9 5.50 0.00 2.9
10 2.25 0.00 2.9
11 2.35 0.00 2.9
12 2.90 0.00 2.9
13 3.72 0.00 2.9
14 3.75 0.00 2.9
15 4.15 0.00 2.9
16 9.46 0.00 2.9
17 1.15 1.00 2.9
18 1.47 0.00 2.9
19 5.52 0.00 2.9
20 5.65 0.00 2.9
26.10

I'm sorry I made a mistake when I was explaining.

Let's say A1:A20 is your data. Do the following:

B1:B20 = 0
B21 = SUMPRODUCT(A1:A20,B1:B20)

In Solver, target cell is B21 equals to value of <your target value by
changing cells B1:B20. There should also be another window that reads
"Subject to the contraints". In here add a constraint B1:B20 = binary.
This limit B1:B20 to be either 1 or 0. Run solver.

Here is the catch, if some numbers you know they can appear more than
once, those numbers need to appear more than once in your data (ie.
A1:A20 in the above example). Another catch is that there may be more
than one combination that yields the same target value and solver will
not give you all the results.





Stephen Wrote:
Hi Morrigan.

I'm confident that I'm following your instructions however I'm either
not
getting a unexpected result or advised that the 'Solver could not find
a
feasible solution'

Where am I going wrong here? I'm not sure what the Answer report is
telling
me or how/why Column B gets updated..

Thanks for all your help,

A B C E
1 2.50 0.00 2.9
2 1.75 1.00 2.9
3 1.65 0.00 2.9
4 8.00 0.00 2.9
5 9.45 0.00 2.9
6 2.20 0.00 2.9
7 6.90 0.00 2.9 26.10
8 7.00 0.00 2.9
9 5.50 0.00 2.9
10 2.25 0.00 2.9
11 2.35 0.00 2.9
12 2.90 0.00 2.9
13 3.72 0.00 2.9
14 3.75 0.00 2.9
15 4.15 0.00 2.9
16 9.46 0.00 2.9
17 1.15 1.00 2.9
18 1.47 0.00 2.9
19 5.52 0.00 2.9
20 5.65 0.00 2.9
26.10


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=387543

It worked!! ;-)

Many thanks for all your help in this Morrigan!!

Morrigan,

Nice advice. I've tried your solution and it paritally worked.

In my situation there are possible 43 values that could make up one value.
Keep in mind not all 43 numbers would give me the total I want. It could be
20 numbers out of 43 or just 2.

So if you work it out, there are 2^43 possibilities altogether, i.e.
17,592,186,044,416.00 possiblities.

How long do I have to let solver run to find one possible solution ???? I've
left the my home computer (Athlon 1.9ghz) run all day but it still hasn't
found a solution.

Would you know a way to set up solver so it would find a solution quicker.
I've tried adding my contraints but it still hasn't found one solution.

Look forward to your response.




"Stephen" wrote:

It worked!! ;-)

Many thanks for all your help in this Morrigan!!

According to me, 2^43 = 8.796E+12 not 1.759E+13. In addition, if you
have 43 numbers and your target value can be made up of 1, 2, 3...up to
43 numbers. The total possible number of combinations is:

SUM(n!/(n-i)!)

where ! = factorial notation, n = 43 in this case, and i =
1,2,3,4....n

Therefore possibilities = 1.642E+53 :eek:


Anyway, back to topic, I tried using 43 random numbers range from
1-999. With a 1.6gHz computer, I was able to get an answer within
3sec. Thus, I am guessing it's not the computer speed.




Victor Wrote:
Morrigan,

Nice advice. I've tried your solution and it paritally worked.

In my situation there are possible 43 values that could make up one
value.
Keep in mind not all 43 numbers would give me the total I want. It
could be
20 numbers out of 43 or just 2.

So if you work it out, there are 2^43 possibilities altogether, i.e.
17,592,186,044,416.00 possiblities.

How long do I have to let solver run to find one possible solution ????
I've
left the my home computer (Athlon 1.9ghz) run all day but it still
hasn't
found a solution.

Would you know a way to set up solver so it would find a solution
quicker.
I've tried adding my contraints but it still hasn't found one
solution.

Look forward to your response.




"Stephen" wrote:

It worked!! ;-)

Many thanks for all your help in this Morrigan!!




--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=387543

I tried some more with numbers of 2 decimals. It turns out it can taken
quite a bit loner depending on your data. If you want to speed up the
process, you can try make some educated guesses. (ie. instead of
having all the changing cells start at 0, put some of them to 1)


Hope it helps.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=387543