How can I determine the components of a sum?

[nealmjr]

If I have a table of numbers and a number that is supposed to be a sum of
selected numbers from the table (but not shown by formula), is there an Excel
formula or function that, given the total, will identify which numbers in the
table sum to the total I've been given?

[B. R.Ramachandran]

Hi,

Let's suppose that the list of numbers is in Column A (e.g., A2:A101).
Create Column B (i.e., B2:B101) by entering 1 in all cells.
Create Column C with the formula =A2*B2 entered in C2 and autofilled down to
C101.
Enter the target sum (the number that is supposed to be the sum of a subset
of numbers from the list) in D2.
In some cell, say E2, enter the formula, =SUM(C2:C101)-D2

Now you are going use "Solver" (under "Tools") for finding out the subset of
numbers that would make E2 equal to zero (Please note that the"Solver" add-in
should have been installed!)

"Tools" --> "Solver"-->
"Set Target Cell" $E$2
"Equal to" check "Value of" and enter 0
"By Changing Cells" B2:B101 (this is the column containing '1's)
"Subject to the Constraints" --> click "Add" and in the pop-up bar enter the
formula,
B2:B101 (on the left-side), click on "<=" and select "bin" and click "OK"
(the constraint would read as B2:B101 = binary)
Click "Solve"

Solver would crunch numbers, change some of the '1's in Column B to '0's,
and after finding a solution if there is one, show a pop-up "Solver Results".
If the results are satisfactory click "Keep Solver Solution".
Please note that multiple solutions are possible for such problems, and
Solver would find one of those solutions.

Regards,
B. R. Ramachandran

"nealmjr" wrote:

> If I have a table of numbers and a number that is supposed to be a sum of
> selected numbers from the table (but not shown by formula), is there an Excel
> formula or function that, given the total, will identify which numbers in the
> table sum to the total I've been given?