Tag Archives: tool

Iridar’s Weapon Analysis Toolbox

This video is outdated and doesn’t include many new features, but it explains the general purpose and spirit behind this tool.

This Excel Spreadsheet is a culmination of years of work on theorycrafting PlanetSide 2 weapon mechanics. It uses Visual Basic macros to pull weapon stats from DBG API, to calculate damage and recoil statistics and draw graphs to visualize them.

This toolbox arms you with all necessary tools for comprehensive weapon analysis.

Current version: v4e

Attention! When you first open the toolbox, you will see a yellow bar with a security warning about macros. You have to allow the use of macros, or the Toolbox will not function. 

Structure

The Excel file has 7 pages:

Toolbox pages

They contain cells of different color:

soft-orange  Soft orange cells. When you double click or right click a weapon on the Stats page of the spreadsheet, weapon’s stats will be copied into soft orange cells on other pages. These cells also accept manual input. You can edit them without fear of breaking anything.

light-greyLight grey cells contain calculations and references to other cells, do not edit grey cells, or you will break the tool’s functionality.

pale-yellow Pale yellow cells store values for configuration, and you may need to edit them in specific situations.

Toolbox chosen cell Light green filling indicates that this cell’s value has been selected, likely by double clicking.

Toolbox tooltipSmall red triangles in the corner of a cell indicate a tooltip. Put your mouse over the cell to display the tooltip.


Note: Toolbox’s calculations always assume worst case scenario: weapon damage is rounded down and target health is rounded up.

Stats Page

Toolbox stats page

This huge table has stats of all known infantry weapons. 

Normally you don’t work with this table itself, and only use it to export stats into other pages:

Double click or right click on any weapon to copy its stats into soft orange cells on other pages.

Other functions:

Double click on Item ID of the weapon to open a JSON query for that weapon for your default browser.

The Toolbox is not perfect, and may sometimes pull wrong stats, or fail to pull stats at all. There is no substitute for looking at a query with your own eyes. 

Select a weapon and click “Export 1” button. A window with exported weapon’s stats  and attachment list will show.

Pulling stats and adding new weapons

The Pull Stats button will initiate a download of weapon stats from DBG API, using JSON queries for each Item ID in the first column. When a new weapon is released, simply insert its Item ID and Name to the end of the list, and its stats will be downloaded as well. 

If you know weapon’s exact name, you can find out its Item ID this way, or you can simply copy-paste it from Attachments Page.

add-new-weapons

Keep in mind that “Name” column is not downloaded, and you will have to fill it manually for any new weapon releases.

The “Date” field near the “pull stats” button stores the date when stats were last downloaded. Normally, you should re-download stats only after a patch that changed something.

Damage Page

damage toolbox page

This page serves to analyze weapon damage at different ranges and/or with different attachments. You can compare two weapons at the same time.

On “Stats” page, double click on a weapon to export its stats for the first weapon, and right click for the second weapon.

You can add attachments to weapons by clicking corresponding buttons. If the weapon doesn’t have a certain attachment, you will be informed with a pop up window, but you can’t see whether weapon has access to an attachment on this page without clicking the Check Box.

The calculations for Minimum and Maximum damage ranges are self-explanatory.

To calculate weapon’s damage stats at certain range, enter it into soft orange Range cell.

Target Settings

target-health 

Both weapons are simulated against the same target. You can specify target’s parameters on the middle left.

Below the Health block, there are reference tables for Health, Nanoweave Armor and Kinetic Armor. 

Double click on Health or Damage Multiplier value to automatically apply it.

Both weapons have Headshot Damage Multiplier listed. You can double click on its value to apply it to the Damage Multiplier. 

Since both weapons fire at the same target, and damage multiplier is tied to the target, it may be inconvenient to compare headshot properties of two weapons with different headshot multipliers. 


The most common way to analyze a weapon is to look at its performance against the default target with 1000 HP and against a Full Nanoweave target with 1250 Effective HP, so these are the default parameters, and statistics for standard Full Nano target are calculated automatically.

BTK Thresholds

Toolbox BTK Thresholds

The “Calculate Thresholds” button will calculate BTK Thresholds for both weapons.

For example, the results on the picture above read as:

