Public Listi's >

Using #Excel #VBA and brute force to solve a #statistics #problem "What are the #odds...?"

By Alec Berg @AlecBerg 4/29/2012

I have one of those RSA devices that helps me log in to work. I noticed that there always seems to be duplicate digits on the display as in the picture above.

So I wondered what the odds of this occurring are? I'm good at statistics and could probably have figured it out, but one thing you can use Excel VBA for is to brute force some statistical calculations.  

So here is the code I used.

Sub brute_force()
'This routine will brute force calculate the number of duplicates
'that will appear in a 6 digit random RSA SecureID
Dim i, snum, snum2, lDups, j, bNoDups

For i = 0 To 999999
    'The display goes from 000000 to 999999
    snum = Format(i, "000000") 'add leading zeroes
    bNoDups = False ' this was used for debugging
    For j = 0 To 9 ' for each digit
        snum2 = Replace(snum, j, "") ' delete the digit
        If Len(snum2) < 5 Then '
            ' if after deleting a number says 0 there would be 5 digits left if no dups
            ' and < 5 if there are dups
            lDups = lDups + 1 'count the dups
            bNoDups = True ' set flag (for testing
            Exit For ' get out this is a dup
        End If
    'If bNoDups = False Then Stop ' used for debugging
MsgBox "Odds are " & Format(lDups / 1000000, "#0.0%") ' the odds are the number of dups divided
                                                      ' by number of trials

End Sub

The following is displayed:

If you have any questions on this contact me via twitter @AlecBerg

As an exercise try to develop the code to display the probability for the Birthday Problem

If you have the statistics solution to this problem send it to me and I'll post it here. If you have any statistical question to brute force send it to

blog comments powered by Disqus

Home | About | Feedback| Privacy | Terms of Service | @alecberg | FAQ | | Copyright © 2021 All Rights Reserved. Edit
This site uses cookies. If you don't want cookies and who doesn't want them as they're yummy, then leave.