Author Topic: AToW Character Generation Spreadsheet (Redux)  (Read 46166 times)

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
AToW Character Generation Spreadsheet (Redux)
« on: 17 April 2016, 08:40:09 »
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#msg1915462

21JAN2023 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/#msg1901268

29OCT2022 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/#msg1878024

08FEB2019 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.
« Last Edit: 25 November 2023, 06:42:55 by Daryk »

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #1 on: 26 December 2017, 13:48:49 »
Phoenixstorm recently pointed out the Special Pilot Abilities (SPAs) I had missed in the A Time of War Companion.  These have now been added to v4-5 of my spreadsheet, along with the Trait introduced in the Companion (Mutation). If the TPTB add "Lucky" or any other SPAs I missed from Campaign Operations, they will be added to a future version.

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #2 on: 14 January 2019, 16:45:00 »
Helping BiggRigg42 with his videos on character creation led me to discover errors in the sample character in the spreadsheet.  They have been corrected in v4-6, attached below.  No calculations were affected, so those who downloaded the previous version don't need to re-download this one.  The instructions in the first post are also still good.

BiggRigg42

  • Catalyst Demo Team
  • Master Sergeant
  • *
  • Posts: 375
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #3 on: 02 February 2019, 01:05:29 »
I'm getting close to figuring out this Excel character generator. I completed step 3 in your instructions, and there is an x next to all of the line items column C. However, I have not met the secondary check within step 3; the miscellaneous checks cells are not equal.


Do you have any idea what may have went wrong or what I should check for?

Wait, I think I just figured it out. Nobody give me the answer just yet. Say a prayer of supplication for me.

Blessed be Blake's word. I figured it out. Go figure I would fix it on my own only after I asked for help.
« Last Edit: 02 February 2019, 01:13:32 by BiggRigg42 »

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #4 on: 02 February 2019, 05:01:43 »
If you do need help, just let me know!  Glad you were able to figure it out!  :thumbsup:

BiggRigg42

  • Catalyst Demo Team
  • Master Sergeant
  • *
  • Posts: 375
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #5 on: 03 February 2019, 22:15:54 »
Peace of Blake be upon you my friend. My conflict with the machine has ended.

I have reviewed this and edited it some, though I may wait a bit longer before I put it in the playlist with other videos and tell the world (i.e., Facebook) about it. So, everyone should feel free to point out any mistakes I may have made, but I don't think there is anything significantly wrong with this.

https://www.youtube.com/watch?v=kafn-J5fhsM

Maelwys

  • Major
  • *
  • Posts: 4872
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #6 on: 03 February 2019, 23:02:44 »
Did anyone else get some weird green screen at around 9:59?

And I don't know if you've considered it, or if you thought it might be too clunky, but have you thought about laying the excel sheet on top of the PDF so you can manipulate it without having to swap it back and forth constantly?

I'm also not sure on how it would work with the chargen exactly (I use my own sheet so aren't familiar with it), but what about keeping the Main Affiliation (FWL) and its Sub Affiliation all in the first Stage 0 box, and the CS main and sub affiliation in the second box, rather than splitting them up?

My guess with the problems with the Xs that you have is that you either didn't highlight columns C and D when you were inserting rows. or you forgot to fill down the C column.

And just a hint, especially since the typing matters in the spreadsheet, you can use Tab to autocomplete phrases that are already in the Column. So for instance, after you typed "Training" for the first time, you can simply start to type "Training" and hit tab once it pops up. Be careful though, because if you have similar phrases (Pilot/Mech and Pilot/Aircraft) using this shortcut can give you the wrong one if you're not paying attention.
« Last Edit: 03 February 2019, 23:51:18 by Maelwys »

BiggRigg42

  • Catalyst Demo Team
  • Master Sergeant
  • *
  • Posts: 375
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #7 on: 03 February 2019, 23:22:51 »
Did anyone else get some weird green screen at around 9:59?

And I don't know if you've considered it, or if you thought it might be too clunky, but have you thought about laying the excel sheet on top of the PDF so you can manipulate it without having to swap it back and forth constantly?

I'm also not sure on how it would work with the chargen exactly (I use my own sheet so aren't familiar with it), but what about keeping the Main Affiliation (FWL) and its Sub Affiliation all in the first Stage 0 box, and the CS main and sub affiliation in the second box, rather than splitting them up?



The green screen is a result of iMovie not playing nice with the Excel frames; I don't think there is anything I can do about it. A lot of this video was cut out and pasted back together. This is signified to happen when the screen turns into a box and rotates. Also, I'm not sure how laying the excel sheet on top of the PDF would work.
« Last Edit: 03 February 2019, 23:30:19 by BiggRigg42 »

