Prev: Macro to Cut and paste Values
Next: CellinFooter() Specify Which Sheets are updated
From: Lars-�ke Aspelin on 28 May 2010 15:55
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
<DB74(a)discussions.microsoft.com> 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
>> <DB74(a)discussions.microsoft.com> 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
>> >> <DB74(a)discussions.microsoft.com> 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
>> >> >> <DB74(a)discussions.microsoft.com> 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
>> >> >> .
>> >> >>
>> >>
>> >> .
>> >>
>>
>> .
>>

First  |  Prev  | 
Pages: 1 2 3
Prev: Macro to Cut and paste Values
Next: CellinFooter() Specify Which Sheets are updated