PDA

View Full Version : How many of you use statisical spreadsheets?



Shockwave
09-03-2006, 08:13
Quite simple really, how many of you use Excel (or other form) to calculate the cost effectiveness of certain weapons or troop types?

For the record, i do.

starlight
09-03-2006, 08:19
I've been playing long enough that all things hobby related interest me. If I had one, I'd mess around with it for fun.

However I've also been playing long enough that I'd probably still ignore what's *best* and take the stuff I like.:p

violenceha
09-03-2006, 08:20
Can you post an example?

Snakebite
09-03-2006, 08:21
In school, one of my IT projects was to make a database that explored just that. Now though, I think I do too much of that at the expense of actual gameplay and painting...

starlight
09-03-2006, 08:22
It sounds like there are few out there....any chances of sharing?

Shockwave
09-03-2006, 09:23
Can you post an example?

An example??

As in an example of how kroot are statiscally better then tau, or an actual spreadsheet? because the spreadsheet can't be posted...

Yog_Sothoth
09-03-2006, 09:51
Iīm too lazy to make statistics of my soldiers success, but heck I remember quite well which troops earned their points.

Colonial Rifle
09-03-2006, 10:46
I don't keep a spread sheet, but sometimes a 'run the numbers' when picking weapons for new units - usually rolling the dice a few ties for hypothetical situations. Table top experience is vital though - theoryhammer can get you into all sorts of trouble.

boogle
09-03-2006, 11:19
meh 90% of statistics are madeup on the spot, i'm one of those who'll take stuff i like, and find a way to best use them effectively, even if they could be statistically worse than other units

Vanger
09-03-2006, 11:26
Funny, first I read it for sadistical spreadsheets...

I don't "do the numbers", I take, what I like, what kind of game I wish to play (can't do much with high CC effectiveness, if I want to play shooty...), or I find it would be useful based on gameplay experience.

Khaine's Messenger
09-03-2006, 11:31
I don't. Mainly because I'm not a fan of playing the numbers game, and I'm not especially skilled in setting up good scenarios to base statistical analysis on in the first place.

Venkh
09-03-2006, 11:37
I use them but not in any formal fashion, i just knock up a quick formula if i want to see how likely a particular event is in the game.

Destroying skimmer tanks with holo-field and spirit stone springs to mind.:evilgrin:

The method is very easy, the probability of getting a certain result on the dice can be represented as a fraction. So if you need 3's to make an armour save that is four chances in six, or 4/6.

The avaerage number of passed saves then can be determined by multiplying the number of dice you need to roll by the fraction.

so:
=SUM(12*4/6)

easy really.

susu.exp
09-03-2006, 16:44
I could do them and have done them whenever I got a new statistical tool. Itīs so much nicer with the standard deviations then "about half of them hit" isnīt it (I actually love to do statistical analysis and when Iīve run out of data for serious things, Iīll just analyze whatever data Iīve got around)? Then again, I donīt consider this stuff to be interesting when picking an army, just because you Ignore the "soft skills" of units, how far they can move, if they have a transport option, how they handle terrain, infiltration, deep-striking, etc. Yes, statistical analysis can show you how two squads compare, when they are maxing their effectiveness, but it will fail at analyzing how good they fare at getting to use it. Try to caluclate the "fear factor" (ammount of firepower brought to bear against unit X, because of the opponents expectiation of the damage itīll do).

athamas
09-03-2006, 16:47
i have used it in tems of aprox. how many marines will die from this much fire, but nothing much more!

too much effot!

priamus
09-03-2006, 17:02
i did a prety coplex excell sheet to ilustrate pulse shot effectivness when i was starting out. still doing the ods whenever i can.

MadJackMcJack
09-03-2006, 17:10
Nope. I go with what looks cool, not with what is statistically most effective.

bryankia
09-03-2006, 19:52
Not only do I run the numbers but I have created an Access DB to do some of the work for me. I am currently working on a web page that will give efficiency of shooting along with wounds from both a single model to a squad. All numbers at all toughness and range.

Bryan

PS don't get me wrong sometimes I just have to take the cool and fluffy units. but when I do it is an informed decision :)

RampagingRavener
09-03-2006, 20:04
Nope. I go with what looks cool, not with what is statistically most effective.

Took the words right out of my mouth. I don't think I have ever, or will ever, build an army list that was designed with the most powerful units and nothing else.

skyfyre
10-03-2006, 04:16
Sometimes I run the numbers a few times to calculate probabilities and averages for complex things just because I'm curious. Other times to dispute tactical advice from people. And yet other times because I want to know optimal configurations.

However, I treat the numbers with a pretty big grain of salt. Perhaps a cube of salt. Perhaps sometimes, even an entire salt block. It's easy to come up with some figures and say which gun or which unit would be best in a given situation but it's not trivial to actually get it to work that way in actual combat given all the intangibles such as movement, deployment order, the presence of supporting units, and what your opponent ultimately decides to attack and ignore.

