Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for the calculation needed to come up with a total
I need to figure out out of 10 possible numbers which ones of those 10 equal
a specific number. An example is: My total is 50. I have numbers in cells like this 1, 12, 30, 24, 35, 16, 47, 10, 20 I need to know what this formula of those numbers would be to come up with the total of 50. The answer would be 30 + 20 = 50 If there a formula that will take that range of number and tell me which were needed to come up with the total? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for the calculation needed to come up with a total
"Phillse" wrote:
I need to figure out out of 10 possible numbers which ones of those 10 equal a specific number. But your example has 9 numbers ;-). An example is: [....] I need to know what this formula of those numbers would be to come up with the total of 50. The answer would be 30 + 20 = 50 So a more precise statement of your problem is: you need know which combination of 2 or more numbers (1 or more?) sums to the target value. What if there is more than one combination? If there a formula that will take that range of number and tell me which were needed to come up with the total? I would be surprised if there is such a formula, even for finding just one combination, other than a formula that invokes a UDF written in VBA. But I can offer a formula that tells you how many combinations you must try, if you want to find all combinations of 1 or more numbers. =sumproduct(combin(10,row(1:10)) Interesting: this is equal to =2^10-1, and more generally =2^n-1. ----- original message ----- "Phillse" wrote in message ... I need to figure out out of 10 possible numbers which ones of those 10 equal a specific number. An example is: My total is 50. I have numbers in cells like this 1, 12, 30, 24, 35, 16, 47, 10, 20 I need to know what this formula of those numbers would be to come up with the total of 50. The answer would be 30 + 20 = 50 If there a formula that will take that range of number and tell me which were needed to come up with the total? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for the calculation needed to come up with a total
I wrote something very similar to this request earlier today. Try this
code. there are two answers. You can put as many numbers into the array below an input box will ask you what total you want. The answers are 20 & 30 24,16,10 Sub FindTotal() Dim Combo() Data = Array(1, 12, 30, 24, 35, 16, 47, 10, 20) DataLen = UBound(Data) + 1 Total = Val(InputBox("Enter Total wanted : ")) ReDim Combo(DataLen) Level = 1 RowCount = 1 ActiveSheet.Cells.ClearContents Call Recursive(Data, Combo(), Level, Total, RowCount) End Sub Sub Recursive(Data, Combo, Level, Total, RowCount) DataLen = UBound(Data) + 1 'make combination For Count = (Combo(Level - 1) + 1) To _ DataLen Combo(Level) = Count MyTotal = 0 For ColCount = 1 To Level MyTotal = MyTotal + Data(Combo(ColCount) - 1) Next ColCount If MyTotal = Total Then For ColCount = 1 To Level Cells(RowCount, ColCount) = _ Data(Combo(ColCount) - 1) Next ColCount Cells(RowCount, DataLen + 1) = MyTotal RowCount = RowCount + 1 End If If Level < DataLen Then Call Recursive(Data, Combo, Level + 1, Total, RowCount) End If Next Count End Sub "Phillse" wrote: I need to figure out out of 10 possible numbers which ones of those 10 equal a specific number. An example is: My total is 50. I have numbers in cells like this 1, 12, 30, 24, 35, 16, 47, 10, 20 I need to know what this formula of those numbers would be to come up with the total of 50. The answer would be 30 + 20 = 50 If there a formula that will take that range of number and tell me which were needed to come up with the total? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for the calculation needed to come up with a total
I wrote:
But I can offer a formula that tells you how many combinations you must try, if you want to find all combinations of 1 or more numbers. =sumproduct(combin(10,row(1:10)) Interesting: this is equal to =2^10-1, and more generally =2^n-1. Well, that shoulda been obvious. Wasn't thinking. PS: Both formulas assume that there are no duplicate numbers among the 10 (or n), or you want to treat each duplicate number as distinct. For example, suppose your numbers are 10, 15, 25 and 25, and your target is 50. You would want 25+25 as a solution. But do you really want two solutions that are 10+15+25 and 10+15+25? ----- original message ----- "JoeU2004" wrote in message ... "Phillse" wrote: I need to figure out out of 10 possible numbers which ones of those 10 equal a specific number. But your example has 9 numbers ;-). An example is: [....] I need to know what this formula of those numbers would be to come up with the total of 50. The answer would be 30 + 20 = 50 So a more precise statement of your problem is: you need know which combination of 2 or more numbers (1 or more?) sums to the target value. What if there is more than one combination? If there a formula that will take that range of number and tell me which were needed to come up with the total? I would be surprised if there is such a formula, even for finding just one combination, other than a formula that invokes a UDF written in VBA. But I can offer a formula that tells you how many combinations you must try, if you want to find all combinations of 1 or more numbers. =sumproduct(combin(10,row(1:10)) Interesting: this is equal to =2^10-1, and more generally =2^n-1. ----- original message ----- "Phillse" wrote in message ... I need to figure out out of 10 possible numbers which ones of those 10 equal a specific number. An example is: My total is 50. I have numbers in cells like this 1, 12, 30, 24, 35, 16, 47, 10, 20 I need to know what this formula of those numbers would be to come up with the total of 50. The answer would be 30 + 20 = 50 If there a formula that will take that range of number and tell me which were needed to come up with the total? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for the calculation needed to come up with a total
"JoeU2004" wrote: I wrote: But I can offer a formula that tells you how many combinations you must try, if you want to find all combinations of 1 or more numbers. =sumproduct(combin(10,row(1:10)) Interesting: this is equal to =2^10-1, and more generally =2^n-1. Well, that shoulda been obvious. Wasn't thinking. PS: Both formulas assume that there are no duplicate numbers among the 10 (or n), or you want to treat each duplicate number as distinct. For example, suppose your numbers are 10, 15, 25 and 25, and your target is 50. You would want 25+25 as a solution. But do you really want two solutions that are 10+15+25 and 10+15+25? No the formula can only use the numbers that are there and those only once. It can't figure it can take 25 twice. If 25 is only entered once that is all it can use. I would want all possible varibles as long as those numbers are already entered. ----- original message ----- "JoeU2004" wrote in message ... "Phillse" wrote: I need to figure out out of 10 possible numbers which ones of those 10 equal a specific number. But your example has 9 numbers ;-). An example is: [....] I need to know what this formula of those numbers would be to come up with the total of 50. The answer would be 30 + 20 = 50 So a more precise statement of your problem is: you need know which combination of 2 or more numbers (1 or more?) sums to the target value. What if there is more than one combination? If there a formula that will take that range of number and tell me which were needed to come up with the total? I would be surprised if there is such a formula, even for finding just one combination, other than a formula that invokes a UDF written in VBA. But I can offer a formula that tells you how many combinations you must try, if you want to find all combinations of 1 or more numbers. =sumproduct(combin(10,row(1:10)) Interesting: this is equal to =2^10-1, and more generally =2^n-1. ----- original message ----- "Phillse" wrote in message ... I need to figure out out of 10 possible numbers which ones of those 10 equal a specific number. An example is: My total is 50. I have numbers in cells like this 1, 12, 30, 24, 35, 16, 47, 10, 20 I need to know what this formula of those numbers would be to come up with the total of 50. The answer would be 30 + 20 = 50 If there a formula that will take that range of number and tell me which were needed to come up with the total? Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for the calculation needed to come up with a total
"Phillse" wrote:
If 25 is only entered once that is all it can use. I never said anything different. At issue is whether you want to exclude "duplicate" combinations caused by the fact that some numbers appear multiple times in the list. You seem to indicate you don't; but I still wonder, since that seems unusual. The following is one solution. I took the liberty of including a duplicate 24 to make a list of 10, per your original specifications. It demonstrates the problem I'm anticipating. It is not difficult to avoid duplication; just additional overhead. This implementation works for a list of up to 31 numbers. That seems sufficient since any larger list would require looking at more than 4,294,967,295 combinations. On my computer, that would take about 9.5 hours. The output goes to the VBA Immediate Window; type ctrl-G to see it. It would not be difficult to output to cells in a worksheet instead. It also would not be difficult to use cells in a worksheet so specify the input parameters, i.e. the list of numbers and the target sum. Sub doit() 'show combinations of num that sum to target Dim num, soln Dim n As Integer, i As Integer, s As Integer Dim sel As Long, xsel As Long Dim total As Double Const target As Double = 50 num = Array(1, 12, 30, 24, 35, 16, 47, 10, 20, 24) n = UBound(num) ReDim soln(n) 'solution; subset of num() sel = 2 ^ (n + 1) - 1 'selector Debug.Print "------" Do total = 0: i = 0: s = -1 xsel = sel Do If xsel And 1 Then total = total + num(i) s = s + 1: soln(s) = num(i) End If xsel = xsel \ 2: i = i + 1 Loop Until xsel = 0 If total = target Then 'use ctrl-G to see solutions For i = 0 To s Debug.Print soln(i); Next i Debug.Print End If sel = sel - 1 Loop Until sel = 0 End Sub Theory of operation. Each bit in "sel" represents a member of the list "num". Since selection of a member is binary (select it or not), we treat "sel" as a binary number. Subtracting one from "sel" each iteration cycles through all binary combinations. ----- original message ----- "Phillse" wrote in message ... "JoeU2004" wrote: I wrote: But I can offer a formula that tells you how many combinations you must try, if you want to find all combinations of 1 or more numbers. =sumproduct(combin(10,row(1:10)) Interesting: this is equal to =2^10-1, and more generally =2^n-1. Well, that shoulda been obvious. Wasn't thinking. PS: Both formulas assume that there are no duplicate numbers among the 10 (or n), or you want to treat each duplicate number as distinct. For example, suppose your numbers are 10, 15, 25 and 25, and your target is 50. You would want 25+25 as a solution. But do you really want two solutions that are 10+15+25 and 10+15+25? No the formula can only use the numbers that are there and those only once. It can't figure it can take 25 twice. If 25 is only entered once that is all it can use. I would want all possible varibles as long as those numbers are already entered. ----- original message ----- "JoeU2004" wrote in message ... "Phillse" wrote: I need to figure out out of 10 possible numbers which ones of those 10 equal a specific number. But your example has 9 numbers ;-). An example is: [....] I need to know what this formula of those numbers would be to come up with the total of 50. The answer would be 30 + 20 = 50 So a more precise statement of your problem is: you need know which combination of 2 or more numbers (1 or more?) sums to the target value. What if there is more than one combination? If there a formula that will take that range of number and tell me which were needed to come up with the total? I would be surprised if there is such a formula, even for finding just one combination, other than a formula that invokes a UDF written in VBA. But I can offer a formula that tells you how many combinations you must try, if you want to find all combinations of 1 or more numbers. =sumproduct(combin(10,row(1:10)) Interesting: this is equal to =2^10-1, and more generally =2^n-1. ----- original message ----- "Phillse" wrote in message ... I need to figure out out of 10 possible numbers which ones of those 10 equal a specific number. An example is: My total is 50. I have numbers in cells like this 1, 12, 30, 24, 35, 16, 47, 10, 20 I need to know what this formula of those numbers would be to come up with the total of 50. The answer would be 30 + 20 = 50 If there a formula that will take that range of number and tell me which were needed to come up with the total? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula needed to concatenate text with result from calculation | Excel Discussion (Misc queries) | |||
Calculation with EDATE Help Needed! | Excel Worksheet Functions | |||
Help needed with a calculation question | Excel Worksheet Functions | |||
Formula Needed to Compare Dates and return a running total | Excel Worksheet Functions | |||
Financial Calculation Help Needed -- Annunity | Excel Worksheet Functions |