Gauss Rifle kills in 6 shots at 0m to 10m, and in 7 shots at 11m+.

“Draw Graph” button will also recalculate Thresholds and then draw a comparative graph.

Recoil Page

toolbox recoil page

This page serves to analyze weapon’s recoil properties.

Both double click and right click on a weapon on the Stats page will import its stats into soft orange boxes of the Recoil page.

Click Calculate Stability to calculate Average and Maximum horizontal Deviations and to update Probability Distribution Graph.

The Visual Basic macro fires a virtual gun in bursts with listed “Burst Length” for the amount of times, listed in “Simulations” cell, and then averages out the results. 

“Average Deviation” is the average distance of the crosshair from the burst’s starting crosshair position. The lower it is, the better is the weapon’s horizontal recoil.

“Graph Scale H” refers to the maximum Horizontal Recoil value, visible on the graph. The default value of 1 is fine for most cases, but for weapons with lower Horizontal Recoil, you may want to reduce Graph Scale H to 0.5 to increase chart’s accuracy / detail.

Vertical Recoil module is self explanatory, you’re mostly interested in Vertical Recoil per Second. 

For the purposes of vertical recoil, it’s better to have high RoF and low vertical recoil per shot, to ensure nice and soft, consistent pull.

FSRM value is listed mostly for your reference, it doesn’t participate in any calculations.

The Average Deviations listed in Recoil Angle block show how much a weapon is affected by Recoil Angle Variance. They basically show you the size of the yellow area:

Serpent Recoil Pattern
Serpent’s recoil pattern – not included with the Toolbox

Cone of Fire Page

toolbox cof page

This page serves to analyze weapon’s Cone of Fire properties using my Angular Size research.

Both double click and right click on a weapon on the Stats page will import its stats into soft orange boxes of the Recoil page.

Target is set up identically to the Damage page, the only exception is that you also have to choose your Aiming Point, since they have different Angular Sizes. Enter “1” for Center Mass and “0” for Head.

The purpose of this page is to calculate the ideal burst length for a weapon at a certain range based on calculated target size on your screen. Calculations ignore recoil completely.

Double click on weapon’s CoF value to import it into analysis window.

Here you can find an example analysis.

Attachments Page

attachments toolbox page

This page stores a table with known effects of known attachments on all weapons. When you add attachments to weapons on other pages, this is where their effects are taken from.

To update the table, click “Pull Attachments“. A VBA Script will pull Names and Item IDs for all weapons from weapon categories on Categories Page, and then pull attachment data for them.

You can also double click on weapon’s name to open a JSON query for that weapon’s attachment list. This may be necessary if a weapon has access to atypical attachment, since the table is formatted only for known effects of known attachments.

Categories Page

categories toolbox page

This page contains a list of weapon categories. It is used when you Pull Attachments

Temp Page

This is a temporary page for storing calculations results. You don’t need to interact with this page at all.

Changelog and To-Do

v1 – initial release.

v2 – added CoF import functionality and reworked Cone of Fire page. Special thanks to FISU. Weapon names also update when you add attachments.

v2a – added the ability to double click on weapon’s Item ID to open JSON queries. 

v2b – Pull Stats will also pull Falling CoF (for flying / jumping with Carbines). Falling CoF will also be imported into Cone of Fire page. Also fixed a bug that any value of 1 character long was not pulled from the API. 

v3 – The toolbox will now also pull ADS movespeed multiplier and other missing stats. Fixed several issues, minor improvements. 

v3a – Fixed a Reload Time export.

v3b – Added the button to pull  information about weapon’s projectiles.

v4a – Added functionality to download exact effects of weapon attachments and apply them to weapons. 

v4c – Cleaned up unnecessary code in order for the Toolbox to work with 64 bit version of Excel. Also fixed a minor bug with HVA on Damage page.

v4d – Downloaded weapon and attachment stats for latest patch, including NSX Tanto. 

v4c – Cleaned up unnecessary code in order for the Toolbox to work with 64 bit version of Excel. Again.

To Do

Determine if BASR time between shots = chamber time.

Overall weapon rating based on multigon square calculations.

CoF / RoF probability distribution to determine weapon consistency (done in alpha)

Reword the horizontal recoil probability distribution graph to be more accurate

