BattleTech - The Board Game of Armored Combat
BattleTech Game Systems => Ground Combat => Topic started by: boilerman on 05 May 2013, 00:36:39
-
Hello.
I've updated my conventional infantry platoon creation spreadsheet. Since it is so big, 614kb, I have stored it at box.com. You can download it by going to the link below.
https://www.box.com/s/8ns4cgbyrnakidviq0vn (https://www.box.com/s/8ns4cgbyrnakidviq0vn)
The instruction pdf:
https://www.box.com/s/2zmimm0ve5gvpxd08jvw (https://www.box.com/s/2zmimm0ve5gvpxd08jvw)
-
A 30 man WoB (WoB Armor and Mauser 1200) Jump Platoon with A-M training is 93 million and change.
Suddenly infantry doesn't seem like that cheap alternative to `Mechs :)
-
Unable to complete...
Is there a user manual?
[edit:1] Still unable to get past 24 soldiers @ 6 per 4 Squad. Any ideas how?
[edit:2] How can I custom the Infantry numbers if the blue user inputs won't allow it? Won't allow me to enter names either.
TT
-
Yes, I think I better write on. I'll try and have something posted tomorrow.
-
THis is great!
I had to remember to "enable editing" in Office 2010, and when saving it complained about links to "TacOps_IPCR", but I found it easy to use and worked well.
Many thanks!
-
THis is great!
I had to remember to "enable editing" in Office 2010, and when saving it complained about links to "TacOps_IPCR", but I found it easy to use and worked well.
Many thanks!
I thought I fixed that "TacOps" thing. Looks like I have my first fix. Of course if you find any more please post them here and I try and fix them.
And FYI for those that don't know there are a number of user defined functions in the spreadsheet so you must enable macros.
I also set the TRO worksheet up so that you can copy and paste it into another spreadsheet to save it.
-
Sweet, an update to this most excellent spreadsheet! Any extra functions/options since your last version?
-
The various BVs seem slightly off. For instance, I have a Jump Capable platoon of 30 men (5 squads of 6 men), armed with Mauser 1200s and the WoB Infantry Armor Kit. Not Anti-Mech trained at all.
The BV on the TacOps_ICR worksheet is 201. No clue if that's right or not.
But when I go to the BV worksheet, the BV is listed as 236.
When I set it to A-M Trained with a skill of 5, the BV on TacOps_ICR changes to 236. The BV on the BV page remains the same at 236.
However, the A-M Battle Rating on the BV worksheet remains the same at 0.00. It doesn't change when I turn on the AM training.
I'm also not sure whats going on, but on the TacOps_IPCR Worksheet there's a couple of IF statements in B12, C12, D12 and F12 that show some results, but I have no idea what they are (looks like something to do with displaying certain lists, and certain values if using Beast mounted infantry or something).
And a small suggestion. Put the BV worksheet either before or after the TRO page, instead of all the way at the end. People are more likely to look at that rather than 10 pages of data :)
-
This workbook was created to do the basic number crunching for creating BattleTech conventional infantry platoons using the rules in TechManual & Tactical Operations. To fully understand how this spreadsheet works you should be familiar with those rules since this workbook does not prevent construction rules violations.
There are only 20 real inputs in this spreadsheet: 17 pulldown menus & 4 number inputs. Pulldown menus:
Infantry Type,
Infantry Specialty,
Engineering Specialty,
XCT Training category,
Gunnery Skill TN,
Secondary Weapons Per Squad,
Anti-‘Mech Attack Equipment,
Anti-‘Mech Skill TN,
Primary Weapon Type & Model,
Secondary Weapon Type & Model,
Disposable Weapon Model,
Field Gun/Field Artillery Model,
Infantry Armor Type,
Riding Beast Species.
Numerical Inputs:
Soldiers Per Squad/Riding Beasts Per Platoon
Number of Squads,
Paramedics Assigned,
Number of Field Guns/Field Artillery.
Title blocks for unit name and notable units are also provided for the TRO.
There are approximately 40 user defined functions in this spreadsheet so you will have to have macros enabled for it to function. I have utilized conditional formatting to grey out or highlight different cells based on selections to help guide the creation process.
Note when selecting the Beast Mounted platoon type 2 cells automatically go red. They will not change color until a riding beast species is chosen in the Beast Mounted Infantry stats block further down the worksheet. This is because certain parameters of a beast mounted platoon are based on the size of the animal. Field guns and field artillery work similarly.
There are several things you need to understand concerning the anti-‘Mech capability in this spreadsheet. Of course the basic capability is based on infantry platoon type, but in the latest version of the TechManual errata something has been added; if a platoon is capable, but not trained or equipped its mass and BV are adjusted.
All infantry types capable of anti-‘Mech attacks have had their individual soldier masses reduced by 15kg. If a soldier is anti-‘Mech attack trained and equipped the soldier’s mass increases 15kg. In the spreadsheet the default is to assume that if the basic platoon type is capable of anti-‘Mech attacks it is equipped and trained, which effects cost. In the Anti-‘Mech Training block you must chose “No” on the Equipped dropdown menu to adjust the mass and cost of the platoon down. If an infantry armor type is chosen that is encumbering the anti-‘Mech stats will adjust accordingly.
This spreadsheet calculates Battle Values, the base, and adjusted. The base BV is standard design BV, which is in turn based of a gunnery skill of 4 and an anti-‘Mech skill of 5. If a platoon is anti-‘Mech capable but not trained or equipped the base BV is adjusted down in accordance with the TechManual errata v3.1. The adjusted BV is of course adjusted based on the platoons gunnery and anti-‘Mech skill levels input via their respectively dropdown lists.
One of my goals with this latest version of the sheet was to have it create a canon style TRO automatically so all you had to do was copy and paste it into sheet or a word document for saving. I’ve come really close. You will need to manually bold parts of the text to exactly copy the canon TRO. I have also added some lines, to reflect such things as disposable weapons, which are used in the published platoons so far.
I cannot say everything is 100% accurate, but I have worked pretty hard to follow the design calculation process exactly. Please let me know if you see problems and I will try to resolve them as quickly as I can.
All constructive criticism is appreciated. Enjoy.
Duane “Boilerman” Bywaters
battletech.boilerman@gmail.com
5/6/2013
A few other things:
Some of the damage values and BV values for the weapons might not be accurate. I tried to verify everything, and Herb can attest to the fact that I badgered him quite a few times about infantry weapon stats but I may have missed something. I do plan on going through the stats again ASAP. On the various equipment pages I have put in some of my own equipment stats. Most of the ones added just changed the name and availability dates. By the way, my “Battle Rifle” is simply an M&G G-150 rifle with a larger magazine. I use it to represent something like an M14 or FN FAL.
I think that will get you started. I will try to flesh this out over the next few days or so to make it more complete. Just remember the key things are the dropdown menus and number inputs for “Squad per Squad,” “Number of Squads” and “Paramedics Assigned” and for beasts and field guns when those options are selected.
-
The various BVs seem slightly off. For instance, I have a Jump Capable platoon of 30 men (5 squads of 6 men), armed with Mauser 1200s and the WoB Infantry Armor Kit. Not Anti-Mech trained at all.
The BV on the TacOps_ICR worksheet is 201. No clue if that's right or not.
But when I go to the BV worksheet, the BV is listed as 236.
When I set it to A-M Trained with a skill of 5, the BV on TacOps_ICR changes to 236. The BV on the BV page remains the same at 236.
However, the A-M Battle Rating on the BV worksheet remains the same at 0.00. It doesn't change when I turn on the AM training.
I'm also not sure whats going on, but on the TacOps_IPCR Worksheet there's a couple of IF statements in B12, C12, D12 and F12 that show some results, but I have no idea what they are (looks like something to do with displaying certain lists, and certain values if using Beast mounted infantry or something).
And a small suggestion. Put the BV worksheet either before or after the TRO page, instead of all the way at the end. People are more likely to look at that rather than 10 pages of data :)
Better disregard BV numbers for now. those cells on row 12 do provide input for some cells. Right clip on the worksheet tab and you can move the worksheet tab any where you like.
-
This is cool! Thanks!
Although I tried to idiot proof this sheet
Every time I've tried that at work, someone's invented a better idiot. :)
Probably won't run into that here though.
-
Updated, I think I have BV working properly now. I have also updated weapon damage values and BV. The link below will take you to version 4K3.
https://www.box.com/s/2998kzv89gksh91jqjfn (https://www.box.com/s/2998kzv89gksh91jqjfn)
The instruction pdf:
https://www.box.com/s/2zmimm0ve5gvpxd08jvw (https://www.box.com/s/2zmimm0ve5gvpxd08jvw)
Sweet, an update to this most excellent spreadsheet! Any extra functions/options since your last version?
Got several little details fixed. BV should work now, beast-mounts should work. I'm sure there are several things I'm missing since I don't remember what didn't work before I dug back into this project.
-
Boilermaker,
Got the new version, love it!
I do note that it's no longer possible for one man with a heavy crossbow to score "golden BB" hits on 'Mechs; you need at least 14 ;) Have yet to explore the wonders of mechanised & beast-mounted infantry, which have always been in the "too hard" basket previously.
Many thanks!
-
Link is not working for me? Try Google Docs?
-
Should work now. Changed the links in the first post.
-
Ooh, this looks nifty! Thanks boilerman, will play around with this when I get home.
-
Boilerman,
This couldn't have been better timed. I've been working to build out some of my 'Mech companies into combined-arms battalions. This has involved much fun with DA infantry stands ;) Your spreadsheet has been a delight to use to try and work out what my custom platoons can do in the game.
A couple of questions, which may be based on my lack of complete comprehension of the infantry construction rules, and not the fault of your spreadsheet:
1) Took a while for me to realise that field gun platoons need to be Motorised (I was thinking they'd be Foot, and need a carrier vehicle, but this is completely sensible in-game). Consider the situation: I have a platoon of 4 squads of 7 men each. Each squad has one LAC-5, for a total of 5 per platoon. SO what we have in "reality" is 20 men serving the guns, and 8 standing around with rifles.
Question: the Standard Weapon damage (roes 63 and 68) seems to calculate standard weapons damage as if all 28 were firing their rifles, where I'd have expected it to be much lower. Is this an artifact of the rules (eg. you either fire the rifles or fire the LAC), or a potential enhancement?
2) IIRC, there are some rules addressing encumbrance on infantry - things that happen if you have more than one support weapon per squad, things that happen if you have heavy armour. Any chance of having these sort of effects listed in the "Special Effects & Notes" section row 42 and under?
I use Excel a lot at work, and figured I was pretty hot with it, but this is marvellously insane! Thanks for all the work.
Cheers,
W.
-
1) Took a while for me to realise that field gun platoons need to be Motorised (I was thinking they'd be Foot, and need a carrier vehicle, but this is completely sensible in-game). Consider the situation: I have a platoon of 4 squads of 7 men each. Each squad has one LAC-5, for a total of 5 per platoon. SO what we have in "reality" is 20 men serving the guns, and 8 standing around with rifles.
Question: the Standard Weapon damage (roes 63 and 68) seems to calculate standard weapons damage as if all 28 were firing their rifles, where I'd have expected it to be much lower. Is this an artifact of the rules (eg. you either fire the rifles or fire the LAC), or a potential enhancement?
This is indeed a rules thing. You are correct in that a platoon can choose to fire their field guns *or* their normal guns in a given turn but not both, and whichever one is fired has the firepower associated wil the full current strength of the platoon.
Heck, I think Motor platoons with field guns can even choose to do anti-mech attacks if they want! (Would not be surprised if a rule does exist prohibiting it that I've forgotten.)
-
It is also good to know that while shooting their rifles infantry can ignore stealth armour (but not mimetic) but the field guns are affected by it normally.
-
Thanks for the comments guys. WT as Weirdo said, it's either fire the big guns or the infantry weapons, not both, regardless of how many people are required to handle the big guns.
As for encumbrance, putting a note to that effect in the notes line would be useful in my opinion. I'll see how I can put it in. Another school semester has started though so it might be a little while before I can get to it.
And as far as I know, yes a motorized platoon of field guns or field artillery is capable of anti-Mech attacks.
-
And as far as I know, yes a motorized platoon of field guns or field artillery is capable of anti-Mech attacks.
Sweet. Now to get some use in before a rules dev notices the obvious problem here and prohibits them from using said field guns during a swarm. [skull]
-
And as far as I know, yes a motorized platoon of field guns or field artillery is capable of anti-Mech attacks.
Thanks, Boilerman. Consider yourself a class example of the good that happens when fans decide to take on one of the more obscure corners of the BT universe, and decide "Ask not what Battletech can do for me ..." O0
-
Sweet. Now to get some use in before a rules dev notices the obvious problem here and prohibits them from using said field guns during a swarm. [skull]
Oh, look what time it is: TW errata time.
-
Take your time. Field guns are TacOps.
-
Take your time. Field guns are TacOps.
This will only briefly delay me. O:-)
-
Sweet. Now to get some use in before a rules dev notices the obvious problem here and prohibits them from using said field guns during a swarm. [skull]
Done!
(http://www.solaris7.com/files/members/602/Desperate_attack.jpg)
-
Cool work WT. O0
I've bee working on another spreadsheet project. It's a TO&E sheet. It's a big one, payroll, calculates unit quality and sums up combat asset values for contract purposes using the IO betas that were posted a few months ago: to name a few features. I'm still working on the maintenance requirements. I hope to polish it for posting in the next month or two, school work permitting.
-
I've updated the sheet to correct a couple of problems I discovered when playing around with it. The platoon cost listed in the TRO was incorrect. For the moment I have disabled a function that modified the cost for the TRO. It adds the commas to make it look neat but it's having problems cutting up the value. The other fix solved the problem with creating combat engineering units other than foot infantry. Had to fully implement some changes I made before I posted the last version, got distracted and didn't complete the job. Sorry worktroll I will get to modifying the sheet as you recommended, it's just going to take a bit more than I thought it would.
https://www.box.com/s/8ns4cgbyrnakidviq0vn (https://www.box.com/s/8ns4cgbyrnakidviq0vn)
The link at the top of the thread has been changed as well. Both take you to the V4K3B version.
-
Downloaded, and will give it a whirl.
One (or two?) other request. At the moment, even though you have the maxium squad size and maximum platoon size on the "Infantry TRO Types" tab (B4-C23), I can quite easily (for example) have a platoon of foot/mountain troops with 4 squads of 15 men each for 60 troopers, when the "legal limits' are 10 men/squad and 20 men total. Don't know how hard it would be to put the limits in the "V" column as read-only , eg.
V5 "Max"
V6 max soldiers per squad
V7 Max number of squads (being max platoon size / current squad size, U6)
V8 max platoon size
and to have the values in U6 and U8 go red if the limit is exceeded.
Just a thought, and thanks again,
W.
-
Can we get the various Clan ACs for field guns?
-
Cool work WT. O0
I've bee working on another spreadsheet project. It's a TO&E sheet. It's a big one, payroll, calculates unit quality and sums up combat asset values for contract purposes using the IO betas that were posted a few months ago: to name a few features. I'm still working on the maintenance requirements. I hope to polish it for posting in the next month or two, school work permitting.
there's a excellent TO&E sheet on PRI that i use (and have heavily modified as i learn how to use Excel with a gruber tool and large mallets)
oh wait. you got links there too LOL... i use Erewhon's MTOE file myself. Not quite as advanced (ill admit I'm actually intimidated by the complexity) but i works for my needs. is there possibility of merging those files? it would be cool to figure out my fiance's Dragoon rating, for example.
-
WT my original goal was simply to warn people when they exceeded TM max sizes. I could modify things to make it more explicit when those limits are exceed but I didn't want to restrict experimentation.
Can we get the various Clan ACs for field guns?
Another one of those items on the to-do list I forgot about. It will probably be a while but I will add them. Thanks for the reminder.
-
there's a excellent TO&E sheet on PRI that i use (and have heavily modified as i learn how to use Excel with a gruber tool and large mallets)
oh wait. you got links there too LOL... i use Erewhon's MTOE file myself. Not quite as advanced (ill admit I'm actually intimidated by the complexity) but i works for my needs. is there possibility of merging those files? it would be cool to figure out my fiance's Dragoon rating, for example.
I like pull down menus and user defined functions that do most of the work. Mine is huge, 21MB, full of a lot of junk and far from finished but what I've got so far is linked below. The color code for functionality is similar to that in platoon creator.
https://www.box.com/s/w1wyrg06e37huyosmni2 (https://www.box.com/s/w1wyrg06e37huyosmni2)
-
Tagged. Squee! New fanbooks!
-
Shame it doesn't work with Open Office...
-
Anyone know of a program that will easily, and accurately convert to an Open Office format?
-
For those trying to open it in Open Office, have you tried opening it with the free Microsoft Excel (http://www.microsoft.com/en-us/download/details.aspx?id=10) viewer?
-
For those trying to open it in Open Office, have you tried opening it with the free Microsoft Excel (http://www.microsoft.com/en-us/download/details.aspx?id=10) viewer?
Thats read-only. Meaning... can't fill in the things needed to generate the stats...