I need to solve the following problem in Solver. To me, the setup in Solver
should be straightforward, but I am not getting the correct answer or really
any answer.

Simplified Example: In Column A, I have a list of numbers; in Column B, the
respective percentage-of-totals; and in Column C, the percentages from Column
B rounded to the thousandths place (3 digits) -- so that Columns A & C are,
for example:

A C
1 3.70%
2 7.40%
3 11.10%
4 14.80%
3 11.10%
5 18.50%
6 22.20%
3 11.10%

The total sum of the values in Column C = 99.90%. I would like to change
the value of one cell in Column A -- for example, the value in A1 (which is
1) -- to come as close as possible to a total of 100% in Column C.

I have set the Target Cell as a separate cell which is the absolute value of
the difference between the totals of Columns B & C and set it Equal To 'Min'.
'By Changing Cells' is set to cell $A$1. I have tried different
combinations of constraints ($A$1 is an integer, = 0, <=100) and tried using
no constraints. However, I still get basically no answer. (It just gives me
whatever I started with.)

Any help is appreciated -- thanks.

Can you not use Goal seek instead?

"nirani" wrote in message
...
I need to solve the following problem in Solver. To me, the setup in
Solver
should be straightforward, but I am not getting the correct answer or
really
any answer.

Simplified Example: In Column A, I have a list of numbers; in Column B,
the
respective percentage-of-totals; and in Column C, the percentages from
Column
B rounded to the thousandths place (3 digits) -- so that Columns A & C
are,
for example:

A C
1 3.70%
2 7.40%
3 11.10%
4 14.80%
3 11.10%
5 18.50%
6 22.20%
3 11.10%

The total sum of the values in Column C = 99.90%. I would like to change
the value of one cell in Column A -- for example, the value in A1 (which
is
1) -- to come as close as possible to a total of 100% in Column C.

I have set the Target Cell as a separate cell which is the absolute value
of
the difference between the totals of Columns B & C and set it Equal To
'Min'.
'By Changing Cells' is set to cell $A$1. I have tried different
combinations of constraints ($A$1 is an integer, = 0, <=100) and tried
using
no constraints. However, I still get basically no answer. (It just gives
me
whatever I started with.)

Any help is appreciated -- thanks.

Thanks for the detailed explanation.

Solver, like other optimization packages in its class, doesn't deal
well with non-smooth functions, which is what ROUND is. Similarly, ABS
is not a function that works well in a Solver model, though it is not
as bad as ROUND.

For a template along the lines of what you appear to be trying to
accomplish see
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/ti...set-match.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I need to solve the following problem in Solver. To me, the setup in Solver
should be straightforward, but I am not getting the correct answer or really
any answer.

Simplified Example: In Column A, I have a list of numbers; in Column B, the
respective percentage-of-totals; and in Column C, the percentages from Column
B rounded to the thousandths place (3 digits) -- so that Columns A & C are,
for example:

A C
1 3.70%
2 7.40%
3 11.10%
4 14.80%
3 11.10%
5 18.50%
6 22.20%
3 11.10%

The total sum of the values in Column C = 99.90%. I would like to change
the value of one cell in Column A -- for example, the value in A1 (which is
1) -- to come as close as possible to a total of 100% in Column C.

I have set the Target Cell as a separate cell which is the absolute value of
the difference between the totals of Columns B & C and set it Equal To 'Min'.
'By Changing Cells' is set to cell $A$1. I have tried different
combinations of constraints ($A$1 is an integer, = 0, <=100) and tried using
no constraints. However, I still get basically no answer. (It just gives me
whatever I started with.)

Any help is appreciated -- thanks.

It sounds to me as though the goal you provided is wrong... don't minimize
the difference between columns B & C, but rather the absolute difference
between the sum of column C and 100%. (And yes, Goal Seek should be able to
handle that as well).


"Tushar Mehta" wrote:

Thanks for the detailed explanation.

Solver, like other optimization packages in its class, doesn't deal
well with non-smooth functions, which is what ROUND is. Similarly, ABS
is not a function that works well in a Solver model, though it is not
as bad as ROUND.

For a template along the lines of what you appear to be trying to
accomplish see
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/ti...set-match.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I need to solve the following problem in Solver. To me, the setup in Solver
should be straightforward, but I am not getting the correct answer or really
any answer.

Simplified Example: In Column A, I have a list of numbers; in Column B, the
respective percentage-of-totals; and in Column C, the percentages from Column
B rounded to the thousandths place (3 digits) -- so that Columns A & C are,
for example:

A C
1 3.70%
2 7.40%
3 11.10%
4 14.80%
3 11.10%
5 18.50%
6 22.20%
3 11.10%

The total sum of the values in Column C = 99.90%. I would like to change
the value of one cell in Column A -- for example, the value in A1 (which is
1) -- to come as close as possible to a total of 100% in Column C.

I have set the Target Cell as a separate cell which is the absolute value of
the difference between the totals of Columns B & C and set it Equal To 'Min'.
'By Changing Cells' is set to cell $A$1. I have tried different
combinations of constraints ($A$1 is an integer, = 0, <=100) and tried using
no constraints. However, I still get basically no answer. (It just gives me
whatever I started with.)

Any help is appreciated -- thanks.