Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
Hi everyone,
I am trying to loop through ALL the combinations and count the number of occurances of the last digit for each of the combinations. There are 10 categories of last digit :- 111111 2887500 211110 6930000 221100 2772000 222000 105600 311100 924000 321000 316800 330000 3960 411000 39600 420000 3960 510000 396 Total = 13983816 111111 means there are 2,887,500 combinations with all last digits different. 321000 means there are 316,800 combinations where 3 of the last digits are the same, 2 of the last digits are the same (but a different last digit to the 3) and 1 last digit (but a different last digit to the 3 or 2). Here is what I have so far :- Option Explicit Dim A As Long, B As Long, C As Long, D As Long, E As Long, F As Long Sub LastDigit() Dim i As Integer Dim LastDigit As Integer Dim DigitCounts(0 To 9) As Integer ' This will hold counters for each digit 0-9 Dim nDupl As Integer Const minVal As Integer = 1 ' The minimum value in ANY combination Const maxVal As Integer = 49 ' The maximum value in ANY combination Application.ScreenUpdating = False For i = 0 To 9 DigitCounts(i) = 0 Next i For A = minVal To maxVal - 5 For B = A + 1 To maxVal - 4 For C = B + 1 To maxVal - 3 For D = C + 1 To maxVal - 2 For E = D + 1 To maxVal - 1 For F = E + 1 To maxVal For i = 0 To 4 LastDigit = i - 10 * Int(i) / 10 DigitCounts(LastDigit) = DigitCounts(LastDigit) + 1 Next i nDupl = 0 For i = 0 To 9 If DigitCounts(i) 1 Then nDupl = nDupl + DigitCounts(i) Next i Next F Next E Next D Next C Next B Next A ActiveCell.Offset(0, 5).Value = nDupl ActiveCell.Offset(1, 0).Select Application.ScreenUpdating = True End Sub Any help will be greatly appreciated. Thanks in Advance. All the Best. Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
I don't follow this at all
"111111 means there are 2,887,500 combinations with all last digits different." Why ? What's a combination ? What's a category ? 321000 means there are 316,800 combinations where 3 of the last digits are the same, 2 of the last digits are the same (but a different last digit to the 3) and 1 last digit (but a different last digit to the 3 or 2). I defy any one to make sense of that ! Why is 6930000 more than 2887500, seems out of sequence ? In passing, looks like you'll be dealing with numbers over 32k. If potentially so you should declare them 'As Long' to avoid overflows (As Integer in VB/VBA is virtually redundant in 32bit systems). Also FWIW, no need to disable screen updating for the sake of populating just two cells. Regards, Peter T "Paul Black" wrote in message ups.com... Hi everyone, I am trying to loop through ALL the combinations and count the number of occurances of the last digit for each of the combinations. There are 10 categories of last digit :- 111111 2887500 211110 6930000 221100 2772000 222000 105600 311100 924000 321000 316800 330000 3960 411000 39600 420000 3960 510000 396 Total = 13983816 111111 means there are 2,887,500 combinations with all last digits different. 321000 means there are 316,800 combinations where 3 of the last digits are the same, 2 of the last digits are the same (but a different last digit to the 3) and 1 last digit (but a different last digit to the 3 or 2). Here is what I have so far :- Option Explicit Dim A As Long, B As Long, C As Long, D As Long, E As Long, F As Long Sub LastDigit() Dim i As Integer Dim LastDigit As Integer Dim DigitCounts(0 To 9) As Integer ' This will hold counters for each digit 0-9 Dim nDupl As Integer Const minVal As Integer = 1 ' The minimum value in ANY combination Const maxVal As Integer = 49 ' The maximum value in ANY combination Application.ScreenUpdating = False For i = 0 To 9 DigitCounts(i) = 0 Next i For A = minVal To maxVal - 5 For B = A + 1 To maxVal - 4 For C = B + 1 To maxVal - 3 For D = C + 1 To maxVal - 2 For E = D + 1 To maxVal - 1 For F = E + 1 To maxVal For i = 0 To 4 LastDigit = i - 10 * Int(i) / 10 DigitCounts(LastDigit) = DigitCounts(LastDigit) + 1 Next i nDupl = 0 For i = 0 To 9 If DigitCounts(i) 1 Then nDupl = nDupl + DigitCounts(i) Next i Next F Next E Next D Next C Next B Next A ActiveCell.Offset(0, 5).Value = nDupl ActiveCell.Offset(1, 0).Select Application.ScreenUpdating = True End Sub Any help will be greatly appreciated. Thanks in Advance. All the Best. Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
Hi peter,
They are 6 number combinations. So for 6 numbers from 49 numbers there are 13,983,816 total combinations. 111111 could be numbers 01 02 03 04 05 06, making ALL 6 last digits different. 321000 could be numbers 01 11 21 30 40 49. The full list of categories are :- 111111 211110 221100 222000 311100 321000 330000 411000 420000 510000 The program will hopefully calculate the the total combinations for ALL the categories and list them one under the other. Thanks in Advance. All the Best. Paul On Oct 5, 10:59 am, "Peter T" <peter_t@discussions wrote: I don't follow this at all "111111 means there are 2,887,500 combinations with all last digits different." Why ? What's a combination ? What's a category ? 321000 means there are 316,800 combinations where 3 of the last digits are the same, 2 of the last digits are the same (but a different last digit to the 3) and 1 last digit (but a different last digit to the 3 or 2). I defy any one to make sense of that ! Why is 6930000 more than 2887500, seems out of sequence ? In passing, looks like you'll be dealing with numbers over 32k. If potentially so you should declare them 'As Long' to avoid overflows (As Integer in VB/VBA is virtually redundant in 32bit systems). Also FWIW, no need to disable screen updating for the sake of populating just two cells. Regards, Peter T "Paul Black" wrote in message ups.com... Hi everyone, I am trying to loop through ALL the combinations and count the number of occurances of the last digit for each of the combinations. There are 10 categories of last digit :- 111111 2887500 211110 6930000 221100 2772000 222000 105600 311100 924000 321000 316800 330000 3960 411000 39600 420000 3960 510000 396 Total = 13983816 111111 means there are 2,887,500 combinations with all last digits different. 321000 means there are 316,800 combinations where 3 of the last digits are the same, 2 of the last digits are the same (but a different last digit to the 3) and 1 last digit (but a different last digit to the 3 or 2). Here is what I have so far :- Option Explicit Dim A As Long, B As Long, C As Long, D As Long, E As Long, F As Long Sub LastDigit() Dim i As Integer Dim LastDigit As Integer Dim DigitCounts(0 To 9) As Integer ' This will hold counters for each digit 0-9 Dim nDupl As Integer Const minVal As Integer = 1 ' The minimum value in ANY combination Const maxVal As Integer = 49 ' The maximum value in ANY combination Application.ScreenUpdating = False For i = 0 To 9 DigitCounts(i) = 0 Next i For A = minVal To maxVal - 5 For B = A + 1 To maxVal - 4 For C = B + 1 To maxVal - 3 For D = C + 1 To maxVal - 2 For E = D + 1 To maxVal - 1 For F = E + 1 To maxVal For i = 0 To 4 LastDigit = i - 10 * Int(i) / 10 DigitCounts(LastDigit) = DigitCounts(LastDigit) + 1 Next i nDupl = 0 For i = 0 To 9 If DigitCounts(i) 1 Then nDupl = nDupl + DigitCounts(i) Next i Next F Next E Next D Next C Next B Next A ActiveCell.Offset(0, 5).Value = nDupl ActiveCell.Offset(1, 0).Select Application.ScreenUpdating = True End Sub Any help will be greatly appreciated. Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
I've narrowed it down to a few different interpretations of what you might
mean. But you win - I give up! I'm curious now to see if someone else can understand <g Regards, Peter T "Paul Black" wrote in message oups.com... Hi peter, They are 6 number combinations. So for 6 numbers from 49 numbers there are 13,983,816 total combinations. 111111 could be numbers 01 02 03 04 05 06, making ALL 6 last digits different. 321000 could be numbers 01 11 21 30 40 49. The full list of categories are :- 111111 211110 221100 222000 311100 321000 330000 411000 420000 510000 The program will hopefully calculate the the total combinations for ALL the categories and list them one under the other. Thanks in Advance. All the Best. Paul On Oct 5, 10:59 am, "Peter T" <peter_t@discussions wrote: I don't follow this at all "111111 means there are 2,887,500 combinations with all last digits different." Why ? What's a combination ? What's a category ? 321000 means there are 316,800 combinations where 3 of the last digits are the same, 2 of the last digits are the same (but a different last digit to the 3) and 1 last digit (but a different last digit to the 3 or 2). I defy any one to make sense of that ! Why is 6930000 more than 2887500, seems out of sequence ? In passing, looks like you'll be dealing with numbers over 32k. If potentially so you should declare them 'As Long' to avoid overflows (As Integer in VB/VBA is virtually redundant in 32bit systems). Also FWIW, no need to disable screen updating for the sake of populating just two cells. Regards, Peter T "Paul Black" wrote in message ups.com... Hi everyone, I am trying to loop through ALL the combinations and count the number of occurances of the last digit for each of the combinations. There are 10 categories of last digit :- 111111 2887500 211110 6930000 221100 2772000 222000 105600 311100 924000 321000 316800 330000 3960 411000 39600 420000 3960 510000 396 Total = 13983816 111111 means there are 2,887,500 combinations with all last digits different. 321000 means there are 316,800 combinations where 3 of the last digits are the same, 2 of the last digits are the same (but a different last digit to the 3) and 1 last digit (but a different last digit to the 3 or 2). Here is what I have so far :- Option Explicit Dim A As Long, B As Long, C As Long, D As Long, E As Long, F As Long Sub LastDigit() Dim i As Integer Dim LastDigit As Integer Dim DigitCounts(0 To 9) As Integer ' This will hold counters for each digit 0-9 Dim nDupl As Integer Const minVal As Integer = 1 ' The minimum value in ANY combination Const maxVal As Integer = 49 ' The maximum value in ANY combination Application.ScreenUpdating = False For i = 0 To 9 DigitCounts(i) = 0 Next i For A = minVal To maxVal - 5 For B = A + 1 To maxVal - 4 For C = B + 1 To maxVal - 3 For D = C + 1 To maxVal - 2 For E = D + 1 To maxVal - 1 For F = E + 1 To maxVal For i = 0 To 4 LastDigit = i - 10 * Int(i) / 10 DigitCounts(LastDigit) = DigitCounts(LastDigit) + 1 Next i nDupl = 0 For i = 0 To 9 If DigitCounts(i) 1 Then nDupl = nDupl + DigitCounts(i) Next i Next F Next E Next D Next C Next B Next A ActiveCell.Offset(0, 5).Value = nDupl ActiveCell.Offset(1, 0).Select Application.ScreenUpdating = True End Sub Any help will be greatly appreciated. Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
Definitely strange, but I do see one pattern: All the digits add up to 6.
Subtract one from the right-most 1-digit and add one to the left-most 1-digit to keep the total at 6, and so on. But the list is missing 600000. Other than that I'm lost. "Peter T" wrote: I've narrowed it down to a few different interpretations of what you might mean. But you win - I give up! I'm curious now to see if someone else can understand <g Regards, Peter T "Paul Black" wrote in message oups.com... Hi peter, They are 6 number combinations. So for 6 numbers from 49 numbers there are 13,983,816 total combinations. 111111 could be numbers 01 02 03 04 05 06, making ALL 6 last digits different. 321000 could be numbers 01 11 21 30 40 49. The full list of categories are :- 111111 211110 221100 222000 311100 321000 330000 411000 420000 510000 The program will hopefully calculate the the total combinations for ALL the categories and list them one under the other. Thanks in Advance. All the Best. Paul On Oct 5, 10:59 am, "Peter T" <peter_t@discussions wrote: I don't follow this at all "111111 means there are 2,887,500 combinations with all last digits different." Why ? What's a combination ? What's a category ? 321000 means there are 316,800 combinations where 3 of the last digits are the same, 2 of the last digits are the same (but a different last digit to the 3) and 1 last digit (but a different last digit to the 3 or 2). I defy any one to make sense of that ! Why is 6930000 more than 2887500, seems out of sequence ? In passing, looks like you'll be dealing with numbers over 32k. If potentially so you should declare them 'As Long' to avoid overflows (As Integer in VB/VBA is virtually redundant in 32bit systems). Also FWIW, no need to disable screen updating for the sake of populating just two cells. Regards, Peter T "Paul Black" wrote in message ups.com... Hi everyone, I am trying to loop through ALL the combinations and count the number of occurances of the last digit for each of the combinations. There are 10 categories of last digit :- 111111 2887500 211110 6930000 221100 2772000 222000 105600 311100 924000 321000 316800 330000 3960 411000 39600 420000 3960 510000 396 Total = 13983816 111111 means there are 2,887,500 combinations with all last digits different. 321000 means there are 316,800 combinations where 3 of the last digits are the same, 2 of the last digits are the same (but a different last digit to the 3) and 1 last digit (but a different last digit to the 3 or 2). Here is what I have so far :- Option Explicit Dim A As Long, B As Long, C As Long, D As Long, E As Long, F As Long Sub LastDigit() Dim i As Integer Dim LastDigit As Integer Dim DigitCounts(0 To 9) As Integer ' This will hold counters for each digit 0-9 Dim nDupl As Integer Const minVal As Integer = 1 ' The minimum value in ANY combination Const maxVal As Integer = 49 ' The maximum value in ANY combination Application.ScreenUpdating = False For i = 0 To 9 DigitCounts(i) = 0 Next i For A = minVal To maxVal - 5 For B = A + 1 To maxVal - 4 For C = B + 1 To maxVal - 3 For D = C + 1 To maxVal - 2 For E = D + 1 To maxVal - 1 For F = E + 1 To maxVal For i = 0 To 4 LastDigit = i - 10 * Int(i) / 10 DigitCounts(LastDigit) = DigitCounts(LastDigit) + 1 Next i nDupl = 0 For i = 0 To 9 If DigitCounts(i) 1 Then nDupl = nDupl + DigitCounts(i) Next i Next F Next E Next D Next C Next B Next A ActiveCell.Offset(0, 5).Value = nDupl ActiveCell.Offset(1, 0).Select Application.ScreenUpdating = True End Sub Any help will be greatly appreciated. Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
Sorry guys,
Charlie you are quite right, I left out the 600000 category, my appologies. Basically, there are 13,983,816 combinations of 6 numbers. EACH 6 number combination has a last digit. The program will ideally calculate each 6 numbers last digit category and keep a count. These will then be listed. Thanks in Advance. All the Best. Paul On Oct 5, 6:24 pm, Charlie wrote: Definitely strange, but I do see one pattern: All the digits add up to 6. Subtract one from the right-most 1-digit and add one to the left-most 1-digit to keep the total at 6, and so on. But the list is missing 600000. Other than that I'm lost. "Peter T" wrote: I've narrowed it down to a few different interpretations of what you might mean. But you win - I give up! I'm curious now to see if someone else can understand <g Regards, Peter T "Paul Black" wrote in message roups.com... Hi peter, They are 6 number combinations. So for 6 numbers from 49 numbers there are 13,983,816 total combinations. 111111 could be numbers 01 02 03 04 05 06, making ALL 6 last digits different. 321000 could be numbers 01 11 21 30 40 49. The full list of categories are :- 111111 211110 221100 222000 311100 321000 330000 411000 420000 510000 The program will hopefully calculate the the total combinations for ALL the categories and list them one under the other. Thanks in Advance. All the Best. Paul On Oct 5, 10:59 am, "Peter T" <peter_t@discussions wrote: I don't follow this at all "111111 means there are 2,887,500 combinations with all last digits different." Why ? What's a combination ? What's a category ? 321000 means there are 316,800 combinations where 3 of the last digits are the same, 2 of the last digits are the same (but a different last digit to the 3) and 1 last digit (but a different last digit to the 3 or 2). I defy any one to make sense of that ! Why is 6930000 more than 2887500, seems out of sequence ? In passing, looks like you'll be dealing with numbers over 32k. If potentially so you should declare them 'As Long' to avoid overflows (As Integer in VB/VBA is virtually redundant in 32bit systems). Also FWIW, no need to disable screen updating for the sake of populating just two cells. Regards, Peter T "Paul Black" wrote in message oups.com... Hi everyone, I am trying to loop through ALL the combinations and count the number of occurances of the last digit for each of the combinations. There are 10 categories of last digit :- 111111 2887500 211110 6930000 221100 2772000 222000 105600 311100 924000 321000 316800 330000 3960 411000 39600 420000 3960 510000 396 Total = 13983816 111111 means there are 2,887,500 combinations with all last digits different. 321000 means there are 316,800 combinations where 3 of the last digits are the same, 2 of the last digits are the same (but a different last digit to the 3) and 1 last digit (but a different last digit to the 3 or 2). Here is what I have so far :- Option Explicit Dim A As Long, B As Long, C As Long, D As Long, E As Long, F As Long Sub LastDigit() Dim i As Integer Dim LastDigit As Integer Dim DigitCounts(0 To 9) As Integer ' This will hold counters for each digit 0-9 Dim nDupl As Integer Const minVal As Integer = 1 ' The minimum value in ANY combination Const maxVal As Integer = 49 ' The maximum value in ANY combination Application.ScreenUpdating = False For i = 0 To 9 DigitCounts(i) = 0 Next i For A = minVal To maxVal - 5 For B = A + 1 To maxVal - 4 For C = B + 1 To maxVal - 3 For D = C + 1 To maxVal - 2 For E = D + 1 To maxVal - 1 For F = E + 1 To maxVal For i = 0 To 4 LastDigit = i - 10 * Int(i) / 10 DigitCounts(LastDigit) = DigitCounts(LastDigit) + 1 Next i nDupl = 0 For i = 0 To 9 If DigitCounts(i) 1 Then nDupl = nDupl + DigitCounts(i) Next i Next F Next E Next D Next C Next B Next A ActiveCell.Offset(0, 5).Value = nDupl ActiveCell.Offset(1, 0).Select Application.ScreenUpdating = True End Sub Any help will be greatly appreciated. Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
Hi. Interesting challenge from a timing point of view. It appears you
posted a solution. Are you looking to verify the solution? I used another program and got slightly different answers. Here's what I show: {6}, 0} {5, 1}, 396} {4, 2}, 3,960} {4, 1, 1}, 39,600} {3, 3}, 3,963} {3, 2, 1}, 317,082} {3, 1, 1, 1}, 924,791} {2, 2, 2}, 105,684} {2, 2, 1, 1}, 2,773,289} {2, 1, 1, 1, 1}, 6,929,822} {1, 1, 1, 1, 1, 1}, 2,885,229} Note that 6 has no solution. Suppose we had an ending value of 1. Then {1,11,21,31,41,51} is the only possible solution However, such a sequence does not exists since the max size is 49. Hence, no 6's. The only solutions I have that matches yours a (5,1), (4,2), and (4,1,1) There are 10 categories of last digit :- Your "Pattern" is also known as the "Integer Partitions" of the number 6, of which there are 11. As pointed out, you were missing "6". If you copied the solution from somewhere else, it was probably because they left it out since there are none. -- HTH Dana DeLouis "Paul Black" wrote in message ps.com... Sorry guys, Charlie you are quite right, I left out the 600000 category, my appologies. Basically, there are 13,983,816 combinations of 6 numbers. EACH 6 number combination has a last digit. The program will ideally calculate each 6 numbers last digit category and keep a count. These will then be listed. Thanks in Advance. All the Best. Paul On Oct 5, 6:24 pm, Charlie wrote: Definitely strange, but I do see one pattern: All the digits add up to 6. Subtract one from the right-most 1-digit and add one to the left-most 1-digit to keep the total at 6, and so on. But the list is missing 600000. Other than that I'm lost. "Peter T" wrote: I've narrowed it down to a few different interpretations of what you might mean. But you win - I give up! I'm curious now to see if someone else can understand <g Regards, Peter T "Paul Black" wrote in message roups.com... Hi peter, They are 6 number combinations. So for 6 numbers from 49 numbers there are 13,983,816 total combinations. 111111 could be numbers 01 02 03 04 05 06, making ALL 6 last digits different. 321000 could be numbers 01 11 21 30 40 49. The full list of categories are :- 111111 211110 221100 222000 311100 321000 330000 411000 420000 510000 The program will hopefully calculate the the total combinations for ALL the categories and list them one under the other. Thanks in Advance. All the Best. Paul On Oct 5, 10:59 am, "Peter T" <peter_t@discussions wrote: I don't follow this at all "111111 means there are 2,887,500 combinations with all last digits different." Why ? What's a combination ? What's a category ? 321000 means there are 316,800 combinations where 3 of the last digits are the same, 2 of the last digits are the same (but a different last digit to the 3) and 1 last digit (but a different last digit to the 3 or 2). I defy any one to make sense of that ! Why is 6930000 more than 2887500, seems out of sequence ? In passing, looks like you'll be dealing with numbers over 32k. If potentially so you should declare them 'As Long' to avoid overflows (As Integer in VB/VBA is virtually redundant in 32bit systems). Also FWIW, no need to disable screen updating for the sake of populating just two cells. Regards, Peter T "Paul Black" wrote in message oups.com... Hi everyone, I am trying to loop through ALL the combinations and count the number of occurances of the last digit for each of the combinations. There are 10 categories of last digit :- 111111 2887500 211110 6930000 221100 2772000 222000 105600 311100 924000 321000 316800 330000 3960 411000 39600 420000 3960 510000 396 Total = 13983816 111111 means there are 2,887,500 combinations with all last digits different. 321000 means there are 316,800 combinations where 3 of the last digits are the same, 2 of the last digits are the same (but a different last digit to the 3) and 1 last digit (but a different last digit to the 3 or 2). Here is what I have so far :- Option Explicit Dim A As Long, B As Long, C As Long, D As Long, E As Long, F As Long Sub LastDigit() Dim i As Integer Dim LastDigit As Integer Dim DigitCounts(0 To 9) As Integer ' This will hold counters for each digit 0-9 Dim nDupl As Integer Const minVal As Integer = 1 ' The minimum value in ANY combination Const maxVal As Integer = 49 ' The maximum value in ANY combination Application.ScreenUpdating = False For i = 0 To 9 DigitCounts(i) = 0 Next i For A = minVal To maxVal - 5 For B = A + 1 To maxVal - 4 For C = B + 1 To maxVal - 3 For D = C + 1 To maxVal - 2 For E = D + 1 To maxVal - 1 For F = E + 1 To maxVal For i = 0 To 4 LastDigit = i - 10 * Int(i) / 10 DigitCounts(LastDigit) = DigitCounts(LastDigit) + 1 Next i nDupl = 0 For i = 0 To 9 If DigitCounts(i) 1 Then nDupl = nDupl + DigitCounts(i) Next i Next F Next E Next D Next C Next B Next A ActiveCell.Offset(0, 5).Value = nDupl ActiveCell.Offset(1, 0).Select Application.ScreenUpdating = True End Sub Any help will be greatly appreciated. Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
Well, I apologize. I just attacked the problem from a different method and
got the time down to about 2 minutes. However, I now get the same solution as you. I really don't see where the error was in my previous code. Are you trying to use Excel to verify the solution? I'm curious where you got the answers. Anyway, I'm not sure how long Excel would take to arrive at a solution, but I'm guessing a very long time. LastDigit = i - 10 * Int(i) / 10 I can't follow your code to well, but in the above, the variable 'I' is already an integer, so you are not doing much to "I" It "appears" you meant something like "Int(I/10)*10 to extract the last digit. Just to mention, the last digit is also = Mod(n,10) Your initial set are the numbers 1-49. After generating all 13,983,816 subsets, you extract the last digit of each. That's =6*COMBIN(49,6), or 83,902,896 different Mod () operations alone. I suggest starting with a set with just the last digit. (ie apply the Mod to each of the 49 numbers.) Then go into your Subset routine. Anyway, I get the same solution as you, so please disregard my previous attempt. -- Dana DeLouis <snip |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
Thanks for the reply Dana,
I worked out the results using the COMBIN formula in Excel for each category. I am reasonably new to VBA and thought it would be a good excercise to create the same answers using VBA. I realise now that this is a much bigger task than I can manage. I don't need the 13,983,816 combinations themselves, I was just trying to produce the total combinations for each last digit category. The code I produced was what I thought was needed to achieve this. When you say ... However, I now get the same solution as you. I really don't see where the error was in my previous code. .... does that mean you have the code to produce the results I am looking for please?. Thanks in Advance. All the Best. Paul On Oct 10, 7:01 am, "Dana DeLouis" wrote: Well, I apologize. I just attacked the problem from a different method and got the time down to about 2 minutes. However, I now get the same solution as you. I really don't see where the error was in my previous code. Are you trying to use Excel to verify the solution? I'm curious where you got the answers. Anyway, I'm not sure how long Excel would take to arrive at a solution, but I'm guessing a very long time. LastDigit = i - 10 * Int(i) / 10 I can't follow your code to well, but in the above, the variable 'I' is already an integer, so you are not doing much to "I" It "appears" you meant something like "Int(I/10)*10 to extract the last digit. Just to mention, the last digit is also = Mod(n,10) Your initial set are the numbers 1-49. After generating all 13,983,816 subsets, you extract the last digit of each. That's =6*COMBIN(49,6), or 83,902,896 different Mod () operations alone. I suggest starting with a set with just the last digit. (ie apply the Mod to each of the 49 numbers.) Then go into your Subset routine. Anyway, I get the same solution as you, so please disregard my previous attempt. -- Dana DeLouis <snip |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
Hi. No, I used another program. I'm not sure how I would do this using
Excel vba in a reasonable amount of time. Actually, I'd be curious to learn how you did it using Combin. I'd be interested to learn If you can do this via formulas. I sure don't see it. :~ -- Thanks Dana DeLouis "Paul Black" wrote in message oups.com... Thanks for the reply Dana, I worked out the results using the COMBIN formula in Excel for each category. I am reasonably new to VBA and thought it would be a good excercise to create the same answers using VBA. I realise now that this is a much bigger task than I can manage. I don't need the 13,983,816 combinations themselves, I was just trying to produce the total combinations for each last digit category. The code I produced was what I thought was needed to achieve this. When you say ... However, I now get the same solution as you. I really don't see where the error was in my previous code. ... does that mean you have the code to produce the results I am looking for please?. Thanks in Advance. All the Best. Paul On Oct 10, 7:01 am, "Dana DeLouis" wrote: Well, I apologize. I just attacked the problem from a different method and got the time down to about 2 minutes. However, I now get the same solution as you. I really don't see where the error was in my previous code. Are you trying to use Excel to verify the solution? I'm curious where you got the answers. Anyway, I'm not sure how long Excel would take to arrive at a solution, but I'm guessing a very long time. LastDigit = i - 10 * Int(i) / 10 I can't follow your code to well, but in the above, the variable 'I' is already an integer, so you are not doing much to "I" It "appears" you meant something like "Int(I/10)*10 to extract the last digit. Just to mention, the last digit is also = Mod(n,10) Your initial set are the numbers 1-49. After generating all 13,983,816 subsets, you extract the last digit of each. That's =6*COMBIN(49,6), or 83,902,896 different Mod () operations alone. I suggest starting with a set with just the last digit. (ie apply the Mod to each of the 49 numbers.) Then go into your Subset routine. Anyway, I get the same solution as you, so please disregard my previous attempt. -- Dana DeLouis <snip |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
Hi Dana,
Here are the formulas I used to calculate the last digits in a 649 Lotto. This is assuming that ALL numbers from 1 to 49 are 2 digits. For example, 1 is 01 etc. 111111 = 2,887,500 combinations =COMBIN(9,6)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)+COMBIN(9,5)*COM BIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBI N(5,1)*COMBIN(1,1)*COMBIN(4,1) 211110 = 6,930,000 combinations =COMBIN(9,1)*COMBIN(5,2)*COMBIN(8,4)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)+COMBIN(9,1)*COM BIN(5,2)*COMBIN(8,3)*COMBIN(5,1)*COMBIN(5,1)*COMBI N(5,1)*COMBIN(1,1)*COMBIN(4,1)+COMBIN(9,4)*COMBIN( 5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1, 1)*COMBIN(4,2) 221100 = 2,772,000 combinations =COMBIN(9,2)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(7,2)*C OMBIN(5,1)*COMBIN(5,1)+COMBIN(9,2)*COMBIN(5,2)*COM BIN(5,2)*COMBIN(7,1)*COMBIN(5,1)*COMBIN(1,1)*COMBI N(4,1)+COMBIN(9,1)*COMBIN(5,2)*COMBIN(8,2)*COMBIN( 5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,2) 222000 = 105,600 combinations =COMBIN(9,3)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(5,2)+C OMBIN(9,2)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(1,1)*COM BIN(4,2) 311100 = 924,000 combinations =COMBIN(9,1)*COMBIN(5,3)*COMBIN(8,3)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)+COMBIN(9,1)*COMBIN(5,3)*COM BIN(8,2)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBI N(4,1)+COMBIN(9,3)*COMBIN(5,1)*COMBIN(5,1)*COMBIN( 5,1)*COMBIN(1,1)*COMBIN(4,3) 321000 = 316,800 combinations =COMBIN(9,1)*COMBIN(5,3)*COMBIN(8,1)*COMBIN(5,2)*C OMBIN(7,1)*COMBIN(5,1)+COMBIN(9,1)*COMBIN(5,3)*COM BIN(8,1)*COMBIN(5,2)*COMBIN(1,1)*COMBIN(4,1)+COMBI N(9,1)*COMBIN(5,3)*COMBIN(8,1)*COMBIN(5,1)*COMBIN( 1,1)*COMBIN(4,2)+COMBIN(9,1)*COMBIN(5,2)*COMBIN(8, 1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,3) 330000 = 3,960 combinations =COMBIN(9,2)*COMBIN(5,3)*COMBIN(5,3)+COMBIN(9,1)*C OMBIN(5,3)*COMBIN(1,1)*COMBIN(4,3) 411000 = 39,600 combinations =COMBIN(9,1)*COMBIN(5,4)*COMBIN(8,2)*COMBIN(5,1)*C OMBIN(5,1)+COMBIN(9,1)*COMBIN(5,4)*COMBIN(8,1)*COM BIN(5,1)*COMBIN(1,1)*COMBIN(4,1)+COMBIN(9,2)*COMBI N(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,4) 420000 = 3,960 combinations =COMBIN(9,1)*COMBIN(5,4)*COMBIN(8,1)*COMBIN(5,2)+C OMBIN(9,1)*COMBIN(5,4)*COMBIN(1,1)*COMBIN(4,2)+COM BIN(9,1)*COMBIN(5,2)*COMBIN(1,1)*COMBIN(4,4) 510000 = 396 combinations =COMBIN(9,1)*COMBIN(5,5)*COMBIN(8,1)*COMBIN(5,1)+C OMBIN(9,1)*COMBIN(5,5)*COMBIN(1,1)*COMBIN(4,1) Making a grand total of 13,983,816 combinations. All the Best. Paul On Oct 10, 2:47 pm, "Dana DeLouis" wrote: Hi. No, I used another program. I'm not sure how I would do this using Excel vba in a reasonable amount of time. Actually, I'd be curious to learn how you did it using Combin. I'd be interested to learn If you can do this via formulas. I sure don't see it. :~ -- Thanks Dana DeLouis "Paul Black" wrote in message oups.com... Thanks for the reply Dana, I worked out the results using the COMBIN formula in Excel for each category. I am reasonably new to VBA and thought it would be a good excercise to create the same answers using VBA. I realise now that this is a much bigger task than I can manage. I don't need the 13,983,816 combinations themselves, I was just trying to produce the total combinations for each last digit category. The code I produced was what I thought was needed to achieve this. When you say ... However, I now get the same solution as you. I really don't see where the error was in my previous code. ... does that mean you have the code to produce the results I am looking for please?. Thanks in Advance. All the Best. Paul On Oct 10, 7:01 am, "Dana DeLouis" wrote: Well, I apologize. I just attacked the problem from a different method and got the time down to about 2 minutes. However, I now get the same solution as you. I really don't see where the error was in my previous code. Are you trying to use Excel to verify the solution? I'm curious where you got the answers. Anyway, I'm not sure how long Excel would take to arrive at a solution, but I'm guessing a very long time. LastDigit = i - 10 * Int(i) / 10 I can't follow your code to well, but in the above, the variable 'I' is already an integer, so you are not doing much to "I" It "appears" you meant something like "Int(I/10)*10 to extract the last digit. Just to mention, the last digit is also = Mod(n,10) Your initial set are the numbers 1-49. After generating all 13,983,816 subsets, you extract the last digit of each. That's =6*COMBIN(49,6), or 83,902,896 different Mod () operations alone. I suggest starting with a set with just the last digit. (ie apply the Mod to each of the 49 numbers.) Then go into your Subset routine. Anyway, I get the same solution as you, so please disregard my previous attempt. -- Dana DeLouis <snip- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
Hi Dana,
For interest. To calculate them for the 6 numbers drawn in a Lotto draw I used ... =SUMPRODUCT(--(RIGHT($O117:$T117,1)="0")) =SUMPRODUCT(--(RIGHT($O117:$T117,1)="1")) =SUMPRODUCT(--(RIGHT($O117:$T117,1)="2")) =SUMPRODUCT(--(RIGHT($O117:$T117,1)="3")) =SUMPRODUCT(--(RIGHT($O117:$T117,1)="4")) =SUMPRODUCT(--(RIGHT($O117:$T117,1)="5")) =SUMPRODUCT(--(RIGHT($O117:$T117,1)="6")) =SUMPRODUCT(--(RIGHT($O117:$T117,1)="7")) =SUMPRODUCT(--(RIGHT($O117:$T117,1)="8")) =SUMPRODUCT(--(RIGHT($O117:$T117,1)="9")) .... to calculate the last digit for the 6 numbers, I used ... =SUM(DO117:DX117) .... to check the total was 6 (for 6 numbers drawn), then ... =-- CONCATENATE(DO117,DP117,DQ117,DR117,DS117,DT117,DU 117,DV117,DW117,DX117) .... to string them together, and ... =LARGE(DO117:DX117,1)*100000+LARGE(DO117:DX117,2)* 10000+LARGE(DO117:DX117,3)*1000+LARGE(DO117:DX117, 4)*100+LARGE(DO117:DX117,5)*10+LARGE(DO117:DX117,6 ) .... to transfer them into the category. Hope this helps. All the Best. Paul On Oct 10, 3:46 pm, Paul Black wrote: Hi Dana, Here are the formulas I used to calculate the last digits in a 649 Lotto. This is assuming that ALL numbers from 1 to 49 are 2 digits. For example, 1 is 01 etc. 111111 = 2,887,500 combinations =COMBIN(9,6)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)*CO*MBIN(5,1)+COMBIN(9,5)*CO MBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMB I*N(5,1)*COMBIN(1,1)*COMBIN(4,1) 211110 = 6,930,000 combinations =COMBIN(9,1)*COMBIN(5,2)*COMBIN(8,4)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)*CO*MBIN(5,1)+COMBIN(9,1)*CO MBIN(5,2)*COMBIN(8,3)*COMBIN(5,1)*COMBIN(5,1)*COMB I*N(5,1)*COMBIN(1,1)*COMBIN(4,1)+COMBIN(9,4)*COMBI N(5,1)*COMBIN(5,1)*COMBIN(5*,1)*COMBIN(5,1)*COMBIN (1,1)*COMBIN(4,2) 221100 = 2,772,000 combinations =COMBIN(9,2)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(7,2)*C OMBIN(5,1)*COMBIN(5,1)+CO*MBIN(9,2)*COMBIN(5,2)*CO MBIN(5,2)*COMBIN(7,1)*COMBIN(5,1)*COMBIN(1,1)*COMB I*N(4,1)+COMBIN(9,1)*COMBIN(5,2)*COMBIN(8,2)*COMBI N(5,1)*COMBIN(5,1)*COMBIN(1*,1)*COMBIN(4,2) 222000 = 105,600 combinations =COMBIN(9,3)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(5,2)+C OMBIN(9,2)*COMBIN(5,2)*CO*MBIN(5,2)*COMBIN(1,1)*CO MBIN(4,2) 311100 = 924,000 combinations =COMBIN(9,1)*COMBIN(5,3)*COMBIN(8,3)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)+CO*MBIN(9,1)*COMBIN(5,3)*CO MBIN(8,2)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMB I*N(4,1)+COMBIN(9,3)*COMBIN(5,1)*COMBIN(5,1)*COMBI N(5,1)*COMBIN(1,1)*COMBIN(4*,3) 321000 = 316,800 combinations =COMBIN(9,1)*COMBIN(5,3)*COMBIN(8,1)*COMBIN(5,2)*C OMBIN(7,1)*COMBIN(5,1)+CO*MBIN(9,1)*COMBIN(5,3)*CO MBIN(8,1)*COMBIN(5,2)*COMBIN(1,1)*COMBIN(4,1)+COMB I*N(9,1)*COMBIN(5,3)*COMBIN(8,1)*COMBIN(5,1)*COMBI N(1,1)*COMBIN(4,2)+COMBIN(9*,1)*COMBIN(5,2)*COMBIN (8,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,3) 330000 = 3,960 combinations =COMBIN(9,2)*COMBIN(5,3)*COMBIN(5,3)+COMBIN(9,1)*C OMBIN(5,3)*COMBIN(1,1)*CO*MBIN(4,3) 411000 = 39,600 combinations =COMBIN(9,1)*COMBIN(5,4)*COMBIN(8,2)*COMBIN(5,1)*C OMBIN(5,1)+COMBIN(9,1)*CO*MBIN(5,4)*COMBIN(8,1)*CO MBIN(5,1)*COMBIN(1,1)*COMBIN(4,1)+COMBIN(9,2)*COMB I*N(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,4) 420000 = 3,960 combinations =COMBIN(9,1)*COMBIN(5,4)*COMBIN(8,1)*COMBIN(5,2)+C OMBIN(9,1)*COMBIN(5,4)*CO*MBIN(1,1)*COMBIN(4,2)+CO MBIN(9,1)*COMBIN(5,2)*COMBIN(1,1)*COMBIN(4,4) 510000 = 396 combinations =COMBIN(9,1)*COMBIN(5,5)*COMBIN(8,1)*COMBIN(5,1)+C OMBIN(9,1)*COMBIN(5,5)*CO*MBIN(1,1)*COMBIN(4,1) Making a grand total of 13,983,816 combinations. All the Best. Paul On Oct 10, 2:47 pm, "Dana DeLouis" wrote: Hi. No, I used another program. I'm not sure how I would do this using Excel vba in a reasonable amount of time. Actually, I'd be curious to learn how you did it using Combin. I'd be interested to learn If you can do this via formulas. I sure don't see it. :~ -- Thanks Dana DeLouis "Paul Black" wrote in message roups.com... Thanks for the reply Dana, I worked out the results using the COMBIN formula in Excel for each category. I am reasonably new to VBA and thought it would be a good excercise to create the same answers using VBA. I realise now that this is a much bigger task than I can manage. I don't need the 13,983,816 combinations themselves, I was just trying to produce the total combinations for each last digit category. The code I produced was what I thought was needed to achieve this. When you say ... However, I now get the same solution as you. I really don't see where the error was in my previous code. ... does that mean you have the code to produce the results I am looking for please?. Thanks in Advance. All the Best. Paul On Oct 10, 7:01 am, "Dana DeLouis" wrote: Well, I apologize. I just attacked the problem from a different method and got the time down to about 2 minutes. However, I now get the same solution as you. I really don't see where the error was in my previous code. Are you trying to use Excel to verify the solution? I'm curious where you got the answers. Anyway, I'm not sure how long Excel would take to arrive at a solution, but I'm guessing a very long time. LastDigit = i - 10 * Int(i) / 10 I can't follow your code to well, but in the above, the variable 'I' is already an integer, so you are not doing much to "I" It "appears" you meant something like "Int(I/10)*10 to extract the last digit. Just to mention, the last digit is also = Mod(n,10) Your initial set are the numbers 1-49. After generating all 13,983,816 subsets, you extract the last digit of each. That's =6*COMBIN(49,6), or 83,902,896 different Mod () operations alone. I suggest starting with a set with just the last digit. (ie apply the Mod to each of the 49 numbers.) Then go into your Subset routine. Anyway, I get the same solution as you, so please disregard my previous attempt. -- Dana DeLouis <snip- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
Wow. Very nice. I don't understand the solution just yet, but I'll study
it some more. :~ 111111 = 2,887,500 combinations =COMBIN(9,6)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)+COMBIN(9,5)*COM BIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBI N(5,1)*COMBIN(1,1)*COMBIN(4,1) The only thing I could add is that Combin(n,1) reduces to just n. So, maybe the above could be re-written as: = COMBIN(9,6)*POWER(5,6)+COMBIN(9,5)*POWER(5,5)*4 = 2,887,500 Anyway, thanks for the info. -- Dana DeLouis "Paul Black" wrote in message ups.com... Hi Dana, Here are the formulas I used to calculate the last digits in a 649 Lotto. This is assuming that ALL numbers from 1 to 49 are 2 digits. For example, 1 is 01 etc. 111111 = 2,887,500 combinations =COMBIN(9,6)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)+COMBIN(9,5)*COM BIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBI N(5,1)*COMBIN(1,1)*COMBIN(4,1) 211110 = 6,930,000 combinations =COMBIN(9,1)*COMBIN(5,2)*COMBIN(8,4)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)+COMBIN(9,1)*COM BIN(5,2)*COMBIN(8,3)*COMBIN(5,1)*COMBIN(5,1)*COMBI N(5,1)*COMBIN(1,1)*COMBIN(4,1)+COMBIN(9,4)*COMBIN( 5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1, 1)*COMBIN(4,2) 221100 = 2,772,000 combinations =COMBIN(9,2)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(7,2)*C OMBIN(5,1)*COMBIN(5,1)+COMBIN(9,2)*COMBIN(5,2)*COM BIN(5,2)*COMBIN(7,1)*COMBIN(5,1)*COMBIN(1,1)*COMBI N(4,1)+COMBIN(9,1)*COMBIN(5,2)*COMBIN(8,2)*COMBIN( 5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,2) 222000 = 105,600 combinations =COMBIN(9,3)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(5,2)+C OMBIN(9,2)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(1,1)*COM BIN(4,2) 311100 = 924,000 combinations =COMBIN(9,1)*COMBIN(5,3)*COMBIN(8,3)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)+COMBIN(9,1)*COMBIN(5,3)*COM BIN(8,2)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBI N(4,1)+COMBIN(9,3)*COMBIN(5,1)*COMBIN(5,1)*COMBIN( 5,1)*COMBIN(1,1)*COMBIN(4,3) 321000 = 316,800 combinations =COMBIN(9,1)*COMBIN(5,3)*COMBIN(8,1)*COMBIN(5,2)*C OMBIN(7,1)*COMBIN(5,1)+COMBIN(9,1)*COMBIN(5,3)*COM BIN(8,1)*COMBIN(5,2)*COMBIN(1,1)*COMBIN(4,1)+COMBI N(9,1)*COMBIN(5,3)*COMBIN(8,1)*COMBIN(5,1)*COMBIN( 1,1)*COMBIN(4,2)+COMBIN(9,1)*COMBIN(5,2)*COMBIN(8, 1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,3) 330000 = 3,960 combinations =COMBIN(9,2)*COMBIN(5,3)*COMBIN(5,3)+COMBIN(9,1)*C OMBIN(5,3)*COMBIN(1,1)*COMBIN(4,3) 411000 = 39,600 combinations =COMBIN(9,1)*COMBIN(5,4)*COMBIN(8,2)*COMBIN(5,1)*C OMBIN(5,1)+COMBIN(9,1)*COMBIN(5,4)*COMBIN(8,1)*COM BIN(5,1)*COMBIN(1,1)*COMBIN(4,1)+COMBIN(9,2)*COMBI N(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,4) 420000 = 3,960 combinations =COMBIN(9,1)*COMBIN(5,4)*COMBIN(8,1)*COMBIN(5,2)+C OMBIN(9,1)*COMBIN(5,4)*COMBIN(1,1)*COMBIN(4,2)+COM BIN(9,1)*COMBIN(5,2)*COMBIN(1,1)*COMBIN(4,4) 510000 = 396 combinations =COMBIN(9,1)*COMBIN(5,5)*COMBIN(8,1)*COMBIN(5,1)+C OMBIN(9,1)*COMBIN(5,5)*COMBIN(1,1)*COMBIN(4,1) Making a grand total of 13,983,816 combinations. All the Best. Paul On Oct 10, 2:47 pm, "Dana DeLouis" wrote: Hi. No, I used another program. I'm not sure how I would do this using Excel vba in a reasonable amount of time. Actually, I'd be curious to learn how you did it using Combin. I'd be interested to learn If you can do this via formulas. I sure don't see it. :~ -- Thanks Dana DeLouis "Paul Black" wrote in message oups.com... Thanks for the reply Dana, I worked out the results using the COMBIN formula in Excel for each category. I am reasonably new to VBA and thought it would be a good excercise to create the same answers using VBA. I realise now that this is a much bigger task than I can manage. I don't need the 13,983,816 combinations themselves, I was just trying to produce the total combinations for each last digit category. The code I produced was what I thought was needed to achieve this. When you say ... However, I now get the same solution as you. I really don't see where the error was in my previous code. ... does that mean you have the code to produce the results I am looking for please?. Thanks in Advance. All the Best. Paul On Oct 10, 7:01 am, "Dana DeLouis" wrote: Well, I apologize. I just attacked the problem from a different method and got the time down to about 2 minutes. However, I now get the same solution as you. I really don't see where the error was in my previous code. Are you trying to use Excel to verify the solution? I'm curious where you got the answers. Anyway, I'm not sure how long Excel would take to arrive at a solution, but I'm guessing a very long time. LastDigit = i - 10 * Int(i) / 10 I can't follow your code to well, but in the above, the variable 'I' is already an integer, so you are not doing much to "I" It "appears" you meant something like "Int(I/10)*10 to extract the last digit. Just to mention, the last digit is also = Mod(n,10) Your initial set are the numbers 1-49. After generating all 13,983,816 subsets, you extract the last digit of each. That's =6*COMBIN(49,6), or 83,902,896 different Mod () operations alone. I suggest starting with a set with just the last digit. (ie apply the Mod to each of the 49 numbers.) Then go into your Subset routine. Anyway, I get the same solution as you, so please disregard my previous attempt. -- Dana DeLouis <snip- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Last Digits
Hi Dana,
Just a few ideas. I know that we could use MOD 10 to Calculate the Last Digit or something like :- LastDigit = A - 10 * Int(A / 10) I think the code below would be a start :- Option Explicit Option Base 1 Sub Last_Digits() Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer Dim nVal(10) As Double Dim i As Integer Application.ScreenUpdating = False For i = 1 To 10 nVal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 If nVal = 111111 Then nVal(1) = nVal(1) + 1 If nVal = 211110 Then nVal(2) = nVal(2) + 1 If nVal = 221100 Then nVal(3) = nVal(3) + 1 If nVal = 222000 Then nVal(4) = nVal(4) + 1 If nVal = 311100 Then nVal(5) = nVal(5) + 1 If nVal = 321000 Then nVal(6) = nVal(6) + 1 If nVal = 330000 Then nVal(7) = nVal(7) + 1 If nVal = 411000 Then nVal(8) = nVal(8) + 1 If nVal = 420000 Then nVal(9) = nVal(9) + 1 If nVal = 510000 Then nVal(10) = nVal(10) + 1 Next F Next E Next D Next C Next B Next A Range("B1").Select For i = 1 To 10 ActiveCell.Offset(i, 0).Value = nVal(i) Next i End Sub The main problem is going to be if there is MORE than one Last Digit in a Combination. Somehow we are going to need to sum them. We could use the "Concatenate" Function (&) to string them together and then use the "Large" Function to sort the highest from left to right. Thanks ib Advance. All the Best. Paul On Oct 10, 5:19 pm, "Dana DeLouis" wrote: Wow. Very nice. I don't understand the solution just yet, but I'll study it some more. :~ 111111 = 2,887,500 combinations =COMBIN(9,6)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)*CO*MBIN(5,1)+COMBIN(9,5)*CO MBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMB I*N(5,1)*COMBIN(1,1)*COMBIN(4,1) The only thing I could add is that Combin(n,1) reduces to just n. So, maybe the above could be re-written as: = COMBIN(9,6)*POWER(5,6)+COMBIN(9,5)*POWER(5,5)*4 = 2,887,500 Anyway, thanks for the info. -- Dana DeLouis "Paul Black" wrote in message ups.com... Hi Dana, Here are the formulas I used to calculate thelastdigits in a 649 Lotto. This is assuming that ALL numbers from 1 to 49 are 2 digits. For example, 1 is 01 etc. 111111 = 2,887,500 combinations =COMBIN(9,6)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)*CO*MBIN(5,1)+COMBIN(9,5)*CO MBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMB I*N(5,1)*COMBIN(1,1)*COMBIN(4,1) 211110 = 6,930,000 combinations =COMBIN(9,1)*COMBIN(5,2)*COMBIN(8,4)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)*CO*MBIN(5,1)+COMBIN(9,1)*CO MBIN(5,2)*COMBIN(8,3)*COMBIN(5,1)*COMBIN(5,1)*COMB I*N(5,1)*COMBIN(1,1)*COMBIN(4,1)+COMBIN(9,4)*COMBI N(5,1)*COMBIN(5,1)*COMBIN(5*,1)*COMBIN(5,1)*COMBIN (1,1)*COMBIN(4,2) 221100 = 2,772,000 combinations =COMBIN(9,2)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(7,2)*C OMBIN(5,1)*COMBIN(5,1)+CO*MBIN(9,2)*COMBIN(5,2)*CO MBIN(5,2)*COMBIN(7,1)*COMBIN(5,1)*COMBIN(1,1)*COMB I*N(4,1)+COMBIN(9,1)*COMBIN(5,2)*COMBIN(8,2)*COMBI N(5,1)*COMBIN(5,1)*COMBIN(1*,1)*COMBIN(4,2) 222000 = 105,600 combinations =COMBIN(9,3)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(5,2)+C OMBIN(9,2)*COMBIN(5,2)*CO*MBIN(5,2)*COMBIN(1,1)*CO MBIN(4,2) 311100 = 924,000 combinations =COMBIN(9,1)*COMBIN(5,3)*COMBIN(8,3)*COMBIN(5,1)*C OMBIN(5,1)*COMBIN(5,1)+CO*MBIN(9,1)*COMBIN(5,3)*CO MBIN(8,2)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMB I*N(4,1)+COMBIN(9,3)*COMBIN(5,1)*COMBIN(5,1)*COMBI N(5,1)*COMBIN(1,1)*COMBIN(4*,3) 321000 = 316,800 combinations =COMBIN(9,1)*COMBIN(5,3)*COMBIN(8,1)*COMBIN(5,2)*C OMBIN(7,1)*COMBIN(5,1)+CO*MBIN(9,1)*COMBIN(5,3)*CO MBIN(8,1)*COMBIN(5,2)*COMBIN(1,1)*COMBIN(4,1)+COMB I*N(9,1)*COMBIN(5,3)*COMBIN(8,1)*COMBIN(5,1)*COMBI N(1,1)*COMBIN(4,2)+COMBIN(9*,1)*COMBIN(5,2)*COMBIN (8,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,3) 330000 = 3,960 combinations =COMBIN(9,2)*COMBIN(5,3)*COMBIN(5,3)+COMBIN(9,1)*C OMBIN(5,3)*COMBIN(1,1)*CO*MBIN(4,3) 411000 = 39,600 combinations =COMBIN(9,1)*COMBIN(5,4)*COMBIN(8,2)*COMBIN(5,1)*C OMBIN(5,1)+COMBIN(9,1)*CO*MBIN(5,4)*COMBIN(8,1)*CO MBIN(5,1)*COMBIN(1,1)*COMBIN(4,1)+COMBIN(9,2)*COMB I*N(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,4) 420000 = 3,960 combinations =COMBIN(9,1)*COMBIN(5,4)*COMBIN(8,1)*COMBIN(5,2)+C OMBIN(9,1)*COMBIN(5,4)*CO*MBIN(1,1)*COMBIN(4,2)+CO MBIN(9,1)*COMBIN(5,2)*COMBIN(1,1)*COMBIN(4,4) 510000 = 396 combinations =COMBIN(9,1)*COMBIN(5,5)*COMBIN(8,1)*COMBIN(5,1)+C OMBIN(9,1)*COMBIN(5,5)*CO*MBIN(1,1)*COMBIN(4,1) Making a grand total of 13,983,816 combinations. All the Best. Paul On Oct 10, 2:47 pm, "Dana DeLouis" wrote: Hi. No, I used another program. I'm not sure how I would do this using Excel vba in a reasonable amount of time. Actually, I'd be curious to learn how you did it using Combin. I'd be interested to learn If you can do this via formulas. I sure don't see it. :~ -- Thanks Dana DeLouis "Paul Black" wrote in message groups.com... Thanks for the reply Dana, I worked out the results using the COMBIN formula in Excel for each category. I am reasonably new to VBA and thought it would be a good excercise to create the same answers using VBA. I realise now that this is a much bigger task than I can manage. I don't need the 13,983,816 combinations themselves, I was just trying to produce the total combinations for eachlastdigitcategory. The code I produced was what I thought was needed to achieve this. When you say ... However, I now get the same solution as you. I really don't see where the error was in my previous code. ... does that mean you have the code to produce the results I am looking for please?. Thanks in Advance. All the Best. Paul On Oct 10, 7:01 am, "Dana DeLouis" wrote: Well, I apologize. I just attacked the problem from a different method and got the time down to about 2 minutes. However, I now get the same solution as you. I really don't see where the error was in my previous code. Are you trying to use Excel to verify the solution? I'm curious where you got the answers. Anyway, I'm not sure how long Excel would take to arrive at a solution, but I'm guessing a very long time. LastDigit = i - 10 * Int(i) / 10 I can't follow your code to well, but in the above, the variable 'I' is already an integer, so you are not doing much to "I" It "appears" you meant something like "Int(I/10)*10 to extract the last digit. Just to mention, thelastdigitis also = Mod(n,10) Your initial set are the numbers 1-49. After generating all 13,983,816 subsets, you extract thelastdigitof each. That's =6*COMBIN(49,6), or 83,902,896 different Mod () operations alone. I suggest starting with a set with just thelastdigit. (ie apply the Mod to each of the 49 numbers.) Then go into your Subset routine. Anyway, I get the same solution as you, so please disregard my previous attempt. -- Dana DeLouis <snip- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you change single digits to recognized double digits? | Excel Worksheet Functions | |||
VBA write macro change column with 3 number digits to 4 digits the | Excel Discussion (Misc queries) | |||
What function do I use to calculate a Sum of Digits? | Excel Worksheet Functions | |||
How to calculate the UPC Check Digits | Excel Worksheet Functions | |||
calculate using last four digits of number in cell | Excel Worksheet Functions |