Which version of Excel do you have?
I tested the formula with your example in Excel 2007 with expected
result.

Lars-Åke


On Tue, 25 May 2010 13:11:01 -0700, DB74
wrote:

Thanks,

When I copied the formula (all in 1 row), it indicates the formula is not
valid and highlights the first OFFSET in the formula.

"Lars-Åke Aspelin" wrote:

On Tue, 25 May 2010 11:11:01 -0700, DB74
wrote:

Does anyone know if there is a function that will take a list of numbers in
different cells and run various addition combinations on the list to detemine
which cells will add to a specific number? For example:

A1 = 1
A2 = 2
A3 = 5
A4 = 7

I want to know which cells added to gether will give me 6...answer = A1 + A3

Put the specific number, in you example 6, in cell C1.
Put the number of numbers, in your example 4, in cell C2.

Then type the following formula, all in one row, in cell B1

=IF(MID(DEC2BIN(MATCH(TRUE,MMULT(--MID(DEC2BIN(
ROW(OFFSET(A$1,,,2^C$2-1)),C$2),COLUMN(OFFSET(A$1,,,,C$2)),1),
(OFFSET(A$1,,,C$2,1)))=C$1,0),C$2),ROW(),1)="1","X ","")

Copy the formula in cell B1 down as far as you have numbers in column
A, ie to cell B4 in your example.

The result will be an "X" beside all the numbers to add to get the
specific number. In cell B1 and B3 in your example.
If there is no possible combination of numbers that will add up to the
specific number, you will get #N/A besides all numbers.

Note that there might be more than one possible combination of numbers
that solves the problem, this way you will only be given at most one
combination.

Hope this helps / Lars-Åke
.