Spreadsheet maths with dice

[Krynn72]

Sup, anyone familiar with spreadsheet programs? Open Office's in particular?

What I'm trying to do is make a spreadsheet that can calculate the spread, average, and probability of success for die rolls. I want to be able to enter in different dice that will add or subtract from "0" and have it crunch the numbers. For instance, if I put in a six sided die as a "negative die" and a 12 sided die as the "positive die", it would crunch the numbers and tell me the probability of getting a result higher than 0, what the spread would be (in this case it would be -5 to 11) and what the average is.

But I need to be able to enter in multiple dice for negative and positive dice. So like for the negative dice it could have a d6 and a d8, while the positive dice have a d4 and a d10. I dont use software like this often so i dont know how to go about setting it up. Is there a way to have the program "understand" what a "d6" is and have it know how to add and subtract it from a "d8"? Like, make "d6" a defined variable so I could do "d6 + d8".

 

[Yorick]

Nerd.

I'd be surprised if such things hadn't been created already - I don't know about usage with a spreadsheet, but there are definitely dice roller programs out there, and given the number of RPG systems that use positive/negative dice, I'd expect what you want already exists?

 

[Krynn72]

What are some games that use this sort of system? I dont recall encountering any that do it this way.

 

[Vegeta897]

Working on this. I have the spread calculation done. I assume the average isn't just a simple calculation between the 2 ends of the spread right? It would have to be weighted based on probability?

Like if you were to calculate by hand, you'd write out all possible results, then take the average of those numbers.

 

[Krynn72]

I assume the average isn't just a simple calculation between the 2 ends of the spread right? It would have to be weighted based on probability?

Correct. How are you doing it? Just in code, or in an actual spreadsheet?

 

[Vegeta897]

I'm doing it all in a spreadsheet. You'll be able to just type the number of sides of the positive and negative dice (up to 3 each, but I can do more if you need) and it will spit out the numbers you're looking for.

I finally worked out a formula that finds out the total sum of any number of dice with any number of sides per die, just through a bunch of trial and error
Like, you can put in a d4, d6, and d10, and it will tell you the sum of all outcomes.
Now I just have to work that into the counteracting of positive and negative dice.

Edit: Finally worked out the formula to find the average. Now for the percent chance of a positive value...

Edit 2: Okay, wow, I'm an idiot. I had this big formula to calculate the average, when in reality, apparently the average really is the average of both ends of the spread. The ****...

Still stumped on how to get the % positive

Edit 3: I can't believe I've been at this for over 4 hours.

 

[Vegeta897]

I give up for now on the positive percentage thing.

Here's my sheet that gives the spread and average. Though I'm sure you could have gotten this far on your own.

www.pixelatomy.com/stuff/dicecalc.xls

 

[Krynn72]

Nice thanks. I made my own version based off yours because I'll be expanding it in the future. Here's how mine turned out with the "chance of success" (which, in this game would be a result of 0 or greater).

https://rapidshare.com/files/1460364338/DiceMechanicVisualization.xls

 

[Yorick]

What are some games that use this sort of system? I dont recall encountering any that do it this way.

I'd have to look, because I don't own them, but I've definitely played a couple before that are like that.

 

[Vegeta897]

That percentage is wrong. With negative dice of 3 sides, 2 sides, and positive dice of 2 sides and 2 sides, the chance of getting a non negative sum is 50/50. Yours says 40%.

 

[Krynn72]

Yeah, well... nobody plays with two sided dice. :arms:

 

[Vegeta897]

The point is your math is ****ed
The only reason I'm using sides that low is because you can actually work out all possible results and validate the outcome yourself.

 

[Krynn72]

The point is your math is ****ed
The only reason I'm using sides that low is because you can actually work out all possible results and validate the outcome yourself.

I changed it to =(H6/(F11 - 1)) I think that should do it, right?

 

[Vegeta897]

A lot closer but still off in a few 4 dice cases, and not right at all in the 3v3 cases I tested, but those take forever to do.

 

[Krynn72]

I realized my calculations for the min and max were off because it would only ever be + 1 or - 1 of the total, regardless of how many dice. I've reworked it to fix that. So now the total min takes in to account how many positive dice there are, and vice versa. Too tired to test now, I'll have to revisit it in the morning.

Also it only works if you enter in numbers from left to right for each set. For example, if you enter in values for D3 but leave D2 blank it doesn't calculate correctly.

https://rapidshare.com/files/4230339967/DiceMechanicVisualizationEDITED.xls

 

[Vegeta897]

I realized my calculations for the min and max were off because it would only ever be + 1 or - 1 of the total, regardless of how many dice. I've reworked it to fix that. So now the total min takes in to account how many positive dice there are, and vice versa. Too tired to test now, I'll have to revisit it in the morning.

Also it only works if you enter in numbers from left to right for each set. For example, if you enter in values for D3 but leave D2 blank it doesn't calculate correctly.

Mine already did all this correctly.

 

[TechnoHippyChic]

Edit 2: Okay, wow, I'm an idiot. I had this big formula to calculate the average, when in reality, apparently the average really is the average of both ends of the spread. The ****...

Still stumped on how to get the % positive

So, this isn't right?

 

[Vegeta897]

What have you DONE

Spoiler

NO

Spoiler

NOOOOOOOOOOOOOOOOO