Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding pieces of a value in a table
Suppose I have a set of data below. I know that that a combination of some values in dataset make up a value of 252. What is the most efficient formula to find and isolate these values? 34 3 34 34 33 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 -- root ------------------------------------------------------------------------ root's Profile: http://www.excelforum.com/member.php...o&userid=36627 View this thread: http://www.excelforum.com/showthread...hreadid=563728 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding pieces of a value in a table
I can't answer your original question, but you don't need to post the
question multiple times. If someone can answer it they will. If they can't, it will remain unanswered. "root" wrote: Suppose I have a set of data below. I know that that a combination of some values in dataset make up a value of 252. What is the most efficient formula to find and isolate these values? 34 3 34 34 33 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 -- root ------------------------------------------------------------------------ root's Profile: http://www.excelforum.com/member.php...o&userid=36627 View this thread: http://www.excelforum.com/showthread...hreadid=563728 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding pieces of a value in a table
root Wrote: Suppose I have a set of data below. I know that that a combination of some values in dataset make up a value of 252. What is the most efficient formula to find and isolate these values? 34 3 34 34 33 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 You might want to look up "SUBSET SUM" on the internet. If you know what it means, this problem is NP-C. Basically, that means that in general you won't be able to find a solution very fast. If you only have a few numbers, like above, you can probably figure it out by just trying all possibilities until you find one that works. It's going to be harder to do in excel as with 22 values, there are 2^22 or about 4.2 million combinations. Your options are probably limited to VB, the Solver, or guess and test. IMO, VB is probably the better choice to run through all the possibilities and return one that works. (You might be able to guess and test with these numbers though... just my opinion.) Scott -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=563728 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding pieces of a value in a table
Ok, thankx for your help. System issue caused the post to replicate, I deleted the duplicated. -- root ------------------------------------------------------------------------ root's Profile: http://www.excelforum.com/member.php...o&userid=36627 View this thread: http://www.excelforum.com/showthread...hreadid=563728 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding pieces of a value in a table
Here's some code that will give you an answer for this. It assumes your values are in A1:A22 and that you want the answers in B1:B22. (in Sheet1) Scott --------- Option Explicit Const N = 22 Const SolveVal = 252 Dim X(N - 1) As Long Dim Answer(N - 1) As Long Function FindAnswer(Val As Long, Sum As Long) As Boolean If (Sum = SolveVal) Then FindAnswer = True Exit Function ElseIf (Val = N Or Sum SolveVal) Then FindAnswer = False Exit Function End If Answer(Val) = 1 If (FindAnswer(Val + 1, Sum + X(Val)) = True) Then FindAnswer = True Exit Function End If Answer(Val) = 0 If (FindAnswer(Val + 1, Sum) = True) Then FindAnswer = True Exit Function End If FindAnswer = False End Function Sub SubsetSum() Dim W As Worksheet Dim i As Long Set W = Worksheets("Sheet1") For i = 1 To N X(i - 1) = W.Cells(i, 1) Next i If (FindAnswer(0, 0) = True) Then For i = 1 To N W.Cells(i, 2) = Answer(i - 1) Next i Else MsgBox ("No solution") End If End Sub -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=563728 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding pieces of a value in a table
Scott, Thanx for the code. It works. Would this give me the first solution it finds? What if there are multiple solutions? -- root ------------------------------------------------------------------------ root's Profile: http://www.excelforum.com/member.php...o&userid=36627 View this thread: http://www.excelforum.com/showthread...hreadid=563728 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding pieces of a value in a table
root Wrote: Scott, Thanx for the code. It works. Would this give me the first solution it finds? What if there are multiple solutions? It gives the first solution it finds. It can be modified to give the Nth solution it finds fairly easily. Try this code. You can probably modify it to make it more robust, but it should do the trick. Scott -------- Option Explicit Const N = 22 Const SolveVal = 252 Dim SolNumber As Long Dim SolTarget As Long Dim X(N - 1) As Long Dim Answer(N - 1) As Long Function FindAnswer(Val As Long, Sum As Long) As Boolean If (Sum = SolveVal) Then SolNumber = SolNumber + 1 If (SolNumber = SolTarget) Then FindAnswer = True Else FindAnswer = False End If Exit Function ElseIf (Val = N Or Sum SolveVal) Then FindAnswer = False Exit Function End If Answer(Val) = 1 If (FindAnswer(Val + 1, Sum + X(Val)) = True) Then FindAnswer = True Exit Function End If Answer(Val) = 0 If (FindAnswer(Val + 1, Sum) = True) Then FindAnswer = True Exit Function End If FindAnswer = False End Function Sub SubsetSum() Dim W As Worksheet Dim i As Long Set W = Worksheets("Sheet1") For i = 1 To N X(i - 1) = W.Cells(i, 1) Next i SolNumber = 0 SolTarget = InputBox("Enter solution number:") If (FindAnswer(0, 0) = True) Then For i = 1 To N W.Cells(i, 2) = Answer(i - 1) Next i Else If (SolTarget SolNumber) Then MsgBox ("Only " & SolNumber & " solutions.") Else MsgBox ("No solution.") End If End If End Sub -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=563728 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up/math text | Excel Worksheet Functions | |||
Incorrect totals in Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Sort & Top List | Excel Discussion (Misc queries) | |||
Finding LARGE value within range of lookup table | Excel Discussion (Misc queries) | |||
Pivot Tables, Help? | Excel Discussion (Misc queries) |