MartyConnelly
martyconnelly at shaw.ca
Wed Oct 15 23:35:06 CDT 2003
With six objects A,B,C,D,E,F you would have 20 unique combinations without regard to order. Try code below to calculate the number of objects you can reasonably use, the calculation fails on overflow when n > 170 There are several methods to list out these combinations, in one of Knuth's programming textbooks. I believe the third volume. I haven't got a copy handy, it's in another province. TAOCP should be required reading. http://www-cs-staff.stanford.edu/~knuth/taocp.html Counting combinations - example How many different groups of 3 could be selected from the 5 objects A, B, C, D, E? 5 ways to select the first, 4 to then select the second, and 3 ways to select the third, so 5*4*3 ways to choose when order matters But, every group of 3 (e.g. A, B, C) is counted 6 (=3!) times: ABC, ACB, BAC, BCA, CAB, CBA So, total number of groups when ignore order is 5*4*3/(3*2*1) = 10 5! / 3!(5-3)! 120/6*2 =10 Counting combinations In general, the number of different groups of r objects out of n is n* (n-1) * ... * (n-r+1)/r! = n!/( r! (n-r)! ) This number is written ( nr ), and is pronounced ‘n choose r’ Example: How many 5 card poker hands are possible (out of a deck of 52 cards)? There are ( 525 ) = 2,598,960 poker hands Public Function Combination(FromN As Long, SelectR As Long) As Double On Error GoTo ErrorHandler '********************************************************************************************************** ' 'Purpose: Algorithm that calculates the arrangement of R objects selected from N objects without regard to order. ' 'Argument(s): SelectR - long integer number, FromN - long integer number. ' 'Source: http://www.paretoanalysts.com ' 'Author: Pareto Analysts ' 'Creation Date: December 1, 2001 ' 'Description: Calculates the number of possible arrangements of (SelectR) items chosen from (FromN) items. ' without regard to order. When an arrangement is selected, it cannot be rearranged to mean ' something else. ' nCr = n! / (r!*(n-r)!) ' nCr = (FromN)! / (SelectR)!*(FromN - SelectR)! ' 'Return Value: The number of possible arrangements of one item with respect to another item without regard to order. ' 'Return Type: Double ' 'Limitation: The Largest Number that can be passed is 170 corresponding to 170!(7.25741561530799E+306) ' Combination = Factorial(FromN) / ((Factorial(SelectR)) * Factorial(FromN - SelectR)) Exit_ErrorHandler: Exit Function ErrorHandler: MsgBox Err.Number & " " & Err.Description, vbInformation, "www.paretoanalysts.com Combination " Resume Exit_ErrorHandler End Function Private Function Factorial(n As Long) As Double On Error GoTo ErrorHandler '********************************************************************************************************** ' 'Purpose: Algorithm that calculates the Factorial of Numbers (N!) up to 170. ' 'Argument(s): N - long integer number less than 171. ' 'Source: http://www.paretoanalysts.com ' 'Author: Pareto Analysts ' 'Creation Date: December 1, 2001 ' 'Description: Calculates the Factorial of a Number. N! can be computed as N*(N-1)*(N-2)...(N-(N-1))*2*1 ' E.g. 5! = 5*4*3*2*1 ' 'Return Value: The Factorial of the Number passed to it. ' 'Return Type: Double ' 'Limitation: The Largest Number that can be passed is 170 corresponding to 170!(7.25741561530799E+306) ' ' 'Warranty : No claim is made as to the accuracy or fitness of this algorithm. The use of this ' algorithm is at your own risk and choice and the author is not liable in anyway ' for its use or damages that may occur as a result of its use. ' 'Copyright: The use or distribution of this algorithm is free provided that it is ' used or distributed along with this header notice and this header notice ' has not been modified in any way. If this algorithm is used in a compiled form then ' the header notice must also be placed in a readme.txt file ' 'Inquiries: Inquiries about this algorithm, data mining, statistical analysis and data analysis ' can be directed to http://www.paretoanalysts.com ' '********************************************************************************************************** If n < 0 Then Factorial = 0 ElseIf n = 0 Then Factorial = 1 Else Factorial = n * Factorial(n - 1) End If Exit_ErrorHandler: Exit Function ErrorHandler: MsgBox Err.Number & " " & Err.Description, vbInformation, "www.paretoanalysts.com Factorial " Resume Exit_ErrorHandler End Function Gustav Brock wrote: >Hi Lonnie > >So a maximum would be 10 rows? >Like Jim, out of curiosity, what is this accounting feature? Balancing >of payments or bank transactions? > >/gustav > > > > >>Thank you Gustav, >> >> > > > >>The number of rows may vary with each client. This will be an accounting feature. There will be a client number and that's how I will group. A client could have one record or ten records. I was >>looking for something efficient and something that I could load a couple of variables which would be the number of records and an array of the values. >> >> > > > >>Thanks again. >> >> > > > >>Gustav Brock <gustav at cactus.dk> wrote: >>Hi Lonnie >> >> > > > >>If you only have a few rows as shown I don't think you have any other >>possibility than trying the different combinations using some loops. >> >> > > > >>You could, of course, start by ruling out some of the impossible >>combinations by adding all the negatives and all the positives; here >>it will return -5 and +18 which shows that 7 can be ruled out as it is >>larger than the absolute value of the sum of negatives. On the other >>hand, -2 and -3 must be included in any combination as the absolute >>value of the sum of these equals the smallest positive number, 5. >> >> > > > >>If you have many rows you'll have to find some smarter routines as the >>number of combinations increase dramatically. >> >> > > > >>/gustav >> >> > > > > >>>This is one for the math guys who code. I have a situation where I need to take a field in a group of records and see if any combination of the values in the field equal zero. >>> >>> > > > >>>Example: >>> >>> > > > >>>MyField >>>5 >>>-2 >>>7 >>>-3 >>>6 >>> >>> > > > >>>This group of records would have a combination that equals zero (5, -2, -3). I hope someone has something. >>> >>> > >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > > > -- Marty Connelly Victoria, B.C. Canada