Background

The other day a YouTube video popped up on my feed called “The Riddle That Seems Impossible Even If You Know The Answer”. It’s a 17-minute video meant to introduce the 100 Prisoners Riddle and dive into the math behind the incredibly simple, but completely mind-boggling answer.

To be fair, they do a great job. But I wasn't fully convinced!

So I set out to model the riddle & solution in Excel to see it for myself.

The Riddle

There are 100 inmates in a prison numbered 1 to 100, and they’ve been offered a chance to escape. To do so, the warden placed a card with each of their numbers randomly inside 100 numbered boxes. Each prisoner can open 50 (half) of the boxes, one at a time. If they ALL find their number, they are free. If just one of them doesn't, then they remain locked up.

(the image is missing the fact that the boxes/drawers themselves are also labeled 1 to 100)

The Probability of Escaping

If a prisoner opens 50 out of 100 boxes at random, they have a 50% chance of finding their number. The probability for all 100 of them to do this and succeed, however, is 0.00000000000000000000000000008%.

(1/2)^100 = 0.0000000000000000000000000000008

To use the example from the YouTube video to put this into perspective, two people have a better chance of picking out the same grain of sand from all the beaches and deserts on Earth. Yeah, you read that right.

In other words, the warden is just having fun with them.

But here’s the catch. Before the first prisoner goes into the room and opens any boxes, they have a chance to come up with a strategy.

And guess what? There's a strategy they can use to improve their chances of escaping to 0.31 (almost 1 in 3!).

Can you think of it?

The Strategy

1. Each prisoner starts by opening the box with their number on the outside
2. They open the box, reveal the number inside, then go to the box with that number
3. They repeat this until they either find their number or run out of boxes (50 max)

Believe it or not, this will get all 100 prisoners to find their number 31% of the time.

Don't believe it? Neither did I!

So I got to work in Excel.

Simulating the Riddle

My goal was to create the 100 prisoners in Excel and have them go through the process outlined in the "winning" strategy. That way, I could randomize the cards inside the boxes myself and see if they did, in fact, find their own number 1/3rd of the time.

The Setup

The first step was to create the 100 boxes using the SEQUENCE function, then randomize the cards inside the boxes with a combination of SORTBY and RANDARRAY:

The next step was to create all 100 prisoners and make room for the 50 boxes they would open (path):

The Simulation

With the elements ready, I needed to have each prisoner "open the box with their number on it and read the card", which can be accomplished with XLOOKUP (or just transposing the "Card" array):

From there, I could use the same XLOOKUP function to open the box with this new number on it, and so on and so forth. I just had to add an IF statement to stop the process if the prisoner "found its number":

This was effectively simulating each prisoner's process of opening up their 50 boxes by using the logic outlined by the solution.

Now I just needed to quickly check if they succeeded or not!

The Outcome

I wanted to display the outcome in a single cell: either "They are saved!" or "They failed!"

To check if each prisoner succeeded or not, I just applied a simple COUNTIFS function to count the number of times their own number appeared inside the boxes they opened. If they found it, the result would be 1, if they didn't then it would be 0.

I hid the results of this by applying a custom number format (;;;), then added up the results inside an IF statement. If the sum was 100, it meant all 100 prisoners found their number, therefore saving themselves:

Finishing Touches

With the simulation finished, there were still some finishing touches that could be applied to make the formatting more appealing:

Conclusion

This was one of those cases where I just couldn't wait to try something out in Excel, and the outcome went above and beyond my expectations.

I just hope this helps others visualize the "unbelievableness" of the solution the same way it helped me!