Finding Consecutive Values in a List of Numbers

I'm not sure how you plan to use this. With 4 numbers each in the range [1..40] the probability of all 4 matching is 1 in 40^4 or 1 in 2,560,000. You're going to wear out your F9 key

You could have VBA doing the re-calculate and keeping count of how many trys are needed before you match all four if that would be interesting. In fact that is what I have implemented in the attached workbook.

"how can I make it so that i can also get a TRUE (win) with 3 of the 4 numbers in a row?"

. So by these rules rows 2,3,4 matching counts, rows 3,4,5 matching counts, but no other row sets like 2, 4, 5 are allowed to count??

The easier thing to calculate is is any set of 3 matching pairs. To do that simply change the "4" in the formula in E1 to "3". I believe the probability of a match in this case is 1 in 40^3/4 or 1 in 16,000. This is what I have limited things to at the moment.

In the attached workbook, first - MAKE SURE MACROS ARE ENABLED then set cell E2 to the number of required matches (1, 2, 3 or 4), then either hit F9 to recalculate and see the tries one by one or alternatively hit the "Run" button and wait for the "Needed tries" to be updated. My somewhat long in the tooth laptop takes on average about 5 sec for 3 matching pairs and I expect it to take about 2 - 3 minutes on average for 4 matches.

Here's how the worksheet looks:

probabilities.png

Here's the VBA:

Option Explicit
Sub go()
    Range("E5").Value = ""
    Application.ScreenUpdating = False
    Dim counter As Long
    counter = 0
    Calculate
    While Range("E1") = False
        'Need to set E5 here rather than after exiting the while
        'loop to avoid Excel doing a recalculate and changing
        'the matching random numbers.
        Range("E5").Value = counter
        Calculate
        counter = counter + 1
    Wend
    Application.ScreenUpdating = True
End Sub

Let me know if you want to explore this any further.