Formula to calculate values into total

**netmk** · 09-28-2010, 12:24 PM

This is my first post so please be patient with me. I am looking for assistance in creating a formula in Excel to sum 3 values and have them add up to a specific total. I have the following 3 values: 30.37, 8.38, and 10.97. I need to find out how many of each of these values will equal 610.52 and 1112.02. I started with additional values and was able to equally divide them into the sum to get the amounts I was looking for, but the two remaining values are not equally distributed and therefore are giving me trouble. My guess is the formula would need to look something like: (x*30.37+y*8.38+z*10.97=610.52); however, I have no idea how to input this into excel, much less get it to calculate. The only other variable in the equation are the amounts available of each value. For the 30.37 value I have only 20 segments that can be used. For the 8.38 value only 65 can be used, and for 10.97 only 52 can be used. If all amounts are totaled and multiplied by the variables it would equal 610.52+1112.02 or 1722.54. Thanks for any help you may be able to provide....

**masteff** · 09-29-2010, 04:03 PM

The topic you're looking for is linear programming and it can be accomplished by using the Solver add-in.
You would set up constraints such as: x, y & z are intergers, x<=20, y<=65, etc.
Solver might have trouble if multiple possible answers exist (such as if two segments added together are equal to a third resulting in two correct answers).

I've set your problem up and ran solver on it. It's looking for an answer that will make cell C6 equal to zero by changing B1:B3 subject to the contraints that B1:B3 are integers and B1:B3<=D1:D3.