Recommend
6 
 Thumb up
 Hide
21 Posts

BoardGameGeek» Forums » Board Game Design » Board Game Design

Subject: EXCEL for gamers rss

Your Tags: Add tags
Popular Tags: excel [+] [View All]
Confusion Under Fire
United Kingdom
Warrington
Cheshire
flag msg tools
designer
Avatar
mbmbmbmbmb
I really love Microsofts Excel programme and use it for all my gaming projects, game design, scenario design and for running my own email game.

The simpler functions include adding up a column or row of numbers, copying text to appear in a different cell and continuing a set of numbers after typing just a few. These are pretty basic Excel commands but I discovered recently a command that could be applied to gaming. The Random Command which looks like this =RAND() This will give a random number of between 0 and 1 so how does this help us in game design?

If we write the numbers from 1 to 10 in column A and then copy the Random command in column B. Then highlight column B followed by column A and click "Sort and Filter" and "Sort Lowest to Highest" This will randomise column A. You can find a pictorial description of this here on the geek
http://www.boardgamegeek.com/thread/691984/looking-for-an-eq...
This could be used to represent 10 different die rolls on a 1D10 or the order of play or could represent cards being shuffled. I recently used Excel to portray 100 card shuffles of 109 cards in less than an hour.


I would be interested to hear about any other Excel tips that could be applied to gaming, especially wargaming.

