Ships Skills and Outfitting spreadsheet

[Remus]

Note: Remus is no longer maintaining Ships Skills and Outfitting and the final version is for build 2.10.
Others are welcome to continue keeping Ships Skills and Outfitting up to date or writing a new ship combat tool with current stats, and you are advised to check the end of this thread to see if anyone else has done so.

Repeatedly delayed, incomplete, doubtless riddled with bugs and inaccuracies and looks damned ugly in OpenOffice†,

Ships Skills and Outfitting
Now with Graphs that work in OpenOffice!
(well, sort of)

was once probably the most comprehensive ship combat resource available.

Others have written ship combat spreadsheets before and I am particularly indebted to Valeroth, some of whose design I have copied. However, as far as I know this is the first ship combat tool which includes Accuracy and Damage Reduction in the calculations.
In addition to ships, it also allows you to select skills, outfittings and ammo to compare.
I don't pretend it is remotely a substitute for trying things out in-game, but you might find you can select your next ship and outfittings sitting quietly at work without anyone noticing. Your boss might not appreciate you fighting a duel during work hours (I know mine wouldn't ) but studying a spreadsheet is fine.

What it includes and how to use it

General
Orange-gold cells are drop down lists for you to select a value (you can also use delete)
Bright yellow cells are for you to enter values
There are a very few green or red Yes/No cells which are also drop down lists
Please do not attempt to change any other cell value.

Sheet: Ship Stats by Level
This lists all the freely available combat ships sorted by level (and within each level by raw broadside damage) in an easy to view table that can be printed on two sheet of A3. Most of the values come directly from in-game statistics, but a few might need a little more comment:
OS Max Speed is from the Speed Table published by FLS with their Guide to Understanding Sailing, modified as per subsequent Release Notes and corrected as errors are found by direct testing.
Time To Sink is calculated as the time an unoutfitted Capricieux MC would take to sink each ship at 200 yds, both ships sailing at 10 knots. Defense, DR and Resistance of the target ship are all included (but gun loss on the Cap MC is not).
Damage and DPS (Damage per Second) values are listed for various distances. These include base cannon accuracy and Offense bonuses, but not Target Tracking (the ship is assumed to be stationary) or Defense (this depends on the ship being fired upon).
If you wish, you can use Microsoft Excel's built-in sort utility to rearrange the ships in any order you wish, but please select entire rows when doing so. You can also rearrange columns.

Sheet: Comparison
This is where you select two combinations of ships, outfittings and skills to compare.
There are three tables.

In the left table, enter the ships, outfittings and skills you wish to compare. These are all from drop down menus. Once you select a ship, only valid outfittings are available. Once you select a Career, only that career's and national/pirate ally skills are available.
You will be warned if you try to select incompatible general outfittings but not if you select incompatible skills, although you are only allowed to select one Crew Focus and one Attack skill. There is a little hiccup in that you cannot (yet) select two mutually compatible Attack skills, such as Warning Shot and Vital Strike. I might work out how best to do this in a future release.
A number of mission reward General outfittings are not (yet) in the drop down lists, but you can enter their stats at the bottom of the table so they still get stacking penalties applied.
You can select Battle Sails and Current Arrow with Yes/No boxes. Spotters is completed automatically and is included just for information.
If you want to copy ships, outfittings or skills between the two ships rather than select from the drop down lists for each ship individually, you must make sure to use Paste Special > Values rather than simply Paste.

In the right (centre) table, you can see the two ships' stats side by side in much the same format as the in-game ship stats. Here you can further refine most of the modifiable values - perhaps you really do want to use Warning Shot together with Vital Strike, or you want to add clickies. Just enter the mods in the yellow cells. Where there are caps, these are also applied in this table and you get warning that a value has reached its cap.

The far right table lists the capped limits and values. Very few of these have been published by FLS and a few more have been discovered by players. Consequently there may be inaccuracies and omissions. Please report any of which you are aware.

Sheet: Damage Graphs
In this sheet, select the ammo you wish to use on each deck, which side of each ship is facing the other and the speed and tracking of the two ships and three graphs will be displayed; Damage Against Distance, DPS Against Distance and, for a chosen distance, Integrity Against Time. These are particularly useful for comparing different outfittngs, skills or ammo using the same base ship.
As with all calculations in Ships Skills and Outfitting, accuracy and DR are included in all calculations. The Damage Against Distance and Integrity Against Time graphs also include the Armor / Structure damage split and the Integrity Against Time graph additionally includes Gun Loss and optionally includes Crew Loss reload penalties.

