Can Solver solve this?

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" <nirani@discussions.microsoft.com> wrote in message
news:B0119124-9D6A-4940-B554-5B488E2BEB99@microsoft.com...
> 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 <B0119124-9D6A-4940-B554-5B488E2BEB99@microsoft.com>,
nirani@discussions.microsoft.com 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 <B0119124-9D6A-4940-B554-5B488E2BEB99@microsoft.com>,
> nirani@discussions.microsoft.com 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.
> >
>