Storm guardians for example are terrifyingly bad at killing marines in hth and absorbing bolter fire, all in one somewhat inexpensive by eldar standards package. However, I was able to drop a squad of 10 of them next to some fire dragons in a battle, and my opponent was so scared of his bolters being tied up in hth and having line of sight blocked, he focused 3 tac squads of bolter/plas fire on them to wipe them off the board completely, instead of focusing on the real threat, the fire dragons.

For a trivial cost in points in sacrifical guardians, I was able to double the life of my fire dragons, extend the ammount of time they were optimized at what they do (short range armor and marine killing) and they even managed to get back in their wave serpent, pop out next to a LR crusader and kill it, and remain a scoring unit at the end of the game.

Had I taken a statistically better unit like say, banshees or striking scorpions, they would have just been a huge points sink when they were gunned down by the same said 3 tac squads, albeit slightly slower.

This one little event has changed my outlook a lot on what is effective and what is not in the game.

mongoosedog300
10-03-2006, 08:56
I take what i think will get the job done e.g assault squads against tau, shooty against khorne and so on.

plus this is a game of chance

Insane Alex
10-03-2006, 09:45
I'm wondering, how does Rending factor into an equation? I mean, you can't just go "+(A1*1/6)" as you would eventually get more kills than the unit has attacks, which doesn't work.

Shockwave
10-03-2006, 09:52
Rending and results that come from "Roll X dice pick Y" are thing that my speadsheet can't do.

However, give me pen, paper and a calculator then I can.

Insane Alex
10-03-2006, 09:54
Yeah, it's sort of wierd having a 4 shot Assault Cannon get 4.88888888 kills against T3 foes... >.>

Scythe
10-03-2006, 09:56
You might call me crazy, but I do most of these calculations just in my head. It is not that hard to calculate the average number of casualties a marine unit with heavy and special weapon does on unit X. For really complicated things, I might get a calculator, but I am usually not bothered enough for that. It isn't all about statistics.

Insane Alex
10-03-2006, 09:59
True enough. But it's nice to have spreadsheets to tally up the number of wounds against specific targets. Sure one Marine kills 1/2*1/2*1/2 Genestealers in CC, but how many wounds do 7 bolters cause against those same Genestealers?

Toastee
10-03-2006, 11:17
I would proberly give it a crack if I could remember how lol. But alas, the only thing I really use Excel for is my army lists.

Pyro8
10-03-2006, 11:21
Scythe: Donīt worry, I count it in my head as well, youīre not alone. Calculator can help, but when someone is not dumb and uses for example 2/3 instead of 0,6666..., it is not that hard.

Alex: The rending - you take all shots that rend and deduct the number that actually rended.
For example - AC shoots at squad of SM.
4 shots, 2/3 chance of causing a rending hit (4*1/6), which is equal to kill.
4-2/3=10/3
10/3*3/5 (3,4,5 is good, 1,2 isnīt, the 6 is out as it rends)*5/6*1/3=10/18

So it should inflict 2/3+10/18=22/18 wounds.
I may be wrong but I believe it is that way.


And to the 7 bolters
7*2/3*1/2*1/2=14/12
if they shoot two shots due to rapid fire, it is
28/12

and if gens donīt have the carapace armor, it is 28/12 at long range, 56/12 at short one.

Scythe
10-03-2006, 11:29
Alex: The rending - you take all shots that rend and deduct the number that actually rended.
For example - AC shoots at squad of SM.
4 shots, 2/3 chance of causing a rending hit (4*1/6), which is equal to kill.
4-2/3=10/3
10/3*3/5 (3,4,5 is good, 1,2 isnīt, the 6 is out as it rends)*5/6*1/3=10/18

So it should inflict 2/3+10/18=22/18 wounds.
I may be wrong but I believe it is that way.


Well, the normal shots can be calculated easier:

4 shots * 3/6 (the chance of rolling a 3,4 or 5, eg a non rending hit) * 5/6 * 1/3 = 10/18

Just add the 2/3 wound from rending after that like you did.

Nice to see I am not the only one who calculates in his head....:D

Latro_
10-03-2006, 13:26
I wrote my own online program to do it all.
I forget the link. I could dig it out if anyone is interested?

Pyro8
10-03-2006, 13:39
Scythe: Hmmm, I am far from being certain it will work all the time. I see it worked in this case, but I donīt know if it will work in all. Could you clarify that consecution please? I am probably too tired now :-)

Crube
10-03-2006, 13:59
For army lists, yes, for effectiveness, No...

I personally dont bother that much with the Theory side of things...I much rather leave it to the dice gods, as it is they who determine my armies fate (9times out of 10 any way)

