Title: HWRM Ship vs. Ship Odds Spreadsheets Version: 1.3.0 Author: Mikali Created: 2016/08/07 Updated: 2016/08/18 Homepage: http://isometricland.net/homeworld/homeworld.php Discussion: https://forums.gearboxsoftware.com/t/guide-hwrm-ship-vs-ship-stats-v0-2-0/1543317 (new) https://forums.gearboxsoftware.com/t/play-balancing-mod-for-hwrm/1542877 (new) http://forums.relicnews.com/showthread.php?16022-Ship-Stats-Spreadsheet (old) http://forums.relicnews.com/showthread.php?t=57399 (old) ================================================================================ DESCRIPTION I created a mod called Play Balancing that iterates through a list of ships, comparing the performance of one ship versus the performance of al the others. Using this method, it is possible to gauge to some degree of accuracy the relative combat strength of one ship versus another. These spreadsheets are a record of these comparisons. For more info on how these stats were gotten, refer to the documentation for the Play Balancing Mod. Let me just say that the goal is to compare the time it takes for Player 1's ship to defeat Player 2's ship with the time it took for Player 2's ship to defeat Player 1's ship. The less time it takes, the more powerful the ship. Some ships don't have any weapons at all and are skipped. There are five spreadsheets in this collection: • odds_datalog.xlsm: contains the raw data that the other spreadsheets read. • odds_hiigaran.xlsm: contains combat odds for Hiigaran ships. • odds_kushan.xlsm contains combat odds for Kushan ships. • odds_taiidan.xlsm contains combat odds for Taiidan ships. • odds_vaygr.xlsm contains combat odds for Vaygr ships. The latter four spreadsheets feature a big button on one of the worksheets that can be used to open the raw data file, re-read the data and recalculate the fields. If the recalculation does not work, try pressing CTRL + ALT + F9. It may take a few seconds to complete. The worksheets named "logs…" contain actual measurements. The worksheets named "odds…" contain the calculated odds. The "odds…" sheets have Top Ships, which are the ships in the topmost row that belonged to Player 2; and Left Ships, which are the ships in the leftmost column that belonged to Player 1. Odds are calculated as follows: Sqr(TopTime / LeftTime) TopTime is the time it took for the Top Ship to kill the Left Ship, and LeftTime is the time it took for the Left Ship to kill the Top Ship. I don't know why I am taking the square root. It just "feels" closer to reality to me. If the listed odds are greater than 1, then the Left Ship is more powerful than the Top Ship. If the listed odds are less than 1, then the Left Ship is weaker than The Top Ship. If the listed odds are equal to 1, then the Left Ship and the Top Ship are equally strong. A value of "NoAttack" means that one or both ships have no offensive weapon and therefore cannot attack. A value of "TimedOut" means that one or both ships ran out of time before killing all of the opponent's ships. Currently the timeout value is 1 hour. A value of "SameShip" means that the Left and Top Ships are identical, and therefore equal in strength. A value of "Skipped" means that it was clear the round would have timed out eventually, and the round was pre- emptively stopped before that could happen. A value of "Failed" means the calculation has gone wrong somewhere and needs to be fixed. Here are what the individual fields in the generated output mean: • P1_ShipNumber: the index number of player 1's ship in ShipList. • P1_ShipsLeft: the number of player 1's ships left over at the end of a round. • P1_AvgHealth: the average health of player 1's ships (including dead ones) at the end of a round. • P1_ShipType: the type of player 1's ship. • P1_ResearchList: any research items applied to player 1's ships. • P2_ShipNumber: the index number of player 2's ship in ShipList. • P2_ShipsLeft: the number of player 2's ships left over at the end of a round. • P2_AvgHealth: the average health of player 2's ships (including dead ones) at the end of a round. • P2_ShipType: the type of player 2's ship. • P2_ResearchList: any research items applied to player 2's ships. • RoundTime: the duration of the battle between player 1 and player 2's ships, or a flag indicating why the duration of the battle was not recorded. The script used to generate the stats is also capable of extrapolating how long it *would* take (probably) for Player 1 to kill Player 2 if time were allowed to continue past "TimeOutValue". This is done whenever possible. ================================================================================ CHANGE LOG 1.3.0 --- 2016/08/18 • All sheets now have conditional formatting applied to them. • Added a checkbox to switch conditional formatting on and off. 1.2.0 --- 2016/08/16 • The "ExtraTime" column has been merged into "RoundTime" colummn. • Four new "tim" sheets per workbook. These display the time in seconds it took each ship to kill the other ships. • The spreadsheets now also show how long it took attack capable ships to kill non-attack capable ships. Before, these stats were omitted. • New conditional formatting in some of the workbooks to show which values are good and which are bad using color gradients. 1.1.0 --- 2016/08/14 • Gathered new stats based on the HWRM patch preview and put the two sets of data in different subfolders. • The "clc" table values are now based on the new "ExtraTime" statistics, which include extrapolations of data beyond the default time-out value based on current health percentages. 1.0.0 --- 2016/08/11 • The spreadsheets now show if *both* the top ship and the left ship timed out. • Done with all four races. • Health percentages now are calculated with respect to all 10 ships, not just the remaining alive ships. 0.2.0 --- 2016/08/10 • Replaced UDF for each cell with a macro that is only run once when a button is pressed. • The current stats now reflect that Player 2's ships are mobile instead of inert. In the past, only Player 1's ships were mbile, which made it too easy for slow ships to destroy fast ships. (Such as an ion cannon frigate popping all the scouts in just a few seconds.) • This version still only has half the statistics as the final 1.0 version. 0.1.0 --- 2016/08/07 • Initial release with only half the statistics.