[AccessD] Math Problem

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





More information about the AccessD mailing list