I have a series of values both positive and negative that I want to change so
that they add up as close as possible to a given value. For example 100000 +
200000 + +300000+ 80000+ -50000 + -250000 I want to change so that they add
up as close as possible to 300000.

The problem is that the only change in value of the cells I want to allow is
to change them to 0 and I am having trouble setting up the constraints to do
that.
I can see that the answer is to change the 80000 to zero to give me the
300000 but how can I get solver to do this?

Thanks, Donna

If these values are in A1:A10, you could create a corresponding set of values
in B1:B10, initially filled with 1's. Your result can be expressed as
sumproduct(A1:A10,B1:B10). Now you can set up solver with the contraints on
B1:B10 (integers, =0, <=1), and the objective of getting the cell with the
sumproduct equation to equal 300000.
Your final results will be the products of A1 and B1, A2 and B2, etc.
HTH. --Bruce

"Donna" wrote:

I have a series of values both positive and negative that I want to change so
that they add up as close as possible to a given value. For example 100000 +
200000 + +300000+ 80000+ -50000 + -250000 I want to change so that they add
up as close as possible to 300000.

The problem is that the only change in value of the cells I want to allow is
to change them to 0 and I am having trouble setting up the constraints to do
that.
I can see that the answer is to change the 80000 to zero to give me the
300000 but how can I get solver to do this?

Thanks, Donna