05MAR2023 EDIT: In putting together some characters for Cannonshop, I discovered an error in Attribute optimization (which was only affecting negative Attributes, a problem for characters that get significant penalties). Link to the post with v4.9 (now fixed after the demise of "pretty links"): https://bg.battletech.com/forums/index.php/topic,52309.msg1915462.html#msg191546221JAN2023 EDIT: Last weekend, I built a Platoon vs. Platoon scenario and realized I needed movement rates to fill out the Squad Combat Record Sheet. Adding them to the main sheet necessitated fixing Climbing and Swimming at the bottom of the skill list, but it now works! I also added a "Condition Monitor", cleaned up some remaining copy/paste errors in the sample character, and marked the extra traits and SPAs in the Data Table with asterisks for ease of reference. Link to the post with v4-8: https://bg.battletech.com/forums/roleplaying/atow-character-generation-spreadsheet-redux/msg1901268/#msg190126829OCT2022 EDIT: The Corrected Third Printing of A Time of War was published this week! Changes included dropping Tiered Skills, which greatly simplified the logic in the spreadsheet. I also figured out how to account for SPAs without screwing up the Surplus column. The Third Printing also dropped Thick- and Thin-skinned traits, but I left them in for convenience. I also added my Expanded TDS traits and Infantry SPAs for those who want to use them. Those extras are all "under the hood" and shouldn't be a problem for those strictly adhering to RAW. Link to the post with v4-7: https://bg.battletech.com/forums/roleplaying/atow-character-generation-spreadsheet-redux/msg1878024/#msg187802408FEB2019 EDIT: Maelwys pointed out the XP from aging formula will properly account for the difference between Inner Sphere and Clan characters if "Clan" is first in the "Affiliation" block (T2). Adding this to the instructions. BiggRigg42 also pointed out I should explain why it's best to insert in the middle of the Trait and Skill lists, which I'm also adding.14JAN2019 EDIT: Helping BiggRigg42 with his character creation videos led me to discover errors in the sample character provided. They have been corrected in v4-6 attached to the third post. Unlike the previous update, no calculations were affected, so people who downloaded v4-5 don't need v4-6.26DEC2017 EDIT: Please see the second post in this thread for the current version. Version 4-5 adds the SPAs and one additional trait from the A Time of War Companion.This is a re-post of my spreadsheet from years ago. The first version was on the previous incarnation of the forum, and it's been revised extensively since then (now v4.4).
Special thanks to Damourc for the latest round of testing, Calan for pointing out a major flaw a few years ago, and everyone else who's expressed interest.
The current version is an Excel 97-2003 file, and has been tested under Open Office (again, thanks to Damourc).
The sheet is intended to automate to the greatest degree possible:
Aging Effects on both Attributes and Flexible XP
Attribute/Trait/Skill Optimization
Skill Target Number Calculation (though it doesn't do things like Gremlins/Tech Empathy)
Instructions:
1) Set Up. Insert a new worksheet and "copy all" from the "Sample" template.
2) Data Entry. In Column A, enter each attribute/trait/skill and its respective XP cost in Column B. When entering items in Column A, I recommend using formatting (outlines) to group items from the same modules and the SUBTOTAL function if you're using life modules (cells with SUBTOTALs will be ignored by the summation functions that total up column B; don't forget the first argument should be 9). This makes error checking easier down the road. Enter an asterisk in Column D to denote items that are "flexible" or chosen from a list. This will also aid in error checking. Be sure to enter Field Rebates as negative numbers. This affects how column B is totaled.
Caution. Never overwrite the "Aging Effects" block at the bottom of Columns A-D. Always insert cells above it (not rows). When doing this, span columns A-D to keep your asterisks lined up with the items they flag. Also span all four columns when deleting excess cells.
There are also invisible index numbers in cells D2-D9, never overwrite these.
Once you have entered all the data in Columns A and B, use a "Fill Down" to propagate the formula in Column C in the attribute section all the way to the bottom of the list. You should see x's next to items already listed in Column E, and blanks next to those that are not.
3) Reconciliation. In Column E, enter all unique line items in the appropriate section (Traits/Skills; Attributes won't change). Like Columns A-D, when inserting or deleting cells, always span Columns E-Q to keep the formulas lined up below. Further, make sure you insert new rows in the middle of the lists. This prevents overwriting key formulas at the bottom, and helps keep the formatting cleaner. Also make sure your spelling is consistent. When Column C has x's next to all line items (i.e. not section headings/SUBTOTALs), you have listed all the unique items. A secondary check is when the Miscellaneous Checks cells (two below the skill table in Column M) are the same. Both of those totals are unoptimized as noted. The Cost Summary block further down should also aid in error checking.
4) Optimization. Once you've accounted for everything listed in Columns A and B in Column E, spend XP in Column I until "XP Balance" is zero. As a note, attribute minimums must be manually entered and met. Additional Traits and Skills can be added by simply inserting blank lines in the Trait or Skill sections, making sure to span Columns E-Q to keep all the formulas aligned. Negative Traits added this way will be accounted for in the "Bought XP" block below the Balance Sheet.
5) Balance Sheet details. This section below the skill table helps account for the total XP pool:
Manually enter the base XP your GM has granted.
Attribute XP from Age and Flex XP from Age are automatically calculated from the character's age as determined by the life modules taken, and if you've chosen a Clan affiliation in cell T2 (if you're Clan, make sure "Clan" is first in the cell; it doesn't matter what comes after that). In the upper right hand corner of the sheet (Column R), make sure you enter all the module durations. These will be totaled in cell T16 to determine aging effects at the bottom of Column B, which are then summed in Column F.
Field Rebates. This cell automatically sums the field rebates from Column B. For the calculation to work, they should all be negative in Column B.
The Starting XP Pool is simply the sum of the Starting Base XP, Age XP (for Attributes and Flex), and Field Rebates.
Spent XP is the sum of all the End XP from column J.
XP Balance is the difference between the two, and should be zero when you're done.
Bought XP is a calculation based on negative traits that weren't part of modules (i.e. not listed in Columns A and B; these should have a Start XP of 0). The 10% limit is a manual check because GMs can bend this rule.
6) Finishing Touches. Enter the character's name, height in cm and weight in kg in Column T. Formulas below those cells provide height in inches and weight in pounds for those still stuck with non-metric measures. At this point, you may also wish to sort the Trait and Skill lists. I use Value/Level as the primary index, but you can use what ever you want. When sorting, make sure you grab all of the manually entered data at a minimum. The formulas will sort themselves out.
Note: The Sample Modules sheet only lists the modules used by the Sample Character in an effort to limit "verbatim material" as per a request from the Line Developer when I made the first version. The Data Tables sheet similarly has the minimum amount of information necessary for the lookup functions to work.
Please post questions and/or bug reports here.