Horizontal Recoil Stability Calculator

The recent fiasco with AF-4 Cyclone guide has reminded me that a mere possibility of a weapon’s recoil being less stable doesn’t necessarily mean it will be less stable on average.

Let me remind you the raw numbers:

Statistic Cyclone
Armistice Eridani
Horizontal Recoil 0.212 / 0.4 0.347 / 0.376 0.3 / 0.392
Horizontal Recoil Tolerance 0.9 (2-3 kicks) 0.9 (2 kicks) 0.9 (2 kicks)

 

Compared to other 1st generation SMGs, the Cyclone has bigger difference between minimum and maximum recoils. It can also potentially have an extra recoil kick, increasing the total width of the recoil pattern.

That led me to conclude that Cyclone has less stable horizontal recoil. 

It is true that potentially Cyclone can kick further from the start than other 1st gen SMGs.

However, statistically, it is very unlikely. The probability of several hits in the same direction and with the recoil magnitude being in specific bounds is simply too low to be worth considering.

Discouraged by my error, I have created a tool that will allow to judge the stability of horizontal recoil pattern in an objective manner.

Horizontal Recoil Stability Calculator

This excel spreadsheet will calculate stability of a weapon’s recoil pattern as average distance from the starting crosshair position.

 Horizontal Recoil Stability Calculator

Important! To be able to open this file, you will need a Microsoft Office with enabled Excel Macros. Supposedly there are security risks for doing this. Responsibility is yours, though I promise there’s nothing malicious in specifically my excel files.

How to use

Enter the weapon’s Horizontal Recoil stats into the three orange boxes at the top (where to get stats?). It’s fine to leave Burst Length and number of Simulations to their default values. 

Click “Calculate Stability” button, and the sheet will automatically update all results and the recoil distribution graph.

The Probability Distribution graph is your main instrument for assessing weapon’s horizontal recoil stability. 

The horizontal axis of the graph represents the distance from the center of the recoil pattern, and the vertical axis represents the probability of that position being chosen. 

“Graph Scale H” defines the horizontal scale of the graph. It is set to “1 degree” by default, and generally it should be high enough for all PS2 infantry weapons. For weapons with low horizontal recoil and low tolerance values, you can reduce Graph Scale H to 0.5 to make the graph more accurate.

Ideally, you want a weapon whose graph looks like this:

ideal

Basically, a weapon without horizontal recoil whatsoever.

The closer the spikes of the graph to the left side, the higher the probability of the crosshair staying near the center of the recoil pattern – near crosshair’s original position at the beginning of a burst.

“Stability” is simply the average distance of the crosshair from the burst’s starting point. The closer it is to zero, the more accurate the weapon is on average. 

“Maximum Deviation” is the highest value taken by Horizontal Recoil during simulation.


You can copy paste the graph as image in order to compare different weapons:

copy as image

This is a graph for Gauss SAW.


On the next picture, I’m manually holding it with my mouse over the graph for Cyclone, allowing us to easily compare them.

overlay


Returning to Cyclone

Statistic Cyclone
Armistice Eridani
Stability 0.264 0.305 0.294
Maximum Deviation 0.848 0.810 0.840

 

Cyclone vs Armistice vs Eridani

As you can see, both Armistice and Eridani have lower stability, and a higher chance for the crosshair to be kicked further from the start. While Cyclone will generally shake in wider bounds, half of these bounds is still closer to the center due to lower minimum recoil. Cyclone may be less predictable on small scale, but it will be more stable on average.

Why Excel Sheet?

I’d love to eventually add this functionality into Weapon Simulator, along with other few minor updates, but for the time being I’ve lost the ability to make any additions to it. My Visual Studio died, basically 🙁

weapon calculator

PlanetSide 2 Weapon Simulator

This tool is outdated, use Weapon Toolbox for up to date weapon stats. 

This program is a standalone simulator of PlanetSide 2 Weapon Mechanics.

It can simulate Cone of Fire, bullet hits, recoil, analyze the effects of attachments on each weapon, and compare different weapons to each other.

This is a work in progress, so please inform me of any bugs or suggestions.

>> Download v1.7 PTS here <<

