It is currently Thu Aug 16, 2018 7:23 pm

All times are UTC




Post new topic Reply to topic  [ 12 posts ] 
Author Message
 Post subject: Dice probability calculator for Excel
PostPosted: Mon Mar 07, 2011 4:25 am 
Offline
User avatar

Joined: Sun Oct 10, 2010 9:46 am
Posts: 29
Location: Bunbury, Western Australia
Since I have been lurking away for a few months now, I thought it was high time that I introduced myself.

I'm very new to The Riddle of Steel. I'm a D&D player since way back, in a sporadic way - every year or two I would gather some friends and host a one-off adventure over a night or three. More recently, we shifted from 3.5 to 4th edition. I've tried hard to like it, but it fell pretty flat in some ways - prompting me to wonder, what do I really want from an RPG?

Imagining that there would be a plethora of well honed, generic RPG options out there to choose from, I was rather shocked by the reality. I found myself gravitating to smaller indie offerings, and I eventually read about Burning Wheel on The Forge. I decided that that this was what I wanted: an RPG that made characters feel alive and vulnerable, and encouraged an evolving storyline and player involvement over mindless hack'n'slash play. I bought the entire set of BW rulebooks and spent some months poring over them.

I can't put my finger on exactly what bothers me about BW. There was certainly a lot to like, and I simply love the character burning process. The game wasn't really designed to suit our sporadic playing style, and the hesitation rules (stand and drool, anyone?) certainly turned me off a bit. I also find the rules a bit overly complicated, and I don't think I'm particularly slow when it comes to crunchy rules. Maybe it just isn't for me. While still working on BW I started scanning for other similar indie offerings, and eventually I turned up at Trosfans.

Ian and Michael (Grettir) will remember me as the lucky recipient of a new copy of The Companion, after I learned about the fate of TRoS and gladly contributed to the Trosfans site. I also bought the pdf's through Ian, and shortly afterwards in a fit of enthusiasm, I scoured the web and bought a secondhand hardcopy of the Core rules at a horrendous price. But it was only a few weeks ago that I picked it up and had a good read.

I love the whole feel of TRoS. Certainly the rules sport some holes and contradictions, but I'm quite comfortable dealing with these - some of my best RPG experiences were with 1st edition D&D, where we pretty much made up anything we weren't sure about. I simply love the combat, and I'm fascinated by the breathtaking ambition of the Core magic rules - I'll probably apply my own tweaks, but I'll enjoy messing with it. Finally, while I have no idea what the TRoS community was like in its heyday, I have to say that this forum is pretty impressive. Perhaps it is because the community is small, but the atmosphere is welcoming and positive and I really appreciate the help that I have already received from a couple of members.

So, I would like to humbly offer something back. Yesterday I started playing with Excel because I wanted to explore d10 probabilities using the TRoS mechanics of Target Numbers and Successes. I know there is an old Windows utility out there somewhere, but it is no longer easily obtainable, and I'm not a Windows user anyway.

What began as a little exploration ended up as a personal challenge: could I write a single cell formula that would calculate the probability for any combination of Dice Number, TN, and Number of Successes? After a few hours of work I believe I achieved this, and although I can't guarantee it's accuracy, I have tested it successfully against first principles for a variety of inputs. I've created a spreadsheet which applies the formula in a couple of different ways - anyone with a little Excel experience will easily modify it to suit themselves. The beauty of a single cell formula is that it can be easily embedded into tables as a self-contained entity. There are no macros, so the file shouldn't offend antivirus software. The formula does make use of a rather important "Combinations Table" at the back of the spreadsheet - if you copy the formula to a new spreadsheet, you will need to copy the table as well. Finally, I haven't protected anything, so please be careful with changes, and test it against the original afterwards.

I have forwarded the file to Ian, but in the meantime, I can send it to anyone who is interested if you just PM me. The spreadsheet was created with Excel 2007, but I can also supply a 2003-compatible version if that is more suitable.

I look forward to sharing my TRoS experiences with you all as they unfold...

Edit: (17 Apr 2011) I strongly recommend downloading version 2 of this calculator, linked further below, rather than this one. Version 2 includes a contested roll probability calculator. Besides giving the overall odds for winning a contested roll, this calculator also derives and plots the probability bell curve for each possible number of successes, for each opponent - so you can see how many successes are most likely.

Edit 2: oops, looks like my editing caused version 1 to become detached anyway! Version 2 is below. :? (sidles away, whistling softly)

