How to play “Rock Paper Scissors” with Excel
Happy New Year everyone! My first blog of 2016 has a light-hearted theme, but actually draws on some concepts that can be very useful when working with Excel.
This blog looks at how to use drop-down lists, lookups and the enigmatic IF(OR(AND())) function combo to re-create this popular playground game.
Setting Up
The “game” will require a Human input, which we will set up as a drop-down list, and an automatic response from Excel, which we will set up as a lookup using INDEX(MATCH()).
First of all, you need to set up the front end. My example looks like this:
Since there are only three possible options available to the players, we can create a simple drop-down list to act as our input. This will enable the Human player to choose either Rock, Paper or Scissors. However, Excel cannot interact with a drop-down list in the same way, so we will need to set up a lookup for Excel to be able to use the same options.
Write out the three options in a table, with the numbers 1 – 3 alongside, as shown opposite.
Creating a drop-down list
Select cell B3, which will be the Human input cell. In order to set up a drop-down list in this cell, navigate in the menu to “Data” -> “Data Validation”.
Under the “Settings” tab, go to the “Allow” field and use the menu to set this option to “List”. Ensure that the check box marked “In-cell dropdown” is checked.
Click the icon in the “Source” field to select the data range that is required for the drop-down list. Drag to select the cells containing the three options (in my example, cells K3:K5) and then click the icon again. Click “OK” to exit the Data Validation dialogue.
You should now have a drop-down list appear in cell B3, as below.
Creating a lookup
Select cell F3, which will be where Excel will declare its hand. Since Excel cannot interact with a drop-down list, we have to set up a formula to allow Excel to participate in the game.
We want Excel to randomly select one of the three options rather than using pre-programmed responses. Fortunately, Excel has a built-in random number generator in the form of the RANDBETWEEN() function. Entering the formula =RANDBETWEEN(1,3) will generate a random number between 1 and 3 inclusive. We can use this number as a lookup against the list of options in column K.
We will use the INDEX(MATCH()) combo to lookup the option that corresponds to our randomly-generated number. If you’re unfamiliar with INDEX(MATCH()), you can read more about it here.
In this example, the MATCH() element of the formula is using the RANDBETWEEN() function as a lookup. The INDEX() element of the formula is then returning the corresponding label from column K.
This almost gives us what we want – however, cell F3 will now be populated by default, which means Excel is “declaring its hand” before the Human player has made a selection from the drop-down! In order to make the game fair, we need to tell Excel to keep its selection hidden until the player has made their choice, i.e. when cell B3 is populated.
We can do this by adding a primary condition to the formula which returns a blank unless cell B3 is populated, as below.
You should now be able to challenge Excel to a game of Rock Paper Scissors by interacting with the drop-down list in cell B3.
Note that RANDBETWEEN() is what’s known as a “volatile function”, which means that it will generate a new random number every time something is input on to the spreadsheet. This means that Excel will make a new random selection every time you choose an item from the drop-down list, which simulates the way the game works in real life.
Determining the Outcome
At the moment, it is up to the Human player to determine who wins and who loses, by checking both players’ selections. Ideally, Excel would declare the outcome of the game itself. We can programme Excel to do this by “teaching” it the rules of Rock Paper Scissors, using a formula.
Now, some might question the real-life application of teaching Excel to play games, but let me put it another way. Thousands of Excel models – from simple risk assessment matrices to large-scale economic predictive models – rely on a rules-based approach to determine outcomes. Being able to programme rules-based decisions into Excel is a valuable skill. And if it also helps you play Rock Paper Scissors, it’s all to the good!
First of all, we need to identify all the various possible outcomes and conditions. Rock Paper Scissors is a very simple game, so this is a short list.
- Player wins
- Player has “Rock”, Excel has “Scissors”
- Player has “Scissors”, Excel has “Paper”
- Players has “Paper”, Excel has “Rock”
- Player loses (i.e. Excel wins, conditions as per above)
- Tie
The cells we need to look at when determining the outcome are B3 (Player 1’s input) and F3 (Excel’s input). We will first look at the Human player’s outcome, which we will write in cell D3.
Whenever formulas include conditions, the IF() function is required. For example, if cell B3 and cell F3 contain the same value, we want to return the outcome “Tie”, as below.
The conditions required to return an outcome of “Win” are more complex, and so additional functions are needed.
The AND() function is useful here, because each victory condition requires two things to be true (e.g. B3=”Rock” AND F3=”Scissors”). There are three different scenarios which would result in a player win, as identified above, and rather than writing out a separate clause in the IF() statement for each of them, we can tell Excel to return an outcome of “Win” if ANY ONE of them are true. This requires the OR() function.
Combining IF(), AND() and OR() is a wonderfully efficient way of describing potentially complex scenarios. Consider the following example:
Each of the three scenarios that would result in a player victory are described in separate AND() statements. All three statements are contained within an OR() function, which tells Excel that only one of them needs to be true in order to return an outcome of “Win”. The final part of the IF() statement tells Excel that if none of the victory conditions have been met, return an outcome of “Lose”.
We can combine this with the “Tie” outcome outlined previously to complete the formula. As before, include a primary condition that keeps the outcome hidden until cell B3 has been populated.
This will now declare the player’s outcome when cell B3 is populated. All that remains is to declare Excel’s outcome in cell H3.
We could use the same formula as is in cell D3, and reverse all references to cells B3 and F3. However, there is an easier way. Since we already know the player’s outcome, we can write a formula to check cell D3 and declare Excel’s outcome accordingly (e.g. if the player wins, Excel will lose).
This can be done using the following formula:-
As a final finishing touch, you can add conditional formatting rules to cells D3 and H3 that will colour code the cell and font accordingly, depending on the outcome. Your game of Rock Paper Scissors is now complete!
Download The Workbook
Rock Paper Scissors (Workbook)
Formula Breakdown
The formula used to teach Excel the rules of Rock Paper Scissors is shown below. It uses the following steps:-
Primary IF() Condition
=IF(B3<>"",
Checks whether cell B3 has been populated, i.e. whether the player has made their choice
“Tie” Condition
IF(B3=F3,"Tie",
If both players have made the same choice, the game is a Tie
“Win!” Conditions
IF(OR(AND(B3="Rock",F3="Scissors"),AND(B3="Scissors",F3="Paper"),AND(B3="Paper",F3="Rock")),
Checks whether any one of the three victory conditions have been met
Outcome
"Win!","Lose")),"")
Returns the outcome of the game, or displays a blank if cell B3 has not yet been populated
Get The Formula
The full formula used in this blog is as follows:-
=IF(B3<>"",IF(B3=F3,"Tie",IF(OR(AND(B3="Rock",F3="Scissors"),AND(B3="Scissors",F3="Paper"),AND(B3="Paper",F3="Rock")),"Win!","Lose")),"")
Comments