OpenOffice users may need a little help with the graphs, not least because OpenOffice does not display the series labels.
Here is what the lines show:

• Red is ship one and blue is ship two, as per the Comparison sheet.
• Hull is shown as a solid line
  Where armour and structure are shown separately, armour is a thick line and structure a thin line
  Masts/sails is a dashed line
  Crew is shown as a dotted line
• Guns in the Integrity against Time graph are plotted against the right hand axis.
  Ship 1 guns are a thin solid orange line
  Ship 2 guns are a thin solid turquoise line
• If you can work out how to do it, the graphs sheets should look far better if you can turn off the gridlines.

Sheet: Speed Graphs
There is a Polars chart for Battle Speed (I have deleted the one for Open Sea as it was hopelessly inaccurate).
There are also Speed against Time and Distance against Time graphs. These are not quite straightforward owing to hidden stats.
FLS released to Valeroth an acceleration formula which I have copied. I have tested it quite a bit and it seems to work fine at Best Point for every ship I have tried, although I understand it isn't quite what the game uses particularly as a ship approches maximum speed.
I then found a general formula that seems to apply to ships at all points of sail. Initial testing suggested there were still hidden stats which needed to be taken into account, but in more recent tests every ship appeared to obey the formula, including those which seemed not to during earlier tests.

The graphs have similar display problems in OpenOffice as the damage graphs.

Sheet: Ships Raw Data
Just what it says. Info from in-game and the speed table spreadsheet. I have checked the damage and reload values for consistancy and have left the damage and reload bonus values in the table as I expect a few users might be interested in these.
You can sort the ships into any order (please sort entire columns) without affecting anything else in the spreadsheet except the order of the ships drop down lists. You can also rearrange rows but please do not reorder Guns Size Reload Damage Range Acc 200 Acc 400 in each gunbank as this will upset other calculations.
Eagle-eyed readers might spot a couple of unexpected ships. Every ship in the table has full stats visible somewhere in-game.

Sheet: Skills
These are broken down into careers and then into certain categories. Please do not rearrange except within a category. If you want to find, for example, the best damage skill, I suggest you select the Damage % column and scroll down to find the best value.

Sheet: Permanents
Divided into ship size and outfitting category. Please do not rearrange across sizes/categories.

Sheet: Generals
One long list in alphabetical order. Rearrange however you wish, but the drop down list order will also be rearranged.

Sheet: Ammo
Ammo stats. Please do not rearrange

Sheet: Consumables etc
So far, only Gunpowder and Experts.

Sheet: Cannon Table
Taelorn's Cannon Table complete with (a best guess of) CRP values and calculated accuracy values at different distances.

The remaining sheets are for internal calculations and lists.

In the future I might add a Repairs sheet to compare different repairs. These seem almost as misunderstood as ammo.

I have also uploaded the file to Slamz' site here.
Unfortunately I have not been able to upload the most recent version.

The next post is my current wish list. I will update it as information is received.
__________________
† OpenOffice users:
If you find N/A in some of the cells, go to Tools > Options > OpenOffice.org Calc > Calculate and check the box
Search criteria = and <> must apply to whole cells

[Remus]

If anyone can help with any of the following, please post in this thread or PM me.

General Outfittings
I am pretty sure I have every tradeable or CofC/MoV obtainable item in the spreadsheet, but I am missing many of the mission reward outfittings.
If you find one that is not in the list, please let me know the stats including level and category, and if you know the mission that rewarded it I would like to know that too.
If you know the mission that rewards any of the outfittings I do have, please let me know these as well.

Cannon Resistance Penetration values
1.23 Release Notes said most of these have changes betweem 1% and 3%, without giving the actual values. The table published in A Treatise on Combat Mechanics has not been updated. I have guessed at the new values but could a Dev publish what they actually are?