Known issue: since it’s program in an archive, this might get flagged as potentially harmful. Windows Smart Screen and Avast have particular issues with it.

I assure you, no harm will come to your PC. Ignore the warnings. Use any online virus scan if you don’t believe me.

v1.6 main window

New in the latest version:

pts

Version 1.7 PTS includes changes from the 14/10/2016 PTS patch to damage and range. Recoil and CoF changes are not included.

 

Instructions 

Select Program Mode in the top right corner. Select a weapon from the list on the right. You can preview selected weapon’s stats in “Weapon Stats” module, and add attachments in “Attachments” module.

“Add to comparison” button copies currently selected weapons with all attachments. You can then select a different weapon or attachment combination.

“Weapon Stats” textbox only displays stats for currently selected weapon. To access stats of weapons copied for comparison, click “Get stats” button under weapon’s name and they will appear in a message box.

Attachment restrictions

I hate when programs try to be smarter than the user, so I let you decide which attachments can be put on which weapons. If you don’t know which weapons can accept which attachments, you can check that in-game (preferable) or on PS2 Wiki if you’re lazy.

Each weapon should be properly affected by attachments, but there are a few notable points:

For non-SMG weapons, Suppressor’s penalty to Minimum Damage Range varies from -20m to -25m depending on weapon. For simplicity’s sake, -20m penalty is assumed for all non-SMG weapons.

Extended Mags won’t do anything for weapons it’s not available for.

Some weapons come with attachments already installed. For example,  MKV Suppressed comes with a pre-installed suppressor, so there’s no need to put a second suppressor on it.

Directive Shotguns in-game have Smart Choke equipped by default, but in the simulator, you will have to equip it yourself.

Slug Ammunition speed is assumed as 300 m/s for all shotguns. It’s possible that Sweeper and Haymaker have 275 m/s slug velocity.

Simulation Mode

Simulation Mode allows you to test fire all selected weapons. Press Left Mouse Button or “Fire Fixed Burst” button to fire.

The program will simulate recoil and bullet hits in accordance with PlanetSide 2 Weapon Mechanics. Bullet drop and travel time are not accounted for. The recoil magnitude should be more or less up to scale with first person shooting in game.

Right Mouse Button toggles ADS. ADSing applies magnification from chosen optic, and lets the weapon use ADS CoFs.

Holding Middle Mouse Button will let you move the window around, and Mouse Wheel to zoom in and out. You can drag around recoil patterns using the blue circle in the bottom part as an anchor.

Bullets to Kill Module

Blackhand BTK

The simulator will automatically calculate the number of bullets required to kill a target
at different ranges. Number in the brackets is time to kill (TTK).

You can specify target’s health and damage resistance in text boxes above. The “Legend” button will display a table to remind you which targets have what amount of health and damage resistance.

You can also specify the number of “Bonus Headshots” the target receives before BTK calculations. This is useful if you want to calculate the amount of bullets to kill a target after you open with a headshot.

Draw Target toggle

v1.5 drag target

Makes an enemy dummy target visible. You can drag and drop the target using the blue anchor at the bottom.

Target should be up to scale as if it’s 5m away from you in game. You can press W and S to move closer / away from the target.

Target scaling is zeroed at 5m, the more you away from it, the less accurate the scaling will be. But I did use my Angular Size research to get the target scaling as close to in-game as possible.

The “TTK Simulation” window will tell you the damage your weapon would do at that range.

TTK Simulation Module

v1.7 TTK simulation

This module is only visible when “Draw Target” is enabled.

My program cannot detect bullet hits, but you can do it manually by pressing “Head”, “Leg” and “Body” buttons.

The program will calculate damage you have dealt, and the remaining target health,
as well as how long it took to fire that amount of shots.

“Reset CoF” button will reset weapon’s CoF, and adjust “time elapsed” for the time
it would take to reset CoF in game.


“Record Pattern” mode is there for demonstration purposes.

Normally, the direction of Horizontal Recoil is chosen randomly. Record Pattern mode allows the user to choose the recoil direction with A and D keys.

This mode is only useful if you want to draw a specific recoil pattern to take a screenshot.

Configuration Module

On the first launch, the program will create “settings.ini”. You can use “save” and “load” buttons to store your preferred configuration. To reset to factory defaults, just delete the “settings.ini”.

 