Maelwys

  • Major
  • *
  • Posts: 4872
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #8 on: 04 February 2019, 00:12:38 »
Also, the White Collar/ComStar Prerequisite conflict isn't exactly a conflict.

White Collar requires a +3 TP in Property OR Wealth.

ComStar says you can't take any Property Traits. So that means you have to take Wealth at +3 to satisfy the White Collar Life Path prerequisite and the ComStar Affiliation restrictions. The work around is fine, just pointing out that there isn't exactly a conflict there.

edit
And its driving me crazy, but what are the asterisks in Column D signifying? I can't seem to figure it out.
/edit

Also, not sure how the sheet handles it, but I think your forgot to spend your Flex XP. At the end, it seems to have accumulated nearly 500 points in your Skill section, but Flex isn't a skill or trait, its a placeholder for XP that lets you customize each life path.

So unless I'm missing something, and that's possible, you might have a large chunk of XP just sitting there.
« Last Edit: 04 February 2019, 00:38:40 by Maelwys »

BiggRigg42

  • Catalyst Demo Team
  • Master Sergeant
  • *
  • Posts: 375
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #9 on: 04 February 2019, 00:35:39 »
Also, the White Collar/ComStar Prerequisite conflict isn't exactly a conflict.

White Collar requires a +3 TP in Property OR Wealth.

ComStar says you can't take any Property Traits. So that means you have to take Wealth at +3 to satisfy the White Collar Life Path prerequisite and the ComStar Affiliation restrictions. The work around is fine, just pointing out that there isn't exactly a conflict there.

edit

And its driving me crazy, but what are the asterisks in Column D signifying? I can't seem to figure it out.
/edit

You are right; it's not a conflict. However, I needed at least a 3+TP in wealth, which I didn't take. So, I still used the Game Master Arbitration rule. I must have been tired; I'm not sure why I thought I saw a conflict there. This will be mentioned in the video description.

The asterisks signify some kind of arbitrary choice. I explain how they work at one point but don't use them regularly. They are just for error checking and don't change anything.

Maelwys

  • Major
  • *
  • Posts: 4872
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #10 on: 04 February 2019, 00:41:10 »
Ah, okay. That's cool. Placeholders are helpful.

Hope you caught the second edit of my post above, that might be the most helpful one.

BiggRigg42

  • Catalyst Demo Team
  • Master Sergeant
  • *
  • Posts: 375
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #11 on: 04 February 2019, 01:18:48 »
Ah, okay. That's cool. Placeholders are helpful.

Hope you caught the second edit of my post above, that might be the most helpful one.


Ah yes, the flex is supposed to go where I put it per the sample character I am mimicking. However, I may have filled down the formulas too far. I'm not sure if this made a difference or not. We will have to ask Daryk. There may be a really easy fix for this.
« Last Edit: 04 February 2019, 01:22:41 by BiggRigg42 »

BiggRigg42

  • Catalyst Demo Team
  • Master Sergeant
  • *
  • Posts: 375
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #12 on: 04 February 2019, 01:31:53 »
If you do need help, just let me know!  Glad you were able to figure it out!  :thumbsup:

Hey Daryk, Maelwys may have pointed out a mistake I made with my Flex XP. I probably filled down the formulas in columns E-Q too far. There is probably an easy fix for this.



Maelwys

  • Major
  • *
  • Posts: 4872
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #13 on: 04 February 2019, 01:46:20 »
Yeah, I was about to say that he had it done differently, I'm just not sure how, and what the results might be.

