Register Register

Author Topic: Infantry Calculator (TacOps, IntOps Augmented Warriors, and AToW Companion)  (Read 3797 times)

Cryhavok101

  • Captain
  • *
  • Posts: 1842
https://docs.google.com/spreadsheets/d/1N8Hd_5V0t7R7ola-ZnrUdJqEcsvwSX_1GJAUtkLJ5vQ/edit?usp=sharing

Link updated April 24, 2017
New: I set the one in the link to view only, so to mess with it, you need to go to 'File' and select 'Make a copy' to get your own editable version.

Edit: For anyone who didn't know, this sheet doesn't work in other spreadsheet programs. It uses formulas that don't translate from one to another. Here is a link to instructions on using google sheets offline.

I put this together because MegaMekLab's didn't do everything I wanted it to. It does require knowledge of the construction rules for infantry, and doesn't really stop you from doing things you aren't supposed to. You'll have to pay attention to the rules on limits and things on your own, or disregard them and do whatever you want lol.

The only exception to that is: it will not calculate damage per remaining troopers for any number beyond 30. Someone who understands how to read spreadsheet formulas can probably figure out how to change that if they really wanted to, but I would have to set stuff up for each number after 30 and honestly, I can't be bothered, since 30 is the size limit in the rules. (in the rules anything bigger than that gets split into multiple smaller units that are within that limit, IE a platoon of 100 troopers would be split into 4 groups of 25 troopers)

Green=place to put info yourself
Blue=place to put something's name
Light Grey=Formula, don't touch it, it calculates stuff for you.
Yellow=Important Total, do not touch, calculated for you.

Though anyone with that link can edit it, I highly recommend going to file and selecting "Make a Copy" instead. This will give you your own version of it to mess around with and leave the original for others to copy as well. (I already did this for myself, and will only be using my own).

I did make a few assumptions based on what made sense to me at the time, which I have questions posted for in the rules forum, and I do plan on updating this based on the answers I get.

EDIT:

So my development list is:
  • Armor automation
  • Field Gun automation
  • Create Beasts
  • Fields to add created beasts to unit type drop-down
  • Fields to add created beast to secondary weapon drop-down
  • Augmented warriors
  • Fields added to select skill levels to modify BV

Cryhavok101

  • Captain
  • *
  • Posts: 1842
Re: Tactical Operations Infantry Calculator
« Reply #1 on: 10 December 2016, 21:54:12 »
Updated. Added more automation. You can select from different weapons and it will automatically fill in damage, BV and C-bill info for them. It won't tell how many crew a support weapon takes, or if it is encumbering though.

Daryk

  • Lieutenant Colonel
  • *
  • Posts: 9572
  • The Double Deuce II/II-σ
Re: Tactical Operations Infantry Calculator
« Reply #2 on: 11 December 2016, 08:19:33 »
I'm not seeing the square root in the cost formula... are you still refining that?  Also, it seems to use the total size of the platoon for the number of primary weapons vice the size less the number of secondary weapons.  I also recommend labelling the "Secondary Weapon Crew" cell as "Number of Secondary Weapons" (with a drop down to be 0, 1 or 2).

Cryhavok101

  • Captain
  • *
  • Posts: 1842
Re: Tactical Operations Infantry Calculator
« Reply #3 on: 11 December 2016, 12:51:07 »
Thanks for pointing those out. If you find anything else, I'll check on fixing it too.

I am still refining it. I am working on getting the armor to work as a drop-down selection as well. I will probably do the same with the field guns at some point. I am also going to be working on a second sheet for creating beasts, and fields that will let you add the created beast to the dropdown for unit type, and another that will let you add created a beast to the secondary weapon dropdown for "K-9" squads. And I still have to work on the augmented warrior stuff. I am least looking forward to that, because of how different augments apply to c-bill and BV calculations differently.

So my development list is:
  • Armor automation
  • Field Gun automation
  • Create Beasts
  • Fields to add created beasts to unit type drop-down
  • Fields to add created beast to secondary weapon drop-down
  • Augmented warriors

Cryhavok101

  • Captain
  • *
  • Posts: 1842
Re: Tactical Operations Infantry Calculator
« Reply #4 on: 11 December 2016, 13:08:02 »
Got those fixes in. You select 'number of secondary weapons per squad' from a dropdown, and now also select 'number of squads' and 'number of troopers per squad'.

Cryhavok101

  • Captain
  • *
  • Posts: 1842
Re: Tactical Operations Infantry Calculator
« Reply #5 on: 11 December 2016, 13:55:05 »
Got the Infantry Armor automated. Also automated the Target Modifier part of the BV calculations.

Daryk

  • Lieutenant Colonel
  • *
  • Posts: 9572
  • The Double Deuce II/II-σ
Re: Tactical Operations Infantry Calculator
« Reply #6 on: 11 December 2016, 14:06:17 »
Great progress today!  The number of squads drop down is wonky, though: 19 is at the top of the list, then goes to down to 10, then 1, then 30 down to 20, then 9 to 2.  I also think it might better to put the square root factor into Column F so a mental subtotal will look something like the bottom line. The should simplify your total calculation too.

