Alright with my limited Excel and statistics knowledge I have created a functional "Hit probablity Calculator." This will show you the probability of each number of hits for a given dice pool. It is easy to use. Open up the file. in the top right hand corner there is are several spaces for various dice pool. Press enter, and Vola! you have the probability for all possible hits 1-30 and a graphical representation. I will be using this for future projects when anylazing the odds.

The formula used for the odds of each specific number of hits is P=(1/3)^h*(2/3)^(T-h)*(T!/(h!*(T-h)!)
P= Probablity
h= "target" number of hits
T= Total Dice pool
note that the ! means that the number before it is factorial. (I never heard of it before I did this)

My next step is going to be analyzing the odds of getting X number of hits above the opposed dice pool. In the graphs its easy to see who has a better chance for success but as noted i'm bad at calc. I will work on this later. An openoffice version of the sheet is in the works. If anyone knows an easy way to change excel 2007 into an open office compatible format, please let me know.



VR2 Excel Hit Probability Calculator

This doesn't work with my OpenOffice 3. Was this made with Excel 2007?

I get missing operator and missing parenthesis/brackets errors.

It seems to work on Google Drive, but I can't change the dice pool then.

I made a hit calculator spreadsheet. Let me know if you want me to email you a copy.

Yes this was made with Excel 2007

I'll play with it. I think if I change [#This row] to [@Column1] it might work.

I have open office at home. When I'm done with V.2 I'll double it over into open office. Should be pretty easy.

I went a different path:

Using open office, I make a table for #hits, row holds number, column holds percentage.

Next row: chance of x hits on the attackers part is calculated using "=BINOMDIST(B4; B1;1/3; 0)"
where B4 is number of hits, B1 is dicepool, 1/3 chance to hit, do not accumulate lower hit numbers)

Next row: attacker having more hits than the defender is calculated for each number of attacker hits using "=BINOMDIST(B4;B1;1/3;0)*(BINOMDIST(B4-1;B2;1/3;1))
where the function of above is extended by multiplying with the probability of the defender having less hits (B4-1), a different dicepool, adding probabilities of 0 up to B4-1 hits. This will return errors for the cells without possible values.
Then 1 add the probabilities up over all possible number of hits using SUMIF to grab only values >0 (to get rid of the error values). That value should be the probability to hit however well.

V-2 is up. I'm looking forward to continuing this little project. This was made in Excel and open office appears to hate it.

Cool idea! A thought just sprang (sprung?) to mind, maybe also take into account Limits, which could cause a different probability distribution for hits.

I'm trying to figure that out exactly. Basically if you had a limit of 4 you would add all the % chances that you would get 4 or greater than 4 hits together, and that would become your new probability of having 4 hits. Its simple enough math but I'm not sure how to get that to work in excel.

I haven't tested anything, but I think the problem is a difference between Excel 2007 and Excel 2010.

I made a spreadsheet using the binomial distribution that shows a huge range of probabilities. I can mail it to you and you can put it in your dropbox to share.