_________________
"If you wish to make an apple pie from scratch, you must first create the universe."
- Carl Sagan

"You can't have everything. Where would you put it?" - Ann Landers


Last edited by Cogs42 on Sun Apr 17, 2011 12:38 pm, edited 2 times in total.

Top
 Profile  
 
 Post subject: Re: Dice probability calculator for Excel
PostPosted: Mon Mar 07, 2011 7:00 am 
Offline
User avatar

Joined: Fri Jan 18, 2008 10:43 pm
Posts: 2112
Location: Melbourne, Australia
Cogs42 wrote:
I have forwarded the file to Ian...


... and I have attached it to your post! :)

So everyone can download it directly.

If you feel like a "version 2", it would be great if it handled contested rolls:

PC had 9 dice and TN 6.

NPC has 11 dice and TN7.

What is the probability that the PC will win the contest?

Cheers,

Ian P.

_________________
Ian Plumb
Illustrations for Gamers
Lyonpaedia
Griffin Grove Gaming
Kraftworks for Kids School Holiday Program


Top
 Profile  
 
 Post subject: Re: Dice probability calculator for Excel
PostPosted: Mon Mar 07, 2011 9:23 am 
Offline
User avatar

Joined: Fri Jan 18, 2008 10:05 pm
Posts: 2035
Location: Estonia
That's quite an introduction. :) Welcome to the boards!

But yeah, I'd appreciate the older .xml file version.

_________________
"Brothels are a much sounder investment than ships, I've found. Whores seldom sink, and when they are boarded by pirates, why, the pirates pay good coin like everyone else."
- Lord Petyr Baelish, A Game of Thrones


Top
 Profile  
 
 Post subject: Re: Dice probability calculator for Excel
PostPosted: Mon Mar 07, 2011 11:02 am 
Offline
User avatar

Joined: Fri Jan 18, 2008 10:43 pm
Posts: 2112
Location: Melbourne, Australia
higgins wrote:
But yeah, I'd appreciate the older .xml file version.


.xls?

Cheers,

Ian P.


Attachments:
File comment: XLS version
TRoS Probability Calculator v1.xls [511.5 KiB]
Downloaded 323 times

_________________
Ian Plumb
Illustrations for Gamers
Lyonpaedia
Griffin Grove Gaming
Kraftworks for Kids School Holiday Program
Top
 Profile  
 
 Post subject: Re: Dice probability calculator for Excel
PostPosted: Mon Mar 07, 2011 11:34 am 
Offline
User avatar

Joined: Sun Nov 16, 2008 10:00 am
Posts: 511
Location: Cary, North Carolina
Welcome aboard! Yep, copies of the core rules are terribly hard to find. It was once a bit easier. I have owned five and have one left for myself at this point, having given a copy to my son and the other three to gamer friends.

Regards,

Phil

_________________
Image


Top
 Profile  
 
 Post subject: Re: Dice probability calculator for Excel
PostPosted: Mon Mar 07, 2011 12:43 pm 
Offline
User avatar

Joined: Sun Oct 10, 2010 9:46 am
Posts: 29
Location: Bunbury, Western Australia
Ian.Plumb wrote:
If you feel like a "version 2", it would be great if it handled contested rolls:

PC had 9 dice and TN 6.

NPC has 11 dice and TN7.

What is the probability that the PC will win the contest?

Cheers,

Ian P.


Ian, I've sent you v2 of the spreadsheet, which now includes a "Combat Odds" page. Just type in the number of dice (limit of 40) and TN for each opponent.

The above is quite a close match, but the PC has a slight edge with 43.2% chance for a win, while the NPC has 39.1% chance. There is a 17.8% chance of a tie. :D

_________________
"If you wish to make an apple pie from scratch, you must first create the universe."
- Carl Sagan

"You can't have everything. Where would you put it?" - Ann Landers


Top
 Profile  
 
 Post subject: Re: Dice probability calculator for Excel
PostPosted: Mon Mar 07, 2011 4:32 pm 
Offline
User avatar

Joined: Fri Jan 18, 2008 10:05 pm
Posts: 2035
Location: Estonia
Ian.Plumb wrote:
.xls?
Duh! Yes. :)

The file doesn't work in Office 2003 though... gives #NAME? as a result everywhere. :(

_________________
"Brothels are a much sounder investment than ships, I've found. Whores seldom sink, and when they are boarded by pirates, why, the pirates pay good coin like everyone else."
- Lord Petyr Baelish, A Game of Thrones


