I need a formula that equals a given #

  • Thread starter Thread starter Spencer G.
  • Start date Start date
S

Spencer G.

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
 
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,

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
 
Sorry, I missed a line out.
add
End If
before
Next fifth

Henry

Henry said:
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
Click to expand...
 
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 said:
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
Click to expand...
 
Back
Top