Ongoing development

The program is still very much in development, so there inevitably will be bugs. Please inform me if you have any trouble with the Simulator.

how i code

To-Do

Already on my to-do list:

  • Let user manually add weapons
  • Add Recoil Simulation mode that will let the user control recoil with mouse
  • Let the “Save configuration” button save selected weapons as well.
  • add recoil pattern width and recoil per second calculations into weapon stats window, and calculated stat values (DPM, FSRM).
  • add TTK graph mode
  • add sorting by weapon class and faction to the weapon list
  • add a way to automatically update weapon stats through census API or accept export from Toolbox
  • add BTK thresholds to the left side of the bullet damage graph
  • add BTK thresholds graph

Changelog and archive

v1.0 – Initial Release

v1.1 – Bugfix

Fixed a bug with Decimal Symbol in different locales.

v1.2 – Function update

Users can now choose a firing stance.
Added weapon comparison.
Recoil patterns can be dragged around using the small blue circle at the bottom.
Also updated the weapon database to include velocity changes.

v1.3 – Bugfix

Fixed a bug that prevented weapon stats from updating properly with HVA, SPA and Suppressor attachments.

v1.4 – Function update

Added an option to draw weapon damage graphs.
Useful for comparing weapon damage at different ranges and with different attachments.

v1.5- Function update

Now the target from “Draw Target” option can be dragged and dropped using the blue anchor at the bottom of the target.

v1.5a – Function fix

Now the drawn target scales relative to center mass.

v1.6 – Function update

Added “BTK Thresholds” window. It will calculate the amount of bullets needed for a
weapon to kill a target with specified health and resistance at different ranges.

v1.6A – Function update

“BTK Thresholds” window will also display TTK.

v1.7 – Function update

Updated for the latest patch, and added “TTK Simulation” module.

v1.7 PTS – Database update

Updated for the latest PTS patch to highlight some weapon changes.


As a closing word, huge thanks to /u/L33-the-3rd for formatting the PS2 Weapon Database into CSV for me.

burst length

Ideal Burst Length Calculator

This Excel sheet will calculate ideal burst length at different ranges from 5m to 70m for any PlanetSide 2 weapon. 

Download links

>> Main download link <<

Google mirror

Excel 97-2003 Version

Excel 97-2003 Google mirror

How to use

1)Pick a weapon and find its stats in game or at PlanetStats. You will need:

  • Maximum Damage
  • Maximum Damage Range
  • Minimum Damage
  • Minimum Damage Range
  • Standing Still Cone of Fire
  • Standing Moving Cone of Fire
  • Cone of Fire Bloom (also known as Cone of Fire Recoil)
  • Rate of Fire (optional)

2)Download and open the Burst Length Calculator.

3)Input weapon’s stats into orange boxes in Stats block:

calculator stats

CoF Recovery Rate is “20” for all known weapons, you don’t have to touch it. 

Rate of Fire is optional, you will only need it if you want Time-to-Kill calculations.

Target Health – 1000 is default, 900 for Infiltrators, up to 1450 for NMG / Adrenaline Shield Heavy Assault.

3)Additional configuration

calculator additional settings

By default, calculations are done for Center Mass. Enter “0” into orange box in Choose Aiming Point block to calculate for headshots instead.

Headshot Damage Multiplier field is there “just in case”, since only shotguns use semi-auto or auto fire modes with 1.5x headshot multiplier. There’s also T7 MCG, but it has static ADS CoF. 

Heavy Assault with active Resist Shield takes 1.2x headshot damage.

Body Damage Multiplier allows you to add Nanoweave into calculations. Naturally, this field is ignored if you calculate for headshots.

Heavy Assault with active Resist Shield takes 0.6x body shot damage (does not stack with Nanoweave).

5)Analyze results.

calculator results

Burst Length will tell you the maximum amount of rounds in one burst that are guaranteed* to land on target at a certain Distance

* – In order to keep calculations pure and simple, this calculator ignores recoil completely and assumes the player keeps his aim exactly at target’s center.

No Human Element whatsoever, only Cone of Fire mechanics participate in calculations.