philbrad2
10-03-2006, 14:52
Used Excel as the basis for a VB based 'Army builder' my programming mate did. Also use Excel for regular army lists - I'm begining not to trust AB files.

As for keeping stats sheets. Why? Surely your experience of units in gaming should give you at least a 'feel' for how effective they are :wtf: Why go to the lengths of raising a spreadsheet for them.

Each to their own I suppose... I voted NO BTW :D

:chrome:

scramasax
10-03-2006, 15:30
I use matlab. this is killing flies with a nuclear bomb but I am able to do things like

How many hits can you expect if 4 stealth suit are shooting?

0 hit= 0,02% of the time
1 hit= 0,3%
2 hit= 1,6%
3 hit= 5,3%
4 hit= 12,8%
5 hit= 19,3%
6 hit= 22,5%
7 hit= 19,3%
8 hit= 12,8%
9 hit= 5,3%
10 hit= 1,6%
11 hit= 0,3%
12 hit= 0,02%

The average is easy to calculate in your head since 12 shots at BS3 would give 6 hits but how often you will get that average is asking for more calculations that cannot be done in your head.

lord_blackfang
10-03-2006, 15:43
I like "number crunching," but I do it all in my head, with no need for Excel.
So does this count as a yes or no to the original question? :eyebrows:

Spell_of_Destruction
10-03-2006, 15:54
I don't get caught up in number crunching as it is only part of what is required to build an effective force, but I definitely give it some consideration. After a while though experience takes over. Far more vauable than number crunching.

Scythe
10-03-2006, 19:42
Scythe: Hmmm, I am far from being certain it will work all the time. I see it worked in this case, but I donīt know if it will work in all. Could you clarify that consecution please? I am probably too tired now :-)

It is not that difficult, really. You just leave out the chance that you roll that 6 for rending (which is calculated seperately). Essentially you treat the BS4 firer as a BS3 one, and add the chance of rolling a 6, thus scoring a rending hit. (chees, that sounded a lot more complicated as I intended it. hope you get what I mean...:p )

Great_Destroyer
10-03-2006, 21:12
I use it all the time in school building armies though not effectiveness of the weapons or armies. Very easy to do!

Da Reddaneks
10-03-2006, 21:48
i actually just finished doing a statastical analysis in excell on orks which determine what are the best units on a point for point basis offensively and defensively. It is not "what is the best unit" but "how effective is a unit for what you are spending on it." It gives you a quick sheet that you can look at and look at a score to determine how good a unit is offensively (point for point) and then how good a unit is defensively (point per point).

i also did the same thing for nids.

there were actually some very surprising results. if anyone is interested in a copy they can just private message me and i will be happy to share.

Shockwave
11-03-2006, 03:38
I once did something similar for "Hunting the Leopold" with a Tau army. The Deathrain Crisis came first as you might expect, but second place was a little suprising....

StarFyre
11-03-2006, 04:08
I did this to prove to someone that archaon was NOT invincible in WHFantasy.

People keep arguing they can't beat him, that it's impossible.

I use lizardmen, so I showed via a lizardmen perspective, that it's not easy but it can be done in close combat and stat wise, it's not THAT far fetched.

Oldblood on carnosaur with weapon that ignores armour saves and item that gives a 4+ ward save, with 3 spawnings, vs archaon.

my playtests came out 6-4, 7-3, 7-3 in favour of archaon..BUT you expect that due to his higher point cost. HOwever, in terms of hitting and wounding rolls, arc and oldblood have same probability. Arc though, once he unleashes his sword's demon gets more attacks..that and his slightly better ward save are what bring him out on top really (i ignored his spells, since most lizardmen armies have a priest, and a cube of darkness or scrolls could stop arcs spells easily)....anyways.. my probability table just showed that, any races with awesome close combat characters (with some excpetions) can take on archaon and it's not like PURE luck. they actually have the items/stats to do it.

later though, i beat arch in close combat with a character that had NO attacks..hehe ...and it was like 8-2 in favour of that cahracter so whatever :) hehe

Sanjay

Scythe
11-03-2006, 22:21
I did this to prove to someone that archaon was NOT invincible in WHFantasy.

People keep arguing they can't beat him, that it's impossible.


Ahh, but the real way to do it is just beat him in game. I faced him twice with my VC army, and seperated his head from his torso twice with my lord. :D

My chaos opponent now knows not to take Archaon against Blood Dragons anymore. ;)

Jericho
12-03-2006, 00:44
Don't have to, it's easy enough in my head. Some times it means pausing for 10 seconds before I pick up the dice, but I usually know the odds of achieving what I'm trying to do rather specifically.

Oh and for argument's sake, give me 220 points of Pikemen and I'll get rid of Archaon for you :D Who says you have to do it with a character? Just don't give him an out... what's he gonna do shoot you?