Polars
I have found errors in the published Polars relating to the Wenden (from detailed testing of only a couple of ships). Also about 25% of the 'old' values listed in the 1.23 Release Notes do not match the previously published values, leading me to think there are numerous errors in the spreadsheet published with the Sailing Guilde. Can these be thoroughly checked, please?
OS Speeds and Polars for the little ships would be nice too.

Polar Speed Interpolation
Recent testing has shown that this is not linear between named points of sail downwind of Close Hauled. I have worked out approximate formulae but I am fairly sure I haven't quite got it right.
Would a Dev care to share the actual equation?

Gun Loss
The Integrity against Time graph includes gun loss. Taelorn told us the base value but we know that ships below level 10 (not sure if this includes the Dragon; it didn't exist at the time) and the Cyclone lose guns at a lower rate. Maybe other ships do too. Any chance of letting us know what all the exceptions are and what the gun loss is on these ships?
Update: Recent testing suggests Taelorn's value is out of date. Even more recent testing suggests gun loss calcs are far more complicated than I thought and I now find myslef completely lost.
Does anyone know how this works?

Crew Regen
Also for use in the Damage/Time graphs. Lum posted the base value but said there are numerous modifiers. Testing has shown that none of these have any effect but that each ship has its own modifier.
The value for the Intrepid has been puiblished in the 2.5.60.0 release notes. Could values for the other ships be released, please?

Caps
Any more knowledge on capped values gratefully received

Bugs and Inaccuracies
I would be amazed if Ships Skills and Outfittings is entirely correct and bug-free. Please will users report anything that seems amiss.

I will update this post as questions are answered and I remember/discover more questions to ask.

[Lum]

Great sheet! It's remarkably similar to many of my own monster spreadsheets. As someone who makes, uses, and maintains sheets like these all the time, the biggest piece of advice I can give you is NAME YOUR CELLS. Pretty much every cell in my tuning sheets has a name if it's referenced by another formula somewhere.

It makes for easier references, easier formula debugging, and should anyone else ever take over your spreadsheet, it makes for much easier interpretation should something not make any sense.

If I have any free time in the next few weeks, I'll see if I can dig up some of the stuff you're looking for, but no promises.

[brendonoid]

/hugs Remus

/hugs Lum


*Beams*

~mog!

[Fishbreath]

At the very least, the comparison sheet works alright in OpenOffice.

Although I ought to note that, at the moment, if you compare, say, 5 and 8 as reload speeds, the difference is in blue instead of red (which seems to be the more sensible display choice).

[Remus]

Originally Posted by Lum Great sheet! It's remarkably similar to many of my own monster spreadsheets. As someone who makes, uses, and maintains sheets like these all the time, the biggest piece of advice I can give you is NAME YOUR CELLS. Pretty much every cell in my tuning sheets has a name if it's referenced by another formula somewhere. It makes for easier references, easier formula debugging, and should anyone else ever take over your spreadsheet, it makes for much easier interpretation should something not make any sense. If I have any free time in the next few weeks, I'll see if I can dig up some of the stuff you're looking for, but no promises.

Thank you, Lum.

I used to have a deep hatred of names. I was under the impression they made on the fly formula writing a lot harder (is this really true?), but I am sure you are right for improving readability.

I'll be very grateful for any asistance you can provide with my wanted list.

Originally Posted by Fishbreath At the very least, the comparison sheet works alright in OpenOffice. Although I ought to note that, at the moment, if you compare, say, 5 and 8 as reload speeds, the difference is in blue instead of red (which seems to be the more sensible display choice).

Yup, you are right. This is an error. I will correct it in the next release.

[Remus]

Two errors corrected:
1. The reload comparison error pointed out by Fishbreath
2. Ship 1 Sail Outfitting drop down lists were for the size of Ship 2

Download from the same url:
http://www.4shared.com/file/19973348...itting_12.html

[Kerrilyn]

Originally Posted by brendonoid /hugs Remus /hugs Lum *Beams* ~mog!

It's "mog~".

Mog!!~~

*Giggle*

[brendonoid]

Originally Posted by Kerrilyn It's "mog~". Mog!!~~ *Giggle*

Mogmogmog!!!!~

[Fishbreath]

Although you've said OpenOffice compatibility isn't guaranteed, the Damage and DPS vs. Distance, plus the position vs. time graphs, seem to be nonfunctional in OpenOffice.