Spreadsheet: Point Defense Calculator

[Exultant (OP)]

Current Version 2.1.0. [Updated 2024.08.06]

HUGE UPDATE TO PD CALCULATOR: Now has a text guide! See below

Updated for PD changes. Able to compare 10cm Rail (any # shots), 10cm Laser (including size reductions) and any gauss. Able to accommodate fighter designs.

Able to help design efficient CIWS CIWS is currently bugged in Aurora 2.5.1. Do not use until this has been fixed

Able to calculate for point blank fire and vs. laser standoff missiles.

Able to determine approximate number of leaks given fire control settings. (Huge shoutout to forum user bean for the binomial distribution spreadsheet). bean's missile analysis is what inspired me to dig into the grit of this calculator again, and their knowledge of excel functions was the breakthrough moment I needed to update my sheet.

Determines number of CIWS needed to catch leaks, AND/OR HS of shields needed to catch leaks.

Determines HS of shields needed to fully recharge-tank leaks if you don't like CIWS.

Determines true cost of PD in HS, accounting for MSP required due to weapon misfires, including CIWS to catch leaks if desired.

Now accounts for missile decoys as of v. 2.1.0!

Calculates 2 sets of values: The "Best" value uses the BFC settings with the highest %chance to hit. The "Efficient" value also includes the total HS of PD assigned to the BFC to design a smaller BFC that allows you to save space. Total missile defense should be near-parity in most cases, so the 'efficient' calculations will likely save 1-2 total HS on your design.

https://docs.google.com/spreadsheets/d/10ANvL_nNYAweesxR4PZeaR0GjgHNKxphRhbH4BcFFtk/edit?usp=sharing

Make a copy to edit settings. The Optimal Beams sheet is a WIP for my personal game, so functionality is incomplete and it looks ugly.

Text Guide to use the calculator: https://docs.google.com/document/d/1JvHhn_W0Seg3j1hZFePjB0QIu4eVikNyHS0F831EBns/edit?usp=sharing

As a final note, having a blind child, I am highly conscious about accessibility in gaming. If you are a vision impaired individual and have suggestions on how to make my calculator or guide more accessible please reach out to me. I will happily and gladly make improvements.

Changelog:

2024.08.01 - Recalculated for HS needed to intercept 10 volleys, up from 1. Added summary information detailing HS of MSP needed per additional 10 volleys intercepted.
24.08.01 - Bug fixes on turret cost calculations, and addition of actually how many turrets are needed (not just total size). 
24.08.03 - Testing confirms shots per missile are not capped to 10, so uncapped max shots in calculator
24.08.03 - fairly significant bug affecting turret costs (was calculating ~20% too high, and I found that my formula wasn't accounting for laser range mods or gauss size below full.
24.08.06 - Fixes a MSP HS bug (was reporting 25x too large) and added missile decoy, ECM/ECCM tech and tactical officer bonus functionalities. Added comment for Missile Decoys to help you estimate decoys if you haven't captured a missile yet.

[Exultant (OP)]

Updated to v2.0.1

- Recalculated for HS needed to intercept 10 volleys, up from 1. Added summary information detailing HS of MSP needed per additional 10 volleys intercepted as well as # of CIWS needed per-ship to catch leaks. Minimizing this value is likely optimal.

[Exultant (OP)]

Updated to 2.0.2. Bug fixes on turret cost calculations, and addition of actually how many turrets are needed (not just total size).

[Jovus]

This is super awesome, thank you.

[Exultant (OP)]

Update to 2.0.4 - fairly significant bug affecting turret costs (was calculating ~20% too high, and I found that my formula wasn't accounting for laser range mods or gauss size below full.

[Exultant (OP)]

Update to 2.1.0: Now includes Missile Decoys!

Specific Changes:

• Addition of Missile Decoys. Added cells for your ECCM tech and Missile's ECM tech. Keep at parity if unknown (worst case scenario)
• Addition of Tactical Officer bonus. Primarily useful if you use CIC on your PD craft, as a captain with Tactical is very RNG. Added after I noticed that my 30% Tactical officer had increased my chance to hit from 68-92%!
• Addition of a guide to explain all the features of the calculator and how to use! See initial post for link.
• Bugfix: MSP HS calculations were 25x too big for all systems (a combination of a 50x bug and calculating based on a 1% failure rate when testing indicates it's likely actually 2%).
  

Current Issues:

• CIWS is bugged in Aurora Version 2.5.1. CIWS fires but does not actually remove missiles, causing them to hit anyway. Please disregard all CIWS calculations and use shields to catch leaks. CIWS remains a part of the calculator so it Just Works (tm) when this issue is fixed in a future Aurora version.
• CIWS is currently calculating a failure rate, but my testing indicates that it may not actually have a misfire chance. It is unknown as to whether this is a bug or Working as Intended due to CIWS being a commercial system. In case it's a bug, CIWS MSP calculations are remaining in the calculator until confirmed otherwise.