If a Burst Length value of 1 turns yellow, it means that even 1 hit is not guaranteed, because target is smaller than starting Cone of Fire. This will be especially noticeable with headshots at longer ranges.

Bursts to Kill will tell you the amount of bursts necessary to kill an enemy with Target Health with a certain Burst Length.

Ideal TTK column will show you time-to-kill with full auto fire.

Burst Delay will tell you the amount of time necessary for CoF to reset between bursts.

Adjusted TTK will tell you the amount of time it will take to fire off Bursts to Kill with Burst Delay between them. 

Considerations

While the resulting Burst Length is a very specific number, you should treat it for what it is – an idealized model. If a robot was playing PlanetSide 2, he would shoot this way. For a human it is impossible to disregard recoil, nor to keep aim exactly at the center of a target. 

Calculator itself is not perfect, as Target Sizes are hard to measure precisely. My way of measuring gives an error of ~1 pixel, which can be a huge deal at longer ranges,  where that pixel constitutes ~25% of the target. 

If I had access to 4k video or screenshots, that would quadruple Calculator’s precision.

How to benefit

The Calculator will tell you what is the best theoretical performance your weapon can give you.

It’s not about your personal performance, it’s about what your gear is capable of. 

Write down or print your calculated results and go to VR training. Equip Sweeper HUD implant, and learn to judge distance to the enemy in ballpark of tenths of meters. You can use crosshair notches or Red Dot as reference.

Or use these screenshots.

Now, with weapon’s capabilities in mind, you can establish rough engagement guidelines for yourself.

Example of Analysis

Let’s use AC-X11 as an example: 

EDIT: I forgot to account for mandatory HVA in this example, but since it’s slated to receive some changes, I’m gonna hold off on updating this example.

ACX11 bodyshots
AC-X11 bodyshots

First, let’s find a cutoff point where an enemy can be realistically killed in one burst while standing moving.

At 30m, AC-X11 takes 7 hits to kill a full nano target, while its CoF can support only a 9 shot burst. So margin of missing is only 2 shots, which is a bit too close for comfort. AC-X11 has rather harsh recoil and unimpressive bullet velocity, so misses are very probable. 

This is also the point where you consider your personal skill as a shooter. Accuracy grades from your character stats at FISU should give you an idea of where you stand among other players. If your accuracy is below average, consider giving yourself a higher margin for error.

At 20m AC-X11 can sustain a 15 shot burst, which is way more than enough.

So I would call 25m the cutoff range for realistic 1-burst kill in the heat of combat. 

If there is an opportunity to stand still and aim carefully, it’s probably possible to 1-burst enemies 35m away. 

Naturally, these values will go up or down depending on enemy. An AFK infiltrator can be 1-bursted from greater ranges than shielded HA, actively dodging incoming fire.

Between 25m and 60m we want to fire in bursts of 10 to 5 rounds depending on distance, expecting a 2-3-burst kill most of the time. 

We can also see that it’s nearly pointless to fire at enemies >60m away while moving.

Now let’s take a look at headshots.

ACX11 headshots
AC-X11 headshots

It’s immediately apparent that AC-X11 struggles at getting headshots at ranges greater than 25m while moving.

So we can set 25m as the cutoff range for 1-bursting an enemy with headshots. And if we want to go for headshots at greater ranges, we need to remain stationary for the whole burst. 

Let’s recap on our guidelines: 

While moving, enemies can be 1-bursted with either headshots or bodyshots within 25 meters. 

While moving, enemies can be 2 or 3 bursted with burst length of 10 to 5 rounds, depending on range.

While standing, enemies can be 1-bursted with either headshots or bodyshots between 25m and 60m.

Given a choice, enemies further than 60m should not be engaged while moving.

After you formulate these guidelines for yourself, all that remains is to learn to put them into practice. Training and mental discipline!

chinese master

Have fun theorycrafting your way to victory. Please inform me of any issues with the Calculator.

Note: for calculating bullet damage at different ranges and finding out Bullet Damage Thresholds, use Bullet Damage Calculator.

planetside 2 bullet damage calculator

Bullet Damage Calculator

Download here

This Excel sheet will calculate bullet damage and other basic properties, such as DPS and TTK, as well as Bullet Damage Thresholds. 

>> Main download link <<