Top
 Profile  
 
 Post subject: Re: Dice probability calculator for Excel
PostPosted: Mon Mar 07, 2011 8:05 pm 
Offline
User avatar

Joined: Fri Jan 18, 2008 10:43 pm
Posts: 2112
Location: Melbourne, Australia
The bad news: The #NAME issue is going to relate to some of the functions used in the formula.

The good news: The spreadsheet works in OpenOffice, which is free.

Here is the version that handles Opposed Rolls.

Regards,


Attachments:
File comment: Version 2
TRoS Probability Calculator v2.xlsx [166.33 KiB]
Downloaded 273 times

_________________
Ian Plumb
Illustrations for Gamers
Lyonpaedia
Griffin Grove Gaming
Kraftworks for Kids School Holiday Program
Top
 Profile  
 
 Post subject: Re: Dice probability calculator for Excel
PostPosted: Tue Mar 08, 2011 10:55 am 
Offline
User avatar

Joined: Sun Oct 10, 2010 9:46 am
Posts: 29
Location: Bunbury, Western Australia
Ian.Plumb wrote:
That's quite an introduction. Welcome to the boards!

I need to work on my brevity. ;)

Cogs42 wrote:
Ian and Michael (Grettir) will remember me as the lucky recipient of a new copy of The Companion, after I learned about the fate of TRoS and gladly contributed to the Trosfans site.

I thoughtlessly forgot to mention that Grettir donated the Companion to me, if I donated the cover price to Trosfans. The generosity was all his, and I am deeply grateful!

_________________
"If you wish to make an apple pie from scratch, you must first create the universe."
- Carl Sagan

"You can't have everything. Where would you put it?" - Ann Landers


Top
 Profile  
 
 Post subject: Re: Dice probability calculator for Excel
PostPosted: Wed Mar 09, 2011 10:58 am 
Offline
User avatar

Joined: Sat Jan 19, 2008 8:06 am
Posts: 1495
Location: Vienna, Austria, Europe
Hello Cogs42! It's good to see that you seem to have found TRoS everything you have hoped for. Welcome to the boards - I'm looking forward to hearing about your experiences with the system. :)

And of course: Thank you for your handy probability calculator!

_________________
My real name is Michael; use it, if you like.


Top
 Profile  
 
 Post subject: Re: Dice probability calculator for Excel
PostPosted: Wed Mar 09, 2011 12:58 pm 
Offline
User avatar

Joined: Sun Oct 10, 2010 9:46 am
Posts: 29
Location: Bunbury, Western Australia
Hi Michael! You were right about TRoS - it has me hooked, to the point where I'm buying up big on d10's. I like the Core rules over the Companion options, but I'm still finding a lot of really useful explanatory stuff in the Companion.

By the way, I have managed to purchase hardcopies of TFoB and OBaM for very reasonable prices from Boostep.com.... lucky I didn't pay the 3x price for TFoB that I saw beforehand.

I discovered another Excel-based probability calculator yesterday - which makes mine look a bit primitive, but the (macro-less) single cell function might still be of use to somebody. And the combat calculator in v2 works quite nicely I think.

higgins wrote:
The file doesn't work in Office 2003 though... gives #NAME? as a result everywhere.


Yes, really sorry about that. I didn't realise that the SERIESSUM command is new to Excel 2007 and not compatible with earlier versions. :oops:

_________________
"If you wish to make an apple pie from scratch, you must first create the universe."
- Carl Sagan

"You can't have everything. Where would you put it?" - Ann Landers


Top
 Profile  
 
 Post subject: Re: Dice probability calculator for Excel
PostPosted: Fri May 13, 2011 2:53 pm 
Offline
User avatar

Joined: Sun Oct 10, 2010 9:46 am
Posts: 29
Location: Bunbury, Western Australia
An update to my probability calculator (v3) - it now lists the probability of each individual margin of success, for contested rolls. Hope this is useful to someone (besides me).

I've sent the file to Ian for attachment.

Regards,
Colin.


Attachments:
File comment: Works in Open Office...
TRoS Probability Calculator v3-1.xlsx [167.75 KiB]
Downloaded 268 times

_________________
"If you wish to make an apple pie from scratch, you must first create the universe."
- Carl Sagan

"You can't have everything. Where would you put it?" - Ann Landers
Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 12 posts ] 

All times are UTC


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron


Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group              Designed by QuakeZone