Obtaining series of data that adds up to a particular large figure

[joke folorunsho]

I have a large figure and series of other small figures on differen
rows.

How do I get the different sets of small figures that exactly or almos
add up to the large figure:confused

 

[Peo Sjoblom]

You can try to use solver under tools>solver (if it is not there select it
from under tools>add-ins and
keep the office/excel cd handy and follow instructions)

Put the series of small figures in a range, select that range and click in
the name box (or do insert>name define)
and give it a name, let's say MyRange. Put 1 in each adjacent cell (if
MyRange is A2:A50 put 1 in B2:B20)

Give it a name like MyOnes.. In a cell put this formula

=SUMPRODUCT(MyRange,MyOnes)

let's say you put that formula in D2

now do tools>solver and in the Set Target Cell: put $D$2, check Value of: in
the Equal To: and put
the large figure there. Now in the By Changing cells put MyOnes, under the
Subject to constraints click Add and
put MyOnes, from the dropdown middle box click the down arrow and select
Bin, click Add.

Now click the Solve button. Keep solver solution and look at the ranges.
The small numbers that have 1 next to them will total the large number. You
can check that by doing a sumif,

=SUMIF(MyOnes,1,MyRange)