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:

Please Login or Register  to view this content.

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