software

I have a long list of monetary amounts listed on an excel spreadsheet. I am
trying to find combinations of those amounts that add up to certain larger
sums. For example on a smaller scale:
10
15
21
41
53
How can I find the cells that add up to 108? Is there a formula or function
I can use on a large scale for this type of thing?

You can use solver,

there was a similar question a few days ago, here's a link

tinyurl.com/pfswmnote that the solver that comes with excel is limited in how large data set
you can use--

Regards,

Peo Sjoblom

nwexcelsolutions.com
quot;Joshua Jacobyquot; gt; wrote in message
...
gt;I have a long list of monetary amounts listed on an excel spreadsheet. I
gt;am
gt; trying to find combinations of those amounts that add up to certain larger
gt; sums. For example on a smaller scale:
gt; 10
gt; 15
gt; 21
gt; 41
gt; 53
gt; How can I find the cells that add up to 108? Is there a formula or
gt; function
gt; I can use on a large scale for this type of thing?

Interesting question

You could do this by using the DEC2BIN function to generate all of the
binary numbers up to two to the power of the count of the numbers that
you have. The individual places could then be used as switches for
whether each number is included in the sum or not. This would allow you
to evaluate all possible combinations and therefore see which matches
your target.--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: www.excelforum.com/member.php...oamp;userid=10931
View this thread: www.excelforum.com/showthread...hreadid=540388Hmm, I just tried that link and I was denied access to it by my work pc.

quot;Peo Sjoblomquot; wrote:

gt; You can use solver,
gt;
gt; there was a similar question a few days ago, here's a link
gt;
gt; tinyurl.com/pfswm
gt;
gt;
gt; note that the solver that comes with excel is limited in how large data set
gt; you can use
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt; quot;Joshua Jacobyquot; gt; wrote in message
gt; ...
gt; gt;I have a long list of monetary amounts listed on an excel spreadsheet. I
gt; gt;am
gt; gt; trying to find combinations of those amounts that add up to certain larger
gt; gt; sums. For example on a smaller scale:
gt; gt; 10
gt; gt; 15
gt; gt; 21
gt; gt; 41
gt; gt; 53
gt; gt; How can I find the cells that add up to 108? Is there a formula or
gt; gt; function
gt; gt; I can use on a large scale for this type of thing?
gt;
gt;
gt;

Hmm, well here's the situation. I'm trying to reconcile an account that has
over 300 entries in it for the month of december. So, over 300 debits, but
only say 100 credits. And I'm trying to each credit with the corresponding
bunch of debits. It takes forever to try to do it visually by highlighting
groups of debit entries to match their sum to a credit entry. You understand
what I mean?

quot;mricequot; wrote:

gt;
gt; Interesting question
gt;
gt; You could do this by using the DEC2BIN function to generate all of the
gt; binary numbers up to two to the power of the count of the numbers that
gt; you have. The individual places could then be used as switches for
gt; whether each number is included in the sum or not. This would allow you
gt; to evaluate all possible combinations and therefore see which matches
gt; your target.
gt;
gt;
gt; --
gt; mrice
gt;
gt; Research Scientist with many years of spreadsheet development experience
gt; ------------------------------------------------------------------------
gt; mrice's Profile: www.excelforum.com/member.php...oamp;userid=10931
gt; View this thread: www.excelforum.com/showthread...hreadid=540388
gt;
gt;


