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 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 >> >> >> . >> >> >> >> >> >> >> . >> >> >> >> . >> First  |  Prev  |