Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination Determination?
Hi, I was wondering if anyone had written, or can think of an easy
algorithm which can determine what combinations of units will fall in a certain range. For instance, if I gave it sections of flooring that were 1m, 2.5m, 4m, and asked for all combinations which would fall between 4.5m & 5m, it would return an array with 5x1m,1x2.5m + 2x1m, 1x4m + 1x1m. Thanks, Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination Determination?
Jeffrey
Interesting problem. Here's a formula that uses an iterative algorithm to determine the combinations. It's seems efficient but perhaps could still be improved on. The coding isn't really best practice, and should at least have some validation of arguments and some comments. Also, the string it produces will show '1+1+1+1+1' rather than '5x1' - it could be changed with a little more effort JB Public Function UnitCombo(ByRef sgMin As Single, ByRef sgMax As Single, ByRef varUnits As Variant, ByRef stCombos As String, Optional ByRef stCurrentCombo As String, Optional ByVal iCurrentPlace As Integer) As Strin Dim i As Intege Dim sgUnit As Singl For i = iCurrentPlace To UBound(varUnits sgUnit = varUnits(i If sgUnit < sgMin The Call UnitCombo(sgMin - sgUnit, sgMax - sgUnit, varUnits, stCombos, stCurrentCombo & sgUnit & "+", i ElseIf sgUnit <= sgMax The If stCombos = "" The stCombos = stCurrentCombo & sgUni Els stCombos = stCombos & "," & stCurrentCombo & sgUni End I Call UnitCombo(sgMin - sgUnit, sgMax - sgUnit, varUnits, stCombos, stCurrentCombo & sgUnit & "+", i End I Next End Functio Sub Test( Dim stCombos As Strin Call UnitCombo(4.5, 5, Array(1, 2.5, 4), stCombos 'the list of possible units can be as long as you like but must be passed as '0-based, 1-Dimensional Variant Array, so 'Array(a,b,c,d,e,...)' works well 'stCombos is passed ByRef & it's value is changed by the Function to return the result 'In this example, stCombos = "1+1+1+1+1,1+1+2.5,1+4,2.5+2.5" afte 'the function has been called Sto End Su ----- Jeffrey Marcellus wrote: ---- Hi, I was wondering if anyone had written, or can think of an eas algorithm which can determine what combinations of units will fall in certain range. For instance, if I gave it sections of flooring that were 1m 2.5m, 4m, and asked for all combinations which would fall between 4.5m & 5m it would return an array with 5x1m,1x2.5m + 2x1m, 1x4m + 1x1m Thanks, Jef |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
coefficient of determination | Excel Worksheet Functions | |||
Date determination and sums | Excel Worksheet Functions | |||
Earned Value Determination | Excel Discussion (Misc queries) | |||
Average determination | Excel Worksheet Functions | |||
excluding a value in a max determination | Excel Programming |