Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiplying combinations
If I have a list of 8 numbers and want all possible products of all 3 number
combinations, how do I set this up? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiplying combinations
Add this function to your workbook: Code: -------------------- Public Function NextComboWF(vInx As Variant, n As Long) As Long() ' Worksheet version ' Example usage for 8 choose 4: ' A B C D ' 1 2 3 4 ' literals for first combination ' 1 2 3 5 ' = NextComboWF(A2:D2, 8) array-entered and copied down ' Returns the next combination in lexical order Dim aiInx() As Long Dim i As Long Dim m As Long m = WorksheetFunction.Count(vInx) Dim bWrap As Boolean ReDim aiInx(1 To m) For i = 1 To m aiInx(i) = vInx(i) Next ' set initial combo if empty If aiInx(1) = 0 Then For i = 1 To m aiInx(i) = i Next i NextComboWF = aiInx Exit Function End If ' find rightmost incrementable index For i = m To 1 Step -1 If aiInx(i) < n - m + i Then Exit For Next i If i = 0 Then bWrap = True i = 1 aiInx(1) = 0 End If ' set 'righter' indices sequentially beyond aiInx(i) = aiInx(i) + 1 For i = i + 1 To m aiInx(i) = aiInx(i - 1) + 1 Next NextComboWF = aiInx End Function -------------------- In A1, B1, and C1, enter 1, 2, and 3 respectively. In A2:C2, array enter =NextComboWF(A1:C1, 8) and copy down to row 56. (Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.) -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27126 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiplying combinations
Generate combinations at
http://theory.cs.uvic.ca/~cos/amof/ Copy into Excel 2007. Easy formulas and no code to write or maintain: http://www.savefile.com/files/1878334 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get rid of #VALUE, after multiplying | Excel Worksheet Functions | |||
Multiplying | Excel Worksheet Functions | |||
Permutation combinations and multiplying them | Excel Worksheet Functions | |||
Multiplying | Excel Discussion (Misc queries) | |||
Multiplying in a row | Excel Discussion (Misc queries) |