Thanks
Mike
3 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
jumbit
China
Zhejiang
flag msg tools
Avatar
mbmbmbmbmb
I've heard that a true wargamer's favorite game is Excel.
3 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
TS S. Fulk
Sweden
Örebro
flag msg tools
designer
Avatar
mbmbmbmbmb
For the MS-free, it works in Apple's Numbers and should work in OO.O's Calc (haven't tried) too.
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
David Gregg
United States
Franklinville
NC
flag msg tools
designer
NightfallGame.com/FAQ
badge
boardgamegeek.com/thread/1234645
Avatar
mbmbmbmbmb
tssfulk wrote:
For the MS-free, it works in Apple's Numbers and should work in OO.O's Calc (haven't tried) too.

Works in Google Docs too:
for column B use randbetween(low#,high#)
select the columns, click Data, Sort Range, Sort by Column B and A -> Z
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Caleb
United States
Seminole
Florida
flag msg tools
badge
Avatar
mbmbmbmbmb
This works in Google Docs too. You can also use =RANDBETWEEN(x,y) to return only integers between two numbers x and y. Better for simulating die rolls on y-sided dice.

I've created counters in Excel before for prototypes. You can make the rows & columns the same width/height to make squares, and then use fonts like Windings for various symbols. Print on label paper and affix to illustration board, then cut out.

You can also get more tricky by using several rows/columns which, when combined, make a square, but leave several cells within the square so you can place things in specific areas. Play around with it - it works pretty well.


EDIT: too slow on RANDBETWEEN.
3 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Richard Clarke
United Kingdom
flag msg tools
Avatar
mbmbmbmbmb
Conditional formatting can be used to highlight success/failure of a die roll by altering the condition as a result of a comparison between a random value and a target value.

To use an RPG example - your skill is 33% which you put in cell A5.

You setup cell C5 to roll a rand between 1 to 100. You could use conditional formatting on cell C5 to colour the box in green if the random number is below or equal to cell A5 or red if above A5.

If 01 or 100 equal critical success or failure in the game you could have further conditions that indicate graphically the result.
3 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Richard Clarke
United Kingdom
flag msg tools
Avatar
mbmbmbmbmb
PS

F9 also recalculates the spreadsheet and re-randomises all of the RAND & RANDBETWEEN fields.

F9 can be very useful!
4 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Nate K
United States
Utah
flag msg tools
designer
Avatar
mbmbmbmbmb
I was trying just yesterday to create a randomization macro and tie it to a button. The hope was to be able to click the button and get a number between 1 and 157 inclusive, simulating drawing a card from the Rainbow Deck. (I'm trying to build an RPG system around the deck.) Unfortunately, I've never actually made a macro before, and I keep screwing it up. But I'm certain that it's possible!
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Matt Robertson
Canada
Regina
Saskatchewan
flag msg tools
Life is Short; Play Games!
badge
The BixCON Series of gaming events: A combination of Great Friends, Great Food, & Great Drink!
Avatar
mbmbmbmbmb
I have built a nice collection analysis tool for myself.

The BGG XML API makes it so easy to load your collection into excel and keep it up to date. I quite enjoy playing around with some MS Excel functions.

VLOOKUP is very handy.
Autofilters are very versatile as well.
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Confusion Under Fire
United Kingdom
Warrington
Cheshire
flag msg tools
designer
Avatar
mbmbmbmbmb
Say you want to return a word depending on how high the total of a column is, for example I want to return the word "NO" if the sum is 0 or less and return "YES" if the sum is 1 or more. The sum total is in cell O3 you would type the following command;

=IF(O3
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
David Gregg
United States
Franklinville
NC
flag msg tools
designer
NightfallGame.com/FAQ
badge
boardgamegeek.com/thread/1234645
Avatar
mbmbmbmbmb
whatambush wrote:
=IF(O3

BGG filters "less than" and "greater than" symbols, as well as everything that comes after it. To make one show up you need to use the HTML entity: &lt; = < and &gt; = >
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Nate K
United States
Utah
flag msg tools
designer
Avatar
mbmbmbmbmb
Bixby wrote:

Autofilters are very versatile as well.


Autofilters?
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls

Lacombe
Louisiana
msg tools
badge
Suddenly a shot rang out! A door slammed. The maid screamed. Suddenly a pirate ship appeared on the horizon! While millions of people were starving, the king lived in luxury. Meanwhile, on a small farm in Kansas, a boy was growing up.
Avatar
mbmbmbmbmb
kurthl33t wrote:
I was trying just yesterday to create a randomization macro and tie it to a button. The hope was to be able to click the button and get a number between 1 and 157 inclusive, simulating drawing a card from the Rainbow Deck. (I'm trying to build an RPG system around the deck.) Unfortunately, I've never actually made a macro before, and I keep screwing it up. But I'm certain that it's possible!


I made an Excel sheet to randomly deal "hands" from the Decktet that you might be interested in referencing. I was lazy and didn't want to fuss with any macros, so it's all done in standard formulas [well, pseudo-standard... I recall I used some pretty wacky tricks]. It makes heavy use of random number generation and formula-based table "look ups". Find it here. It could probably be fairly easily adapted to another deck, and certainly the general principles could be abstracted to get you somewhere.

Of course, just generating a single random number between 1 and 157 is pretty trivially easy [RANDBETWEEN(1,157) in a formula; the macro shouldn't be too wacky].
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Caleb
United States
Seminole
Florida
flag msg tools
badge
Avatar
mbmbmbmbmb
Bixby wrote:

VLOOKUP is very handy.
Autofilters are very versatile as well.


Use INDEX and MATCH instead of VLOOKUP. INDEX/MATCH don't need special sorting, and MATCH looks beyond the first 15 characters in a cell, which is the limit of VLOOKUP. Also you can combine two MATCH functions with INDEX to find the intersection of a table, which is really handy for CRT's and other two-dimensional data tables.
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Caleb
United States
Seminole
Florida
flag msg tools
badge
Avatar
mbmbmbmbmb
NateStraight wrote:


Of course, just generating a single random number between 1 and 157 is pretty trivially easy [RANDBETWEEN(1,157) in a formula; the macro shouldn't be too wacky].



It's CalculateNow I believe. As in application.activesheet.calculatenow, but I don't have Excel on my Mac to check.

Record a macro and hit F9. Then open the Forms toolbar and put a button on the screen and tie it to that macro...done.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Wim van Gruisen
Netherlands
Den Bosch
Unspecified
flag msg tools
designer
Avatar
mbmb
whatambush wrote:
This could be used to represent 10 different die rolls on a 1D10 or the order of play or could represent cards being shuffled. I recently used Excel to portray 100 card shuffles of 109 cards in less than an hour.

Errm, not the first use. Your system shuffles the numbers 1 to 10, which is different from rolling a d10 ten times. Unless you reroll for every result already rolled.

To simulate a d10, just use INT(RAND()*10+0.5)
I used this function in a rather complicated spreadsheet to simulate the results of a number of die rolls for a game I was developing. Although tests showed that it worked, I later switched to a diceless system.

The trick to use RAND to simulate dealing cards is not new (to me at least); I believe that (some) Magic players use it to test their decks for starting hands.

I use spreadsheets to calculate probabilities of dice rolls. Handy for some role playing game mechanics. If you have a (roll three dice, pick the highest) system, for example, you can fill the spreadsheet with every combination possible, use MAX to determine the highest roll and then count how often, say, you don't roll higher than 4 (using COUNTIF).

Did you know that you can use Excel for Monte Carlo simulations? There are a few extensions available with which you can do so. Try and google for them.
3 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Matt Robertson
Canada
Regina
Saskatchewan
flag msg tools
Life is Short; Play Games!
badge
The BixCON Series of gaming events: A combination of Great Friends, Great Food, & Great Drink!
Avatar
mbmbmbmbmb
kurthl33t wrote:
Bixby wrote:

Autofilters are very versatile as well.


Autofilters?


Very easy to use, here is a brief article explaining filters:
http://www.contextures.com/xlautofilter01.html
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Confusion Under Fire
United Kingdom
Warrington
Cheshire
flag msg tools
designer
Avatar
mbmbmbmbmb
Whymme wrote:
whatambush wrote:
This could be used to represent 10 different die rolls on a 1D10

Errm, not the first use. Your system shuffles the numbers 1 to 10, which is different from rolling a d10 ten times. Unless you reroll for every result already rolled.


What I should of said was 10 different results on 1D10. You can of course use it to generate die rolls on die that do not exist eg 13, 21, 77 etc.

1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Kevin Brown
United States
Macon
Georgia
flag msg tools
badge
Avatar
mbmbmbmbmb
It is perhaps worth noting that "random" numbers generated by a computer are not truly random, but merely pseudo-random.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Caleb
United States
Seminole
Florida
flag msg tools
badge
Avatar
mbmbmbmbmb
pilight wrote:
It is perhaps worth noting that "random" numbers generated by a computer are not truly random, but merely pseudo-random.



Oh, boy, here we go.

shake
3 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Nate K
United States
Utah
flag msg tools
designer
Avatar
mbmbmbmbmb
pilight wrote:
It is perhaps worth noting that "random" numbers generated by a computer are not truly random, but merely pseudo-random.


Indeed. I've been using random.org a lot to better simulate dice rolls. (They don't use a digitized pseudo-random number generator.)

But even pseudo-random can be useful in many applications.
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Front Page | Welcome | Contact | Privacy Policy | Terms of Service | Advertise | Support BGG | Feeds RSS
Geekdo, BoardGameGeek, the Geekdo logo, and the BoardGameGeek logo are trademarks of BoardGameGeek, LLC.