This code by Harlan Grove may help (I haven't tried it myself!). It finds
numbers that add to a given sum.

'Begin VBA Code' By Harlan GroveSub findsums()
'This *REQUIRES* VBAProject references to
'Microsoft Scripting Runtime
'Microsoft VBScript Regular Expressions 1.0 or higherConst TOL As Double = 0.000001 'modify as needed
Dim c As VariantDim j As Long, k As Long, n As Long, p As Boolean
Dim s As String, t As Double, u As Double
Dim v As Variant, x As Variant, y As Variant
Dim dc1 As New Dictionary, dc2 As New Dictionary
Dim dcn As Dictionary, dco As Dictionary
Dim re As New RegExpre.Global = True
re.IgnoreCase = TrueOn Error Resume NextSet x = Application.InputBox( _
Prompt:=quot;Enter range of values:quot;, _
Title:=quot;findsumsquot;, _
Default:=quot;quot;, _
Type:=8 _
)If x Is Nothing Then
Err.Clear
Exit Sub
End Ify = Application.InputBox( _
Prompt:=quot;Enter target value:quot;, _
Title:=quot;findsumsquot;, _
Default:=quot;quot;, _
Type:=1 _
)If VarType(y) = vbBoolean Then
Exit Sub
Else
t = y
End IfOn Error GoTo 0Set dco = dc1
Set dcn = dc2Call recsolnFor Each y In x.Value2
If VarType(y) = vbDouble Then
If Abs(t - y) lt; TOL Then
recsoln quot; quot; amp; Format(y)ElseIf dco.Exists(y) Then
dco(y) = dco(y) 1ElseIf y lt; t - TOL Then
dco.Add Key:=y, Item:=1c = CDec(c 1)
Application.StatusBar = quot;[1] quot; amp; Format(c)End IfEnd If
Next yn = dco.CountReDim v(1 To n, 1 To 3)For k = 1 To n
v(k, 1) = dco.Keys(k - 1)
v(k, 2) = dco.Items(k - 1)
Next kqsortd v, 1, nFor k = n To 1 Step -1
v(k, 3) = v(k, 1) * v(k, 2) v(IIf(k = n, n, k 1), 3)
If v(k, 3) gt; t Then dcn.Add Key:=quot; quot; amp; _
Format(v(k, 1)), Item:=v(k, 1)
Next kOn Error GoTo CleanUp
Application.EnableEvents = False
Application.Calculation = xlCalculationManualFor k = 2 To n
dco.RemoveAll
swapo dco, dcnFor Each y In dco.Keys
p = FalseFor j = 1 To n
If v(j, 3) lt; t - dco(y) - TOL Then Exit For
x = v(j, 1)
s = quot; quot; amp; Format(x)
If Right(y, Len(s)) = s Then p = True
If p Then
re.Pattern = quot;\quot; amp; s amp; quot;(?=(\ |$))quot;
If re.Execute(y).Count lt; v(j, 2) Then
u = dco(y) x
If Abs(t - u) lt; TOL Then
recsoln y amp; s
ElseIf u lt; t - TOL Then
dcn.Add Key:=y amp; s, Item:=u
c = CDec(c 1)
Application.StatusBar = quot;[quot; amp; Format(k) amp; quot;] quot; amp; _
Format(c)
End If
End If
End If
Next j
Next yIf dcn.Count = 0 Then Exit For
Next kIf (recsoln() = 0) Then _
MsgBox Prompt:=quot;all combinations exhaustedquot;, _
Title:=quot;No Solutionquot;CleanUp:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = FalseEnd SubPrivate Function recsoln(Optional s As String)
Const OUTPUTWSN As String = quot;findsums solutionsquot; 'modify to tasteStatic r As Range
Dim ws As WorksheetIf s = quot;quot; And r Is Nothing Then
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets(OUTPUTWSN)
If ws Is Nothing Then
Err.Clear
Application.ScreenUpdating = False
Set ws = ActiveSheet
Set r = Worksheets.Add.Range(quot;A1quot;)
r.Parent.Name = OUTPUTWSN
ws.Activate
Application.ScreenUpdating = False
Else
ws.Cells.Clear
Set r = ws.Range(quot;A1quot;)
End If
recsoln = 0
ElseIf s = quot;quot; Then
recsoln = r.Row - 1
Set r = Nothing
Else
r.Value = s
Set r = r.Offset(1, 0)
recsoln = r.Row - 1
End If
End FunctionPrivate Sub qsortd(v As Variant, lft As Long, rgt As Long)
'ad hoc quicksort subroutine
'translated from Aho, Weinberger amp; Kernighan,
'quot;The Awk Programming Languagequot;, page 161Dim j As Long, pvt As LongIf (lft gt;= rgt) Then Exit Sub
swap2 v, lft, lft Int((rgt - lft 1) * Rnd)
pvt = lft
For j = lft 1 To rgt
If v(j, 1) gt; v(lft, 1) Then
pvt = pvt 1
swap2 v, pvt, j
End If
Next jswap2 v, lft, pvtqsortd v, lft, pvt - 1
qsortd v, pvt 1, rgt
End SubPrivate Sub swap2(v As Variant, i As Long, j As Long)
'modified version of the swap procedure from
'translated from Aho, Weinberger amp; Kernighan,
'quot;The Awk Programming Languagequot;, page 161Dim t As Variant, k As LongFor k = LBound(v, 2) To UBound(v, 2)
t = v(i, k)
v(i, k) = v(j, k)
v(j, k) = t
Next k
End SubPrivate Sub swapo(a As Object, b As Object)
Dim t As ObjectSet t = a
Set a = b
Set b = t
End Sub
'---- end VBA code ----

quot;Joshua Jacobyquot; wrote:

gt; Hmm, well here's the situation. I'm trying to reconcile an account that has
gt; over 300 entries in it for the month of december. So, over 300 debits, but
gt; only say 100 credits. And I'm trying to each credit with the corresponding
gt; bunch of debits. It takes forever to try to do it visually by highlighting
gt; groups of debit entries to match their sum to a credit entry. You understand
gt; what I mean?
gt;
gt; quot;mricequot; wrote:
gt;
gt; gt;
gt; gt; Interesting question
gt; gt;
gt; gt; You could do this by using the DEC2BIN function to generate all of the
gt; gt; binary numbers up to two to the power of the count of the numbers that
gt; gt; you have. The individual places could then be used as switches for
gt; gt; whether each number is included in the sum or not. This would allow you
gt; gt; to evaluate all possible combinations and therefore see which matches
gt; gt; your target.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; mrice
gt; gt;
gt; gt; Research Scientist with many years of spreadsheet development experience
gt; gt; ------------------------------------------------------------------------
gt; gt; mrice's Profile: www.excelforum.com/member.php...oamp;userid=10931
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=540388
gt; gt;
gt; gt;

Here it is, this example was done on a small data set but you should be able
to use this technique, instead of 8 in this example you would put the first
credit than use the debits in let's say A2:A308, B2:B308 would have 1s and
so on

quot;put the data set in let's say A2:A8, in B2:B8 put a set of ones {1,1,1 etc}
in the adjacent cells
in C2 put 8, in D2 put=SUMPRODUCT(A2:A7,B2:B7)select D2 and do toolsgt;solver, set target cell $D$2 (should come up
automatically if selected)Equal to a Value of 8, by changing cells $B$2:$B$7, click add under Subject
to the constraints of:
in Cell reference put$B$2:$B$7from dropdown select Bin, click OK and click Solve, Keep solver solution
and look at the table2 1
4 0
5 0
6 1
9 0
13 0there you can see that 4 ones have been replaced by zeros and the adjacent
cells to the 2 ones total 8 quot;adapt to fitquot;--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;Joshua Jacobyquot; gt; wrote in message
...
gt; Hmm, I just tried that link and I was denied access to it by my work pc.
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; You can use solver,
gt;gt;
gt;gt; there was a similar question a few days ago, here's a link
gt;gt;
gt;gt; tinyurl.com/pfswm
gt;gt;
gt;gt;
gt;gt; note that the solver that comes with excel is limited in how large data
gt;gt; set
gt;gt; you can use
gt;gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Joshua Jacobyquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I have a long list of monetary amounts listed on an excel spreadsheet.
gt;gt; gt;I
gt;gt; gt;am
gt;gt; gt; trying to find combinations of those amounts that add up to certain
gt;gt; gt; larger
gt;gt; gt; sums. For example on a smaller scale:
gt;gt; gt; 10
gt;gt; gt; 15
gt;gt; gt; 21
gt;gt; gt; 41
gt;gt; gt; 53
gt;gt; gt; How can I find the cells that add up to 108? Is there a formula or
gt;gt; gt; function
gt;gt; gt; I can use on a large scale for this type of thing?
gt;gt;
gt;gt;
gt;gt;
Thank you Peo. Uggghh, I don't think my version has solver. it's not listed
under tools... Well, I guess I can't do it.

quot;Peo Sjoblomquot; wrote:

gt; Here it is, this example was done on a small data set but you should be able
gt; to use this technique, instead of 8 in this example you would put the first
gt; credit than use the debits in let's say A2:A308, B2:B308 would have 1s and
gt; so on
gt;
gt; quot;put the data set in let's say A2:A8, in B2:B8 put a set of ones {1,1,1 etc}
gt; in the adjacent cells
gt; in C2 put 8, in D2 put
gt;
gt;
gt; =SUMPRODUCT(A2:A7,B2:B7)
gt;
gt;
gt; select D2 and do toolsgt;solver, set target cell $D$2 (should come up
gt; automatically if selected)
gt;
gt;
gt; Equal to a Value of 8, by changing cells $B$2:$B$7, click add under Subject
gt; to the constraints of:
gt; in Cell reference put
gt;
gt;
gt; $B$2:$B$7
gt;
gt;
gt; from dropdown select Bin, click OK and click Solve, Keep solver solution
gt; and look at the table
gt;
gt;
gt; 2 1
gt; 4 0
gt; 5 0
gt; 6 1
gt; 9 0
gt; 13 0
gt;
gt;
gt; there you can see that 4 ones have been replaced by zeros and the adjacent
gt; cells to the 2 ones total 8 quot;
gt;
gt;
gt; adapt to fitquot;
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt; quot;Joshua Jacobyquot; gt; wrote in message
gt; ...
gt; gt; Hmm, I just tried that link and I was denied access to it by my work pc.
gt; gt;
gt; gt; quot;Peo Sjoblomquot; wrote:
gt; gt;
gt; gt;gt; You can use solver,
gt; gt;gt;
gt; gt;gt; there was a similar question a few days ago, here's a link
gt; gt;gt;
gt; gt;gt; tinyurl.com/pfswm
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; note that the solver that comes with excel is limited in how large data
gt; gt;gt; set
gt; gt;gt; you can use
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt;
gt; gt;gt; Regards,
gt; gt;gt;
gt; gt;gt; Peo Sjoblom
gt; gt;gt;
gt; gt;gt; nwexcelsolutions.com
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Joshua Jacobyquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I have a long list of monetary amounts listed on an excel spreadsheet.
gt; gt;gt; gt;I
gt; gt;gt; gt;am
gt; gt;gt; gt; trying to find combinations of those amounts that add up to certain
gt; gt;gt; gt; larger
gt; gt;gt; gt; sums. For example on a smaller scale:
gt; gt;gt; gt; 10
gt; gt;gt; gt; 15
gt; gt;gt; gt; 21
gt; gt;gt; gt; 41
gt; gt;gt; gt; 53
gt; gt;gt; gt; How can I find the cells that add up to 108? Is there a formula or
gt; gt;gt; gt; function
gt; gt;gt; gt; I can use on a large scale for this type of thing?
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

Wow, that's a long code. Where am I supposed to put that? I don't
understand any of it.

quot;Toppersquot; wrote:

gt;
gt; This code by Harlan Grove may help (I haven't tried it myself!). It finds
gt; numbers that add to a given sum.
gt;
gt; 'Begin VBA Code
gt;
gt;
gt; ' By Harlan Grove
gt;
gt;
gt; Sub findsums()
gt; 'This *REQUIRES* VBAProject references to
gt; 'Microsoft Scripting Runtime
gt; 'Microsoft VBScript Regular Expressions 1.0 or higher
gt;
gt;
gt; Const TOL As Double = 0.000001 'modify as needed
gt; Dim c As Variant
gt;
gt;
gt; Dim j As Long, k As Long, n As Long, p As Boolean
gt; Dim s As String, t As Double, u As Double
gt; Dim v As Variant, x As Variant, y As Variant
gt; Dim dc1 As New Dictionary, dc2 As New Dictionary
gt; Dim dcn As Dictionary, dco As Dictionary
gt; Dim re As New RegExp
gt;
gt;
gt; re.Global = True
gt; re.IgnoreCase = True
gt;
gt;
gt; On Error Resume Next
gt;
gt;
gt; Set x = Application.InputBox( _
gt; Prompt:=quot;Enter range of values:quot;, _
gt; Title:=quot;findsumsquot;, _
gt; Default:=quot;quot;, _
gt; Type:=8 _
gt; )
gt;
gt;
gt; If x Is Nothing Then
gt; Err.Clear
gt; Exit Sub
gt; End If
gt;
gt;
gt; y = Application.InputBox( _
gt; Prompt:=quot;Enter target value:quot;, _
gt; Title:=quot;findsumsquot;, _
gt; Default:=quot;quot;, _
gt; Type:=1 _
gt; )
gt;
gt;
gt; If VarType(y) = vbBoolean Then
gt; Exit Sub
gt; Else
gt; t = y
gt; End If
gt;
gt;
gt; On Error GoTo 0
gt;
gt;
gt; Set dco = dc1
gt; Set dcn = dc2
gt;
gt;
gt; Call recsoln
gt;
gt;
gt; For Each y In x.Value2
gt; If VarType(y) = vbDouble Then
gt; If Abs(t - y) lt; TOL Then
gt; recsoln quot; quot; amp; Format(y)
gt;
gt;
gt; ElseIf dco.Exists(y) Then
gt; dco(y) = dco(y) 1
gt;
gt;
gt; ElseIf y lt; t - TOL Then
gt; dco.Add Key:=y, Item:=1
gt;
gt;
gt; c = CDec(c 1)
gt; Application.StatusBar = quot;[1] quot; amp; Format(c)
gt;
gt;
gt; End If
gt;
gt;
gt; End If
gt; Next y
gt;
gt;
gt; n = dco.Count
gt;
gt;
gt; ReDim v(1 To n, 1 To 3)
gt;
gt;
gt; For k = 1 To n
gt; v(k, 1) = dco.Keys(k - 1)
gt; v(k, 2) = dco.Items(k - 1)
gt; Next k
gt;
gt;
gt; qsortd v, 1, n
gt;
gt;
gt; For k = n To 1 Step -1
gt; v(k, 3) = v(k, 1) * v(k, 2) v(IIf(k = n, n, k 1), 3)
gt; If v(k, 3) gt; t Then dcn.Add Key:=quot; quot; amp; _
gt; Format(v(k, 1)), Item:=v(k, 1)
gt; Next k
gt;
gt;
gt; On Error GoTo CleanUp
gt; Application.EnableEvents = False
gt; Application.Calculation = xlCalculationManual
gt;
gt;
gt; For k = 2 To n
gt; dco.RemoveAll
gt; swapo dco, dcn
gt;
gt;
gt; For Each y In dco.Keys
gt; p = False
gt;
gt;
gt; For j = 1 To n
gt; If v(j, 3) lt; t - dco(y) - TOL Then Exit For
gt; x = v(j, 1)
gt; s = quot; quot; amp; Format(x)
gt; If Right(y, Len(s)) = s Then p = True
gt; If p Then
gt; re.Pattern = quot;\quot; amp; s amp; quot;(?=(\ |$))quot;
gt; If re.Execute(y).Count lt; v(j, 2) Then
gt; u = dco(y) x
gt; If Abs(t - u) lt; TOL Then
gt; recsoln y amp; s
gt; ElseIf u lt; t - TOL Then
gt; dcn.Add Key:=y amp; s, Item:=u
gt; c = CDec(c 1)
gt; Application.StatusBar = quot;[quot; amp; Format(k) amp; quot;] quot; amp; _
gt; Format(c)
gt; End If
gt; End If
gt; End If
gt; Next j
gt; Next y
gt;
gt;
gt; If dcn.Count = 0 Then Exit For
gt; Next k
gt;
gt;
gt; If (recsoln() = 0) Then _
gt; MsgBox Prompt:=quot;all combinations exhaustedquot;, _
gt; Title:=quot;No Solutionquot;
gt;
gt;
gt; CleanUp:
gt; Application.EnableEvents = True
gt; Application.Calculation = xlCalculationAutomatic
gt; Application.StatusBar = False
gt;
gt;
gt; End Sub
gt;
gt;
gt; Private Function recsoln(Optional s As String)
gt; Const OUTPUTWSN As String = quot;findsums solutionsquot; 'modify to taste
gt;
gt;
gt; Static r As Range
gt; Dim ws As Worksheet
gt;
gt;
gt; If s = quot;quot; And r Is Nothing Then
gt; On Error Resume Next
gt; Set ws = ActiveWorkbook.Worksheets(OUTPUTWSN)
gt; If ws Is Nothing Then
gt; Err.Clear
gt; Application.ScreenUpdating = False
gt; Set ws = ActiveSheet
gt; Set r = Worksheets.Add.Range(quot;A1quot;)
gt; r.Parent.Name = OUTPUTWSN
gt; ws.Activate
gt; Application.ScreenUpdating = False
gt; Else
gt; ws.Cells.Clear
gt; Set r = ws.Range(quot;A1quot;)
gt; End If
gt; recsoln = 0
gt; ElseIf s = quot;quot; Then
gt; recsoln = r.Row - 1
gt; Set r = Nothing
gt; Else
gt; r.Value = s
gt; Set r = r.Offset(1, 0)
gt; recsoln = r.Row - 1
gt; End If
gt; End Function
gt;
gt;
gt; Private Sub qsortd(v As Variant, lft As Long, rgt As Long)
gt; 'ad hoc quicksort subroutine
gt; 'translated from Aho, Weinberger amp; Kernighan,
gt; 'quot;The Awk Programming Languagequot;, page 161
gt;
gt;
gt; Dim j As Long, pvt As Long
gt;
gt;
gt; If (lft gt;= rgt) Then Exit Sub
gt; swap2 v, lft, lft Int((rgt - lft 1) * Rnd)
gt; pvt = lft
gt; For j = lft 1 To rgt
gt; If v(j, 1) gt; v(lft, 1) Then
gt; pvt = pvt 1
gt; swap2 v, pvt, j
gt; End If
gt; Next j
gt;
gt;
gt; swap2 v, lft, pvt
gt;
gt;
gt; qsortd v, lft, pvt - 1
gt; qsortd v, pvt 1, rgt
gt; End Sub
gt;
gt;
gt; Private Sub swap2(v As Variant, i As Long, j As Long)
gt; 'modified version of the swap procedure from
gt; 'translated from Aho, Weinberger amp; Kernighan,
gt; 'quot;The Awk Programming Languagequot;, page 161
gt;
gt;
gt; Dim t As Variant, k As Long
gt;
gt;
gt; For k = LBound(v, 2) To UBound(v, 2)
gt; t = v(i, k)
gt; v(i, k) = v(j, k)
gt; v(j, k) = t
gt; Next k
gt; End Sub
gt;
gt;
gt; Private Sub swapo(a As Object, b As Object)
gt; Dim t As Object
gt;
gt;
gt; Set t = a
gt; Set a = b
gt; Set b = t
gt; End Sub
gt; '---- end VBA code ----
gt;
gt;
gt;
gt;
gt; quot;Joshua Jacobyquot; wrote:
gt;
gt; gt; Hmm, well here's the situation. I'm trying to reconcile an account that has
gt; gt; over 300 entries in it for the month of december. So, over 300 debits, but
gt; gt; only say 100 credits. And I'm trying to each credit with the corresponding
gt; gt; bunch of debits. It takes forever to try to do it visually by highlighting
gt; gt; groups of debit entries to match their sum to a credit entry. You understand
gt; gt; what I mean?
gt; gt;
gt; gt; quot;mricequot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; Interesting question
gt; gt; gt;
gt; gt; gt; You could do this by using the DEC2BIN function to generate all of the
gt; gt; gt; binary numbers up to two to the power of the count of the numbers that
gt; gt; gt; you have. The individual places could then be used as switches for
gt; gt; gt; whether each number is included in the sum or not. This would allow you
gt; gt; gt; to evaluate all possible combinations and therefore see which matches
gt; gt; gt; your target.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; mrice
gt; gt; gt;
gt; gt; gt; Research Scientist with many years of spreadsheet development experience
gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; mrice's Profile: www.excelforum.com/member.php...oamp;userid=10931
gt; gt; gt; View this thread: www.excelforum.com/showthread...hreadid=540388
gt; gt; gt;
gt; gt; gt;

I have Excel 2000

quot;Joshua Jacobyquot; wrote:

gt; Thank you Peo. Uggghh, I don't think my version has solver. it's not listed
gt; under tools... Well, I guess I can't do it.
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt; gt; Here it is, this example was done on a small data set but you should be able
gt; gt; to use this technique, instead of 8 in this example you would put the first
gt; gt; credit than use the debits in let's say A2:A308, B2:B308 would have 1s and
gt; gt; so on
gt; gt;
gt; gt; quot;put the data set in let's say A2:A8, in B2:B8 put a set of ones {1,1,1 etc}
gt; gt; in the adjacent cells
gt; gt; in C2 put 8, in D2 put
gt; gt;
gt; gt;
gt; gt; =SUMPRODUCT(A2:A7,B2:B7)
gt; gt;
gt; gt;
gt; gt; select D2 and do toolsgt;solver, set target cell $D$2 (should come up
gt; gt; automatically if selected)
gt; gt;
gt; gt;
gt; gt; Equal to a Value of 8, by changing cells $B$2:$B$7, click add under Subject
gt; gt; to the constraints of:
gt; gt; in Cell reference put
gt; gt;
gt; gt;
gt; gt; $B$2:$B$7
gt; gt;
gt; gt;
gt; gt; from dropdown select Bin, click OK and click Solve, Keep solver solution
gt; gt; and look at the table
gt; gt;
gt; gt;
gt; gt; 2 1
gt; gt; 4 0
gt; gt; 5 0
gt; gt; 6 1
gt; gt; 9 0
gt; gt; 13 0
gt; gt;
gt; gt;
gt; gt; there you can see that 4 ones have been replaced by zeros and the adjacent
gt; gt; cells to the 2 ones total 8 quot;
gt; gt;
gt; gt;
gt; gt; adapt to fitquot;
gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Regards,
gt; gt;
gt; gt; Peo Sjoblom
gt; gt;
gt; gt; nwexcelsolutions.com
gt; gt;
gt; gt;
gt; gt; quot;Joshua Jacobyquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hmm, I just tried that link and I was denied access to it by my work pc.
gt; gt; gt;
gt; gt; gt; quot;Peo Sjoblomquot; wrote:
gt; gt; gt;
gt; gt; gt;gt; You can use solver,
gt; gt; gt;gt;
gt; gt; gt;gt; there was a similar question a few days ago, here's a link
gt; gt; gt;gt;
gt; gt; gt;gt; tinyurl.com/pfswm
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt; note that the solver that comes with excel is limited in how large data
gt; gt; gt;gt; set
gt; gt; gt;gt; you can use
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt; --
gt; gt; gt;gt;
gt; gt; gt;gt; Regards,
gt; gt; gt;gt;
gt; gt; gt;gt; Peo Sjoblom
gt; gt; gt;gt;
gt; gt; gt;gt; nwexcelsolutions.com
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt; quot;Joshua Jacobyquot; gt; wrote in message
gt; gt; gt;gt; ...
gt; gt; gt;gt; gt;I have a long list of monetary amounts listed on an excel spreadsheet.
gt; gt; gt;gt; gt;I
gt; gt; gt;gt; gt;am
gt; gt; gt;gt; gt; trying to find combinations of those amounts that add up to certain
gt; gt; gt;gt; gt; larger
gt; gt; gt;gt; gt; sums. For example on a smaller scale:
gt; gt; gt;gt; gt; 10
gt; gt; gt;gt; gt; 15
gt; gt; gt;gt; gt; 21
gt; gt; gt;gt; gt; 41
gt; gt; gt;gt; gt; 53
gt; gt; gt;gt; gt; How can I find the cells that add up to 108? Is there a formula or
gt; gt; gt;gt; gt; function
gt; gt; gt;gt; gt; I can use on a large scale for this type of thing?
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt;
gt; gt;
gt; gt;