I'm not sure if it got mentioned or not, and if this video was more of a "here's how to use the spreadsheet" rather than "Here's how to make a character," but you might want to mention that there are limits to what you can spend Flex points on. For instance, in Stage 2 you can't spend more than 35 flex points on a single skill, or more than 200 on a single Trait. I don't think the spreadsheet can limit it, but its something to keep in mind as the creation process goes on.

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #14 on: 04 February 2019, 04:10:54 »
Yep, you went too far with the formulas to the right of column F.  Column G should be simply "negative" column F, and I, K, and M through Q should be blank.  G, J, and L should be SUBTOTALs of the columns to their left (for G and J, that's the same as above in the list; L is very different).

I'll try to take a look at the video tonight after work, thanks!  :thumbsup:

BiggRigg42

  • Catalyst Demo Team
  • Master Sergeant
  • *
  • Posts: 375
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #15 on: 04 February 2019, 09:16:24 »
Cool, I may make a part 2 correcting the Flex XP or see if I can still edit some more.

CoreWatch

  • Private
  • *
  • Posts: 27
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #16 on: 04 February 2019, 14:58:09 »
G'day gents!

I peruse these forums every now and then for info and new ideas to use myself for use in my BT/MW campaign and I came across this thread. First off, kudos for keeping the fire alive for this game. It's honestly a hard sell to get some players to make characters for this game without getting frustrated due to the complexity of making a character. So, every time I see someone willing to put up nice and simple excel sheet (like yours Daryk) and now a simple and effective YouTube training video (like yours BiggRiggs42), well needless to say it makes me happy to know that folks care enough to keep it alive and educate the masses!

Having said that, I'd like to add that once upon a time on this forum, a gentleman by the name of Gigobyte also had a decent character creator excel sheet up on this forum as well. While I'm not trying to hijack your thread or promote his character sheet over yours. I figured I would at least throw it up here for folks to access from my dropbox. Again the credit is all his for the creation of it, I've just have made use of it in my game.

Gigobyte's Character Creator on CoreWatch's Dropbox

https://www.dropbox.com/sh/w17kyhv3jsgwtlo/AAAX7TuhsoO5FRRam68S1mSka?dl=0

Cheers gents!
-CW


Addendum...

Here was his original thread on this forum as well for it...

https://bg.battletech.com/forums/index.php?topic=42679.0
« Last Edit: 04 February 2019, 15:05:22 by CoreWatch »

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #17 on: 04 February 2019, 17:39:07 »
No worries, CoreWatch!  I think I corresponded with Gigobyte back in the day.  I offer my players the option of just telling me what they want, and doing the heavy lifting for them with my spreadsheet, then letting them negotiate tweaks.

I'm almost through all my threads, and will hit the video as soon as I'm done...

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #18 on: 04 February 2019, 17:53:02 »
OK, first things first... my name is pronounced like the more conventionally spelled "Derek".  I blame my parents...  ;D

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #19 on: 04 February 2019, 17:57:14 »
Quick note: the SUBTOTAL formula is useful because Excel will ignore it when doing SUM functions on the column.  That's why I used it (to aid error checking without requiring me to change the SUMs).

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #20 on: 04 February 2019, 18:02:30 »
I'll say you chose the most challenging Stage 0 with ComStar.  If you PM your e-mail address, I'll clog your inbox with the spreadsheet from my game (which is MOSTLY ComStar personnel).  You'll probably have to check your spam folder when I send it, as it's pretty big.

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #21 on: 04 February 2019, 18:08:37 »
You can do the Secondary Affiliations that way, but I insert new headings with new SUBTOTAL functions to make error checking easier.  That's just me, though.  You do you!  8)

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #22 on: 04 February 2019, 18:12:07 »
Excel tip: when you click on a range in a cell (like when you did for B15:B16), you will notice it highlights on the sheet with a colored box.  You can also drag the corner of that colored box to adjust the range.  :thumbsup:

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #23 on: 04 February 2019, 18:20:20 »
I'm hoping you eventually caught that "/" in "Small Arms" in your Basic Training Field around 19:00.  That may have contributed to your accounting error later...

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #24 on: 04 February 2019, 18:25:49 »
The only other point I'd make around 22:00 about Skill Fields from schools is that you can take only two... you don't HAVE to take three.  You do need to take at least two, though (one Basic, and "at least one" Advanced, per page 70).

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #25 on: 04 February 2019, 18:29:53 »
Another Excel tip, just shy of 25:00: if you simply delete those extra cells below Row 144, the SUBTOTAL will automatically adjust.

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #26 on: 04 February 2019, 18:33:26 »
Just shy of 26:00, I think I spotted another accounting error.  I think you missed the 50 XP of Flex from the ComStar Service Stage 4 module.  Hopefully you caught that, but I'm only half way through the video...

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #27 on: 04 February 2019, 18:35:57 »
Around 27:00, you might want to mention that you have to enter in all the ages correctly (to include making sure the "Age" total covers the right cells) for the "Aging Effects" rows to adjust correctly.

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #28 on: 04 February 2019, 18:37:51 »
That is a very clear explanation around 28:00!  :thumbsup:

Daryk

  • Lieutenant General
  • *
  • Posts: 37046
  • The Double Deuce II/II-σ
Re: AToW Character Generation Spreadsheet (Redux)
« Reply #29 on: 04 February 2019, 18:42:35 »
Aha!  I see where the error with the Flex originated around 31:00... It looks like you overwrote the Flex with Administration.  That probably caused no end of trouble with the accounting.  Perhaps I should mention that in the instructions (i.e., to only insert new cells above the Flex)...

 

Register