Cryhavok101

  • Captain
  • *
  • Posts: 1842
Re: Tactical Operations Infantry Calculator
« Reply #7 on: 11 December 2016, 14:26:58 »
Great Idea for the square roots. Implemented.

Made a change to the number of squads list to make it a little better. Unfortunately it is still problematic. It is a problem with google sheets rather than anything I am doing. Their data validation likes to rearrange number lists based on the one you have selected, so if you pick 3 the list will start with 3, then 30, then continue the rest of the list in normal number order. 1 and 2 will do that as well but with every number that has a first digit of 3.

Daryk

  • Lieutenant Colonel
  • *
  • Posts: 9572
  • The Double Deuce II/II-σ
Re: Tactical Operations Infantry Calculator
« Reply #8 on: 11 December 2016, 14:35:26 »
It sounds like it's sorting alphabetically instead of by number.  Does it recognize lists of numbers, or just text?

Cryhavok101

  • Captain
  • *
  • Posts: 1842
Re: Tactical Operations Infantry Calculator
« Reply #9 on: 11 December 2016, 14:53:16 »
Fixed a problem in Total Damage that cropped up from my changes to secondary weapon stuff.

Cryhavok101

  • Captain
  • *
  • Posts: 1842
Re: Tactical Operations Infantry Calculator
« Reply #10 on: 11 December 2016, 14:55:55 »
It sounds like it's sorting alphabetically instead of by number.  Does it recognize lists of numbers, or just text?

Messing with the format to make sure it knew it was looking at a list of numbers didn't change anything.

Daryk

  • Lieutenant Colonel
  • *
  • Posts: 9572
  • The Double Deuce II/II-σ
Re: Tactical Operations Infantry Calculator
« Reply #11 on: 11 December 2016, 15:00:02 »
Odd that... I mostly use Excel, or Open Office.  Sorry I can't be more help with Google Sheets...

Cryhavok101

  • Captain
  • *
  • Posts: 1842
Re: Tactical Operations Infantry Calculator
« Reply #12 on: 11 December 2016, 15:03:44 »
No problem. I would use excel more, but I have found google sheets to be the most convenient for me, personally. There are just a few things it can't do, or doesn't do as well as excel.


Cryhavok101

  • Captain
  • *
  • Posts: 1842
Re: Tactical Operations Infantry Calculator
« Reply #14 on: 18 December 2016, 14:05:17 »
Added a second Sheet to create a custom beast (or enter one from the RPG).
It automatically populates as a new Unit type on the infantry calculator sheet.

For Large or Larger beasts, treat each squad as the number of infantry you can put on one beast. This is because the type of beast decides how many support weapons you can have on it, instead of the 1-2 per squad.

Cryhavok101

  • Captain
  • *
  • Posts: 1842
Re: Tactical Operations Infantry Calculator
« Reply #15 on: 18 December 2016, 14:24:33 »
Now it also shows maximum secondary weapons per squad/beast, based on infantry type.

Cryhavok101

  • Captain
  • *
  • Posts: 1842
Re: Tactical Operations Infantry Calculator
« Reply #16 on: 18 December 2016, 14:28:52 »
I realized I should also mention that most of the time I have updated this, I have posted a New link in the OP, because I replaced the old sheet with an updated one.

This is because I am working off my own master and creating a public one that is separate which I share. Each update is done first on my master, and then I make a new copy and replace the old public version with the new copy. I have added a "link last updated" date to the OP, for clarity.

Cryhavok101

  • Captain
  • *
  • Posts: 1842
Updated the sheet with the ability to add creatures as K-9 forces, taking the secondary weapon slots in a squad as per ATOW Companion pg 135. These 'K-9s' do not affect the BV due to no rules in ATOW Companion touching on how to calculate that. Since they are only useful against unarmored targets in the same hex as you, I can't see them adding a ton to the BV though.

Cryhavok101

  • Captain
  • *
  • Posts: 1842
I thought I would mention that for the K-9 stuff you use the creature creator to build the monster (or just input the stats of an existing creature), and if it is small or medium sized, it will show up in the support weapons.

Cryhavok101

  • Captain
  • *
  • Posts: 1842
New link with major changes: https://docs.google.com/spreadsheets/d/1N8Hd_5V0t7R7ola-ZnrUdJqEcsvwSX_1GJAUtkLJ5vQ/edit?usp=sharing

I set the one in the link to view only, so to mess with it, you need to go to 'File' and select 'Make a copy' to get your own editable version.

I finally finished adding in everything for augmented warriors. Like other parts, it doesn't put hard limits on things, and you will need to know the rules on what you can and can't do... or purposefully ignore them, as you desire.

Most of the augmented warrior stuff should be self explanatory, if you are familiar with the rules, but if not, feel free to ask questions.

I also added a field to set the unit's skill levels so the BV2 will get modified here for it, and you don't have to figure that out later.

As always, if anyone finds anything wrong, please let me know.

Cryhavok101

  • Captain
  • *
  • Posts: 1842
For anyone who didn't know, this sheet doesn't work in other spreadsheet programs. It uses formulas that don't translate from one to another. Here is a link to instructions on using google sheets offline.