Google mirror 

This sheet calculates BTK, ideal DPS and TTK three times:
1) For bullet damage achieved at maximum damage range
2) For bullet damage achieved at minimum damage range
3) For damage achieved at range specified by you
 
Calculations are done independently for two weapons, and then compared to each other.
 
You can specify target’s health and damage modifier, which is useful if you want to calculate for headshots or nanoweave armor.
 
Additionally, you can press “Calculate Thresholds” button, and the sheet will automatically calculate bullet damage thresholds for you – find ranges, where the weapon starts to require an additional bullet to kill the target.
 
You can also press a “Draw Chart” button, and a comparative chart will be automatically created, showing the differences in weapons’ BTKs at different ranges.

Applications

The damage model of a weapon can be inaccurate in certain situations. For example,
ACX-11: 200 @ 10m – 143 @ 80m.
BTKs on both ends are obvious: 5 @ 10m and 7 @ 80m.
 
But what about what happens in the middle? With this sheet, you can find out that BTK will change from 6 to 7 bullets at 52m already. So, in a sense, minimum damage range is actually 52m, not 80m.
 
Obviously, there are a lot of conditions: full health, only chest and arm hits, no nanoweave (similar threshold exists for nanoweave too, but at different range).

How to use

Important!
First, open your Microsoft Excel. Then go to File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings and check the box “enable all macros”. If you’re using a different version of Excel, just google how to “allow the Excel to run programs included with spreadsheets” in your version. You have to do this, otherwise you won’t be able to open the sheet.
 
Enter stats of one or two weapons in green boxes. You can take them from the game, your imagination or this spreadsheet.
 
If you want to calculate weapon’s bullet damage at a specific range, enter this range into the blue box.
 
Example:
how to use calculate bullet damage
 
You can specify target’s base health. Common values are 900 for infiltrators, 1000 for other infantry and 2000 for MAX units.
 
You can also specify a damage multiplier. This is useful if you want to calculate for headshots or nanoweave armor / kinetic armor.
 
Headshot multipliers can be taken from the spreadsheet I linked above, but, as the rule of a thumb, headshot multiplier is 1.5x for shotguns, crossbow and T7 MCG, 2.1x for bolt action sniper rifles and 2x for other weapons.
 
Nanoweave Armor 5 blocks 20% of small arms damage, so enter 0.8 if you want to calculate for it.
 
Remember that headshots ignore nanoweave.
 
MAX units have 80% resistance to small arms fire, 87.5% for Kinetic Armor 5.
 
How to read “Calculate Thresholds” button output:
how to use calculate bullet damage thresholds
 
BTK stands for bullets-to-kill. This picture shows a weapon that has BTK of 7 at ranges from 0 to 15m, 8 for range of 16m to 33m, 9 for range of 34m to 48m and 10 for any range past 49m.
 
Video with examples

Known issues

1) If the value in the blue box is lower than Max Damage Range or higher than Min Damage Range, computations for Damage @ Range, BTK @ Range and TTK @ Range become screwed.
 
For example, you want to compare ACX-11 to T5 AMC at 70 meters.
ACX-11: 200 @ 10m – 143 @ 80m
T5 AMC: 143 @ 10m – 112 @ 60m
 
If you type [70] into the blue boxes for both ACX-11 and T5 AMC, computations for T5 AMC will be wrong.
Solution: don’t. You can expect T5 AMC to behave at 70 meters same as it does at 60 meters (can’t go below minimum damage). So enter 70 into the ACX-11’s blue box, and enter 60 into T5 AMC’s blue box.
 
2) Sometimes “Draw Chart” function bugs out, and draws a mumbo-jumbo of colorful lines.
I haven’t quite figured out the reason for this.
 
Solution: Go to the “Temp” worksheet, press Ctrl+A, then Delete. This will wipe out its contents.
Then go to the main worksheet, save and exit the excel. After reopening the sheet file, everything should function normally. If it doesn’t, repeat the process.
 
 
I realize that this sheet is not as cool as Axiom80’s Weapon Damage Analyzer, that has pretty graphs and all, but that thing is outdated.
 
You input values into my sheet yourself, so it’ll never be outdated, as long as core weapon mechanics of PS2 stay the same.