Automating a calculation - (Solver)

**Muten_roshi** · 12-10-2006, 06:44 AM

I have a list of numbers as such:

5
1
3
6
100

I need to add up rows in order to reach a target figure. At the moment I'm manually going through the list and adding the numbers. Great fun.

So say I want 101, I want the system to return row no.2 and row no.5.
I'm pretty sure this can be done via VBA but I'm no code jocky. On the code front, the solution needs to be decently scalable as the list can be up to 20 variables long.

Many thanks

**TomMoston** · 12-10-2006, 07:16 AM

Just but this formula in where cell a1 id the first value in your list. Then just fill the formula down to the bottom of your list. Where this column = 101 will be the same as where you manually workrd it out.

=SUM($A$1:A1)

Cheers

**VBA Noob** · 12-10-2006, 07:38 AM

Hi,

If you have solver add-in installed try this

In range B1:B5, add a set of 0s

Then Enter this in C1

=SUMPRODUCT(A1:A5*B1:B5)

Now do Tools / Solver / Set Target Cell 'C1' to 'value of' and put in your
target value of 101
Next, using the range selector under the 'By Changing cells'
section, select cells B1:B5 as the ones to change and hit enter which will
take you back to the first dialog box. Now hit the 'Add' button, and add
the constraint that B1:B30 must be 'bin' (Means binary as in 1 or 0, and
it's one of the dropdowns, so just hit the arrow and select 'bin') and just
hit Solve. You MUST ensure that in this example, when you add the 'bin'
constraint range, you do not inadvertantly include the formula cell C1,
else you will get an error message such as 'Binary Contsraint cell reference
must include only adjustable cells'

VBA Noob

**Muten_roshi** · 12-10-2006, 07:42 AM

TomMoston = Thats not working, it sounds like my initial post wasn't clear. Your forumla just seems to be adding the numbers up, it isn't telling me which combination of rows = my target.