In order to use the calculator, you'll obviously need to download
and unzip it first, after which you should be all set... unless of course you don't have MS Office or some Excel analog installed on your computer, although I doubt anyone can live without this.
After opening the spreadsheet, quickly navigate to the "Scatter Sim" tab which contains the main calculator and should resemble the image below:Main window
This is where you can set the parameters of the simulation and view the results from a couple of tables and handy graphs for visualization:
You'll notice that, due to the way Excel handles volatile functions, the sheet will update automatically whenever you change any cell value. This happens quite quickly on my 3yo laptop, but could take significantly longer on older machines due to the unoptimized cell logic (sorry). For those who feel this is slightly annoying there is a button to toggle the calculation mode to manual, which lets you update the sheet only when you want it by pressing F9.
To start a scatter simulation, first select a shooter from the dropdown menu (L2:M2, J2:K2 to select specific categories) and set the parameters for distance to target (D2), max number of shots (A2, must be between 1 and 500) and one-hit kill threshold (E2, should typically be 80 HP, but can be set to lower or higher numbers to simulate cover or damage reduction). If desired, additional modifiers for firing into the FoW (G2) or on the move (H2) can also be enabled or disabled.
Next, the number and formation of the target entities can be selected from the "Formation" menu (N5), with the option of either using one of the 6 formation presets (big thanks for Hannibal for measuring and providing the in-game templates 4-6) or generating your own squad formation using the custom builder or the formation randomizer.
With these set, the targeting mode (N7) can be adjusted to either randomly pick a model to target ("random"), pick the entities with the lowest number first ("fixed") or fire at the center of the template instead (this disables target switching and should be chosen for barrage-type weapons).
Automatic target switching after a model dies can be enabled (N11) or disabled to target specific models only.
Lastly, the "Position" dropdown menu changes how the simulation handles the position of the shooting entity and the target. Choosing "relative" keeps the distance and angle between shooter and every entity targeted constant. As a result, the shooter is 'teleported' each time another model in the formation is targeted.
A more realistic option can be selected by choosing "absolute", which locks the position of the shooting entity in place and only changes the firing angle upon switching targets. However, since in this case the true distance between shooter and each entity in the formation may be greater or smaller than the respective minimum and maximum range of the shooter, the distance set in cell D2 may need to be adjusted accordingly.Difference between "relative" and "absolute" position mode
Using the input variables, the calculator will simulate shots fired at the target squad until the last model has received fatal damage. For each shot, an accuracy roll first determines if a natural hit of the targeted model occurs or if the shot scatters to a random point within the scatter cone. Then, the distance between point of impact and each model is calculated to check if and how much AoE damage is dealt. If the targeted model dies, the model with the next-highest priority is selected and targeted for the following shots until the simulation ends.
Detailed results and timestamps can be viewed in the table from columns T:AU and should look something like this:Main calculation table
Alternatively, the main results are summarized in the leftmost table (A5:H10), showing the number of natural hits, one-hit kills, shots required to kill, and, in case not all models died within the set limit of fired shots, the entities and respective HP left after the simulation finished.
For visualization, the two upmost graphs provide information on the shot distribution (i.e. where each shot landed with respect to the target formation), while the 3rd graph displays the area of the scatter cone and size of the AoE circle. Last but not least, the AoE curve can be seen in the last graph.Numbered shot distribution display indicating shot sequenceColor-coded shot distribution display indicating targeted entityAoE and scatter area representationAoE curve display
2.4) Enumeration, a.k.a. increase sample size
Now being able to see the results of a simulated fight is neat and all, but it still doesn't give any more information on the overall performance than an isolated test in-game would provide.
However, in contrast to in-game testing, firing 50 simulated shots at a target using the spreadsheet is a good deal faster (hopefully) than any in-game test would allow, so we can use this to our advantage to conduct hundreds or thousands of simulated runs in mere minutes as opposed to hours or days.
Still, copying a result and hitting F9 thousands of times in a row to get a statistically relevant result can be quite tedious and unnerving to say the least.
Fortunately, the "Scatter Enum" tab is there to save the day (literally), allowing to repeat a simulated fight for up to 10k iterations and further queue up other simulations to be processed one after another.
All you need to do is to select a range, entity, formation combination in rows 1:3, set the number of iterations of each simulation (BT4), the number of columns to process (BT5), and, most importantly, the desired result to iterate from the dropdown menu (BS3).
With this all set, start the iteration with the "Run calc" and wait (this could take quite long, depending on the number of iterations and the speed of your rig, so proceed with caution) for the macro to finish.Enumeration calculator interface
Afterwards, you can use the "Analyze" buttons for each dataset on the right side to perform a basic analysis, giving the minimum, maximum, average and standard deviation over all repetitions in the upper panel, as well as the probability distribution for each event in the dataset (i.e. how many times the simulation ended after 10, 11, 12, etc.. fired shots).Analyzer for Enumeration results