software

Dear all - I'm not familiar with accounting jargon but what I need is this:
say I have a range of different numbers from cell A2 to cell A100. I need to
establish if any of these cells *ADDED TOGETHER* equal quot;xquot;. so when I enter
the no quot;xquot; into the formula in cell A102, this will then highlight which
cells will equal quot;xquot;.

An example: I need to establish if any cells added together equal 150. I
put in 150 in the formula. Formula then searches the range A2 to A100 and
highlights cell A27 which contains no.100, and cell A89 which contains the
no. 50.

Any assistance will be most sincerely appreciated!!

Cheers Bob


chickenshed_bob Wrote:
gt; Dear all - I'm not familiar with accounting jargon but what I need is
gt; this:
gt; say I have a range of different numbers from cell A2 to cell A100. I
gt; need to
gt; establish if any of these cells *ADDED TOGETHER* equal quot;xquot;. so when I
gt; enter
gt; the no quot;xquot; into the formula in cell A102, this will then highlight
gt; which
gt; cells will equal quot;xquot;.
gt;
gt; An example: I need to establish if any cells added together equal 150.
gt; I
gt; put in 150 in the formula. Formula then searches the range A2 to A100
gt; and
gt; highlights cell A27 which contains no.100, and cell A89 which contains
gt; the
gt; no. 50.
gt;
gt; Any assistance will be most sincerely appreciated!!
gt;
gt; Cheers Bob
To highlight:
Select A2:A100, Formats -gt; Conditional formatting
If the current cell is A2 , then formula is =$B2=1, and choose a
colourNow use Solver and try the following:

B2:B100 = 0
A102 = SUMPRODUCT(A2:A100,B2:B100)

In Solver, target cell is A102 equals to value of lt;your target valuegt;
by changing cells B2:B100. There should also be another window that
reads quot;Subject to the contraintsquot;. In here add a constraint B2:B100 =
binary. This limit B2:B100 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.
A2:A100 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--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: www.excelforum.com/member.php...foamp;userid=7094
View this thread: www.excelforum.com/showthread...hreadid=524442
Morrigan - thankyou for the response.

Unfortunatelly, I can't get it to work. In the quot;solver parametersquot;
I've set the target cell to equal to an amount which I know exists in
two of the cells in the range (and the two said cells are the only
cells which equal to the amount in the target cell) but it seems unable
to work this out.

--
chickenshed_bob
------------------------------------------------------------------------
chickenshed_bob's Profile: www.excelforum.com/member.php...oamp;userid=32985
View this thread: www.excelforum.com/showthread...hreadid=524442
Morrigan - thankyou for the response.

Unfortunatelly, I can't get it to work. In the quot;solver parametersquot;
I've set the target cell to equal to an amount which I know exists in
two of the cells in the range (and the two said cells are the only
cells which equal to the amount in the target cell) but it seems unable
to work this out.

--
chickenshed_bob
------------------------------------------------------------------------
chickenshed_bob's Profile: www.excelforum.com/member.php...oamp;userid=32985
View this thread: www.excelforum.com/showthread...hreadid=524442
Attached is an example using 10 numbers.Hope it helps. -------------------------------------------------------------------
|Filename: solver.zip |
|Download: www.excelforum.com/attachment.php?postid=4552 |
-------------------------------------------------------------------

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: www.excelforum.com/member.php...foamp;userid=7094
View this thread: www.excelforum.com/showthread...hreadid=524442