What cells in a array equal my value?

[Stephen]

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]

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

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.

[Stephen]

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
>
>

[Stephen]

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
> >
> >

[Morrigan]

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




[QUOTE=Stephen]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]

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