|
Addition function of multiple cells
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 |
Addition function of multiple cells
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 |
Addition function of multiple cells
See:
http://www.tushar-mehta.com/excel/te...ues/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 |
Addition function of multiple cells
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 . |
Addition function of multiple cells
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 . |
Addition function of multiple cells
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 . . |
Addition function of multiple cells
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 . . |
Addition function of multiple cells
DB74
you can use Tom Hutchins code, i already try and succeed... @ Tom, hope its ok if i give this code to others... thanks tom you can read in this link http://www.microsoft.com/communities...=en-us&m=1&p=1 To run the macro, select the range of 4 numbers (A1-A4). Then press Alt-F8 to bring up a list of available macros. Select Knapsack OK. The macro will prompt you for a target number. Enter 6 and click OK. 'Global variables for Knapsack Public Type RngType Nbr As Double 'Number in cell Addr As String 'Address of cell End Type Public Cellz() As RngType, Targett As Double Public Kount As Currency, RngCnt As Long, strTarget As String Public Soln() As RngType, SolnCnt As Long Public SolnNbr As Long, SolnRow As Long Sub Knapsack() 'Calls function KS to find combinations of values 'within the selection that total the target number. 'Current LIMITS: only finds target numbers which 'are positive numbers; can find multiple solutions, 'but not necessarily every possible solution. Also, 'if the target is the sum of the only two numbers in the 'selection which are smaller than the target, it may not 'find the solution. Dim c As Range, aa As Long, bb As Long, msg101 As String Dim Temp() As RngType, NegFlag As Boolean, BigFlag As Boolean On Error GoTo KSerr1 'Check if the selected range has 2 cells. If Selection.Count < 3 Then MsgBox "You must select more than 2 cells", , "Are you kidding?" Exit Sub End If 'Get the target number from the user. strTarget$ = InputBox("Enter the target amount") If Len(strTarget$) = 0 Then Exit Sub Targett# = CDbl(strTarget$) 'Load range to be checked into Cellz array. 'Store the address & value from each cell in the selected range. RngCnt& = -1 For Each c In Selection RngCnt& = RngCnt& + 1 ReDim Preserve Temp(RngCnt&) Temp(RngCnt&).Addr = c.Address Temp(RngCnt&).Nbr = c.Value Next c 'Add one more dummy element to Cellz() to make sure last cell gets tested. RngCnt& = RngCnt& + 1 ReDim Preserve Cellz(RngCnt&) Cellz(RngCnt&).Addr = Cellz(RngCnt& - 1).Addr Cellz(RngCnt&).Nbr = 0 'See if there are any negative numbers or numbers larger than Targett# in Temp(). BigFlag = False NegFlag = False For aa& = 0 To (RngCnt& - 1) If Temp(aa&).Nbr < 0 Then NegFlag = True ElseIf Temp(aa&).Nbr Targett# Then BigFlag = True End If Next aa& 'If both NegFlag and BigFlag are True (or False), 'copy all elements of Temp() to Cellz(). If Negflag is False but 'BigFlag is True, copy only elements that are smaller than Targett#. bb& = RngCnt& - 1 RngCnt& = -1 For aa& = 0 To bb& If (BigFlag = True) And (NegFlag = False) Then If (Temp(aa&).Nbr <= Targett#) And (Temp(aa&).Nbr < 0) Then RngCnt& = RngCnt& + 1 ReDim Preserve Cellz(RngCnt&) Cellz(RngCnt&).Addr = Temp(aa&).Addr Cellz(RngCnt&).Nbr = Temp(aa&).Nbr End If Else If Temp(aa&).Nbr < 0 Then RngCnt& = RngCnt& + 1 ReDim Preserve Cellz(RngCnt&) Cellz(RngCnt&).Addr = Temp(aa&).Addr Cellz(RngCnt&).Nbr = Temp(aa&).Nbr End If End If Next aa& 'Add one more dummy element to Cellz() to make sure last cell gets tested. RngCnt& = RngCnt& + 1 ReDim Preserve Cellz(RngCnt&) Cellz(RngCnt&).Addr = Temp(RngCnt& - 1).Addr Cellz(RngCnt&).Nbr = 0 'Set Kount@ and SolnNbr& to zero. Kount@ = 0 SolnNbr& = 0 'First call to KS() starts the chain of recursive calls. The For..Next 'loop starts a new chain every time the previous chain returns a solution 'or False (no solution). Each new chain starts one element farther in 'Cellz(), to ensure that a different solution, if any, will be found. 'However, this means that the first element in Cellz() can only be in 1 'solution, the 2nd element can only be in 2 solutions, etc. So, we are 'still not finding every possible solution. For bb& = 0 To (RngCnt& - 1) SolnCnt& = -1 If KS(Cellz(bb&).Nbr, bb& + 1) Then SolnNbr& = SolnNbr& + 1 SolnCnt& = SolnCnt& + 1 ReDim Preserve Soln(SolnCnt&) Soln(SolnCnt&).Addr = Cellz(bb&).Addr Soln(SolnCnt&).Nbr = Cellz(bb&).Nbr 'Add a new worksheet to the current workbook at the end. If SolnNbr& = 1 Then Worksheets.Add.Move After:=Worksheets(Worksheets.Count) SolnRow& = 1 Else 'Find the last row with data in column A. Cells(65535, 1).Select Selection.End(xlUp).Select Selection.Offset(4, 0).Select SolnRow& = Selection.Row End If 'Stop before hitting the last row of the worksheet & abending. If (SolnCnt& + SolnRow&) Rows.Count Then MsgBox "Can't fit all the solutions on the sheet", , "Error" Exit Sub End If 'List the elements in Soln(), which make up the solution. For aa& = 1 To SolnCnt& ActiveSheet.Cells(aa& + SolnRow& + 2, 1).Value = Soln(aa&).Addr ActiveSheet.Cells(aa& + SolnRow& + 2, 2).Value = Soln(aa&).Nbr 'Add some headings also. Cells(SolnRow&, 1).Value = Targett# Cells(SolnRow&, 2).Value = " = Target" Cells(SolnRow& + 2, 1).Value = "Cell" Cells(SolnRow& + 2, 2).Value = "Value" Next aa& End If 'Clear the array before the next iteration. ReDim Soln(0) Next bb& 'Find the last row with data in column A. 4 rows down, summarize the results. If SolnNbr& 0 Then Cells(65535, 1).Select Selection.End(xlUp).Select Selection.Offset(4, 0).Select Selection.Value = SolnNbr& & _ " solutions were found. KS function was called " & Kount@ & " times." End If 'Tell user we are done. Summarize results. MsgBox SolnNbr& & _ " solutions were found. KS function was called " & Kount@ & " times.", , "Done!" Exit Sub KSerr1: If Err.Number < 0 Then msg101$ = "Error # " & Str(Err.Number) & " was generated by " _ & Err.Source & Chr(13) & Err.Description MsgBox msg101$, , "Knapsack error", Err.HelpFile, Err.HelpContext End If End Sub Public Function KS(yy As Double, xx As Long) As Boolean 'My own recursive AND iterative algorithm for the classic 'knapsack programming problem. yy& is the cumulative total 'tested against the target number in this call, and passed 'to the next call increased by the next element of Cellz(). Dim nn As Long 'Call DoEvents so the screen can refresh, etc. DoEvents 'Add 1 to Kount every time function is called. Kount@ = Kount@ + 1 'Start a loop to test all remaining values of Cellz[xx] 'from this point in the solution chain. nn& = xx& Do While nn& <= RngCnt& If (yy# = Targett#) Then 'Found a solution in this call! Increase Soln() and save info 'about the last element of Cellz() that was tried (nn&, which 'should always be the same as xx& at this point in the function). SolnCnt& = SolnCnt& + 1 ReDim Preserve Soln(SolnCnt&) Soln(SolnCnt&).Addr = Cellz(nn&).Addr Soln(SolnCnt&).Nbr = Cellz(nn&).Nbr 'Return True to the calling function. KS = True Exit Function ElseIf (yy# Targett#) Then 'yy& in this call exceeds the target number. Return False to the 'calling function. KS = False Exit Function 'yy& is still less than the target number. Call KS() again, adding 'the next element in Cellz() to yy& ElseIf (KS(yy# + Cellz(nn&).Nbr, nn& + 1)) Then 'The call to another element of Cellz() found a successful chain. 'Info about that element of Cellz() has already been saved in Soln(). 'Now increase Soln() and store information about the Cellz() element 'in this call that is one link earlier in the solution chain. SolnCnt& = SolnCnt& + 1 ReDim Preserve Soln(SolnCnt&) Soln(SolnCnt&).Addr = Cellz(nn&).Addr Soln(SolnCnt&).Nbr = Cellz(nn&).Nbr 'Return True to the calling function. KS = True Exit Function End If nn& = nn& + 1 Loop KS = False End Function Put the code in a general VBA module in your workbook. If you are new to macros, this link to Jon Peltier's site may be helpful: http://peltiertech.com/WordPress/200...e-elses-macro/ Some of the lines may wrap from being posted in the forum. The visiual basic editor will color these red until you fix (unwrap) them. "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 |
Addition function of multiple cells
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 . . . |
Addition function of multiple cells
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 . . . |
| All times are GMT +1. The time now is 09:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com