Running Simulations Within Microsoft Excel

When you think of Microsoft Excel, you probably don’t think simulation. However, it can be one of the most powerful simulation tools you have access to.

800px-five_ivory_dice

Running a simulation in Excel is probably going to look slightly different than other more graphical models. With a little work setting up the spreadsheet, you can simulate problems of profit, cost, capacity, anything you can think of. Excel as a program is very flexible in what it allows the user to do. This means that the ability to run simulations is only limited virtually by your knowledge. One of the advantages of using Excel to run numerical simulations is its work time. Using random number generators and complex spreadsheets, you can iterate simulations as many times as you need to see the standard result. From this data, you can extract summary sheets and easily evaluate complex situations.

Let’s stop talking about running simulations in Excel and run through a basic example. Say you are playing a game with 2 other people. You each take turns rolling a die and whoever rolls the biggest number each round wins. If you roll the same number as another player, then the game is a tie. You want to simulate the game and give an output of whether you will win, lose, or tie. Take a moment to think about the game and how you might simulate it using equations within excel. If you really want to show off your skills, then actually open it up and start simulating. If you’re stuck, don’t worry. Let’s go through the answer. 

microsoft_excel_2013_logo-svg

To begin the simulation, you need to set up the die. To do this, we will use the random number generator function built into Excel. Input the function, =RANDBETWEEN(1,6) into the cell that you want to simulate roll one. Repeat this 2 more times to get a total of 3 cells all generating random numbers, each one being the roll of one player. When you press F9, the cells should generate another random number. The game is essentially now set up, but now we need to create a way for Excel to output win, tie, or lose. To do this, select which dice cell will simulate you in the game. In the cell you want to output the “win”, “lose”, or “tie” results, you will need to write up an equation using logic functions such as IFS, IF, AND, XOR, and OR into the results box. Your equation will vary based on your specific data table, but keep in mind that you will need to simulate all possible results in this one equation. Using these tools, see if you can get an equation that outputs the correct answers throughout many iterations of the game.

The other option to go about simulating and outputting the correct answer would be to set up a data table with all possible outcomes from the dice rolls. By setting up all possible outcomes in a table, you can then set your result to output the value reflected in the outcome table using only simple IFS statements within Excel. These would be the two main ways that one would simulate this simple game.

As you may be able to imagine, simulating even more complex problems requires a certain ability to follow along with Excel. This is why it is so crucial that you properly organize and label your spreadsheet. It’s quite easy to get mostly through an Excel simulation and get lost along the way.

Utilizing Excel simulation as an engineer is going to look slightly different than playing a dice game like the example above. Even with that said, the same basic principles apply. Before you get into setting up any level of a complex spreadsheet, it is always good to write out a roadmap for what you want to simulate with the inputs and outputs. For example, you could be evaluating the cost effectiveness of various additive manufactured parts compared to injection molding. You would likely want to input part characteristics like cost or quality and receive an output of an arbitrary part score.

While Excel simulations may not be the same as the simulations you run on a daily basis, they can be equally as important in an engineering workflow. Don’t discount Excel’s ability to simulate.

Sources: University of Oregon, Vazoof, Stack Exchange

Images: [1]

Trevor is a civil engineer by trade and an accomplished internet blogger with a passion for inspiring everyone with new and exciting technologies. He is also a published children’s book author whose most recent book, ZOOM Go the Vehicles, is aimed at inspiring young kids to have an interest in engineering.


It's only fair to share...Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn2