• open dock
  • Home
  • Professional Services
    • Business Improvement
    • Data Optimisation Services
  • Our Approach
    • maBI
    • Data-Driven Decision Model
  • Portfolio
    • Case Studies and Portfolio
    • Timeline
  • Contact Us
Change this Limited
  • Home
  • Professional Services
    • Business Improvement
    • Data Optimisation Services
  • Our Approach
    • maBI
    • Data-Driven Decision Model
  • Portfolio
    • Case Studies and Portfolio
    • Timeline
  • Contact Us

Rock Paper Scissors

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:

01

02Since 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”.03

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.04

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.05

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.06

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.07

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.08

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:09

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.10

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:-11

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!
12

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")),"")
  • 05 Jan
  • Tobias Owen
  • Excel
  •  1 Comment
  •  1
  • AND() , Data Validation , Drop-downs , Excel , Formula , Game , IF() , INDEX-MATCH , Lookups , OR()

Share This

Related Posts

  • Updated Excel workbooks links
  • Date Functions : Excel Calendar (Part 2)
  • Calculate Time Elapsed using NETWORKDAYS()
  • Why INDEX-MATCH is better than VLOOKUP
Rank by Multiple Criteria in Excel →← Calculate Time Elapsed using NETWORKDAYS()
3

Comments

  1. Ranking 2 Criteria in Excel - Change This Limited
    29th June 2015 at 10:15 am

    […] For a more detailed look at INDEX(MATCH()) formulas, check out the blog post Why INDEX-MATCH is better than VLOOKUP. […]

    Reply
  2. Rock Paper Scissors | Change This Limited
    5th January 2016 at 5:49 pm

    […] 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. […]

    Reply
  3. Rank by Multiple Criteria in Excel - Change this Limited
    9th March 2017 at 11:01 am

    […] For a more detailed look at INDEX(MATCH()) formulas, check out my blog post Why INDEX-MATCH is better than VLOOKUP. […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Recent Posts

    • Change this Christmas Dash-er-board
    • Expo round-up and prize draw answers
    • Change this at Birmingham Business Expo – 14 Nov 2017
    • Power BI Custom Visual – Play Axis – Review
    • File Naming Conventions and Best Practice
  • Archives

    • December 2018
    • November 2017
    • June 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • September 2016
    • August 2016
    • July 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • September 2015
    • June 2015
    • May 2015
    • April 2015
  • Categories

    • Case Studies
    • Excel
    • Insight
    • News
    • Portfolio
    • Power BI
    • SAP Dashboards
    • SharePoint
  • Search our site

  • Social

  • Recent Blog Posts

    • Change this Christmas Dash-er-board
    • Expo round-up and prize draw answers
    • Change this at Birmingham Business Expo – 14 Nov 2017
    • Power BI Custom Visual – Play Axis – Review
    • File Naming Conventions and Best Practice
  • © 2016 Change this Limited
    Website by Change this
    Standard Terms of Business
    Privacy Policy

    Delivering the difference

This website uses some cookies to improve your experience within the website.


We also use cookies to understand how visitors use our website Accept


To find out what cookies we use and for more information, Read More.
Privacy & Cookies Policy

Necessary Always Enabled

Non-necessary