If you can accept "1" and "0" instead of "X" and "blank"
(you can always have this in a hidden helper column and translate "1"
to "X" and "0" to "blank" with a second formula) try this formula that
is limited to 7 levels of nesting:

=MID(DEC2BIN(MATCH(TRUE,MMULT(--MID(DEC2BIN(ROW(A$1:A$15),C$2),COLUMN(OFFSET(A$1,, ,,C$2)),1),OFFSET(A$1,,,C$2,1))=C$1,0),C$2),ROW(), 1)

Note that it is no longer sufficient to just change cell C2 to reflect
a changed number of numbers in the A column.
You also have to manually modify the formula to suit the number of
numbers that you have.
In the formula above, replace the 15 with 2^n-1 where n is the number
of numbers.

Hope this helps / Lars-Åke

On Fri, 28 May 2010 11:56:06 -0700, DB74
wrote:

I tried the formula in 07 and it worked and then saved the file and tried to
open it in 03. I received a msg indicating the formula had too many nested
statements for 03. Any chance you can modify the formula so that it does not
have more than the max of 7 nested statements?

Thanks.

"Lars-Åke Aspelin" wrote:

I don't think so, but I have only tested the formula in Excel 2007, so
I am not sure.

Lars-Åke

On Thu, 27 May 2010 13:13:28 -0700, DB74
wrote:

I am using 2003... would that make a difference?

"Lars-Åke Aspelin" wrote:

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
.


.


.