Addition function of multiple cells
in [General]
|
From: DB74 on 25 May 2010 14:11 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
From: Lars-�ke Aspelin on 25 May 2010 15:07 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
From: Gary''s Student on 25 May 2010 15:22 See: http://www.tushar-mehta.com/excel/templates/match_values/index.html -- Gary''s Student - gsnu201003 "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
From: DB74 on 25 May 2010 16:11 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 > . >
From: Lars-�ke Aspelin on 25 May 2010 17:06 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 >> . >>
|
Next
|
Last
Pages: 1 2 3 Prev: Macro to Cut and paste Values Next: CellinFooter() Specify Which Sheets are updated |