ExcelBanter - I need a formula that equals a given #

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - I need a formula that equals a given # (https://www.excelbanter.com/excel-programming/279799-i-need-formula-equals-given.html)

I need a formula that equals a given #

Does anyone know how to create a formula with 5 sets of numbers that
have to equal another number? For example, if
Set 1 = (1,2,3)
Set 2 = (4,5,6)
Set 3 = (7,8,9)
Set 4 = (10,11,12)
Set 5 = (13,14,15)

I would want the formula to pick one number from each set so that the
sum of these numbers would equal 40. Is there a way to write a formula
in Excel that would create all of the possible number combinations
(using the 5 sets) that equal a given number?

Thanks in advance for your help!

Spencer

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I need a formula that equals a given #

Sorry, I missed a line out.
add
End If
before
Next fifth

Henry

"Henry" wrote in message
...
Spencer,

Here's one way

Assuming that Set 1 is in A1, B1 & C1 and Set 2 is in A2, B2 & C2 etc.,
etc.,
and your list of matches goes into D1 to H1 for the first match, D2 to H2
for the second match, etc., etc.

counter = 1
For first = 1 To 3
For second = 1 To 3
For third = 1 To 3
For fourth = 1 To 3
For fifth = 1 To 3
If Cells(1, first).Value + Cells(2, second).Value + Cells(3, third).Value
_
+ Cells(4, fourth).Value + Cells(5, fifth).Value = 40 Then
'Found a match
Cells(counter, 4).Value = Cells(1, first).Value
Cells(counter, 5).Value = Cells(2, second).Value
Cells(counter, 6).Value = Cells(3, third).Value
Cells(counter, 7).Value = Cells(4, fourth).Value
Cells(counter, 8).Value = Cells(5, fifth).Value
counter = counter + 1
Next fifth
Next fourth
Next third
Next second
Next first

Not very elegant or fast, but it'll do the job for you.

HTH
Henry

"Spencer G." wrote in message
...
Does anyone know how to create a formula with 5 sets of numbers that
have to equal another number? For example, if
Set 1 = (1,2,3)
Set 2 = (4,5,6)
Set 3 = (7,8,9)
Set 4 = (10,11,12)
Set 5 = (13,14,15)

I would want the formula to pick one number from each set so that the
sum of these numbers would equal 40. Is there a way to write a formula
in Excel that would create all of the possible number combinations
(using the 5 sets) that equal a given number?

Thanks in advance for your help!

Spencer

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

I need a formula that equals a given #

Here is just a slightly different technique to your excellent idea...

Sub test()

Dim Set1, Set2, Set3, Set4, Set5
Dim result As Long
Dim i As Long, j As Long, k As Long, l As Long, m As Long

Set1 = Array(, 1, 2, 3)
Set2 = Array(, 4, 5, 6)
Set3 = Array(, 7, 8, 9)
Set4 = Array(, 10, 11, 12)
Set5 = Array(, 13, 14, 15)

For i = 1 To 3
For j = 1 To 3
For k = 1 To 3
For l = 1 To 3
For m = 1 To 3
result = Set1(i) + Set2(j) + Set3(k) + Set4(l) + Set5(m)
If result = 40 Then Debug.Print Set1(i); Set2(j);
Set3(k); Set4(l); Set5(m); result
Next m
Next l
Next k
Next j
Next i

End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

"Doug Glancy" wrote in message
...
Spencer,

I look forward to seeing more concise answers (I don't use arrays much),
but
this seems to work:

Sub test()

Dim Set1(1 To 3), Set2(1 To 3), Set3(1 To 3), Set4(1 To 3), Set5(1 To 3),
result As Long
Dim i, j, k, l, m As Integer

Set1(1) = (1)
Set1(2) = (2)
Set1(3) = (3)
Set2(1) = (4)
Set2(2) = (5)
Set2(3) = (6)
Set3(1) = (7)
Set3(2) = (8)
Set3(3) = (9)
Set4(1) = (10)
Set4(2) = (11)
Set4(3) = (12)
Set5(1) = (13)
Set5(2) = (14)
Set5(3) = (15)

For i = 1 To 3
For j = 1 To 3
For k = 1 To 3
For l = 1 To 3
For m = 1 To 3
result = Set1(i) + Set2(j) + Set3(k) + Set4(l) +
Set5(m)
If result = 40 Then Debug.Print Set1(i); Set2(j);
Set3(k); Set4(l); Set5(m); result
Next m
Next l
Next k
Next j
Next i

End Sub

"Spencer G." wrote in message
...
Does anyone know how to create a formula with 5 sets of numbers that
have to equal another number? For example, if
Set 1 = (1,2,3)
Set 2 = (4,5,6)
Set 3 = (7,8,9)
Set 4 = (10,11,12)
Set 5 = (13,14,15)

I would want the formula to pick one number from each set so that the
sum of these numbers would equal 40. Is there a way to write a formula
in Excel that would create all of the possible number combinations
(using the 5 sets) that equal a given number?

Thanks in advance for your help!

Spencer

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/