Real life small arms

[Milano]

I just wanted to share a Excel file that I have been working on for a few years and several evolutions. I use it to create small arms for my own use but thought that the community might enjoy playing with it a bit. It uses the Fire Fusion and Steel template but I have switched some values to represent real life weapons and the variations.

The first tab is the instructions for how to use this. (They are included at the end of this as well.) Please ask if anything doesn't make sense or you need a guiding hand. I am wanting the community to help me in also.
Essentially what this does it that it expands upon the data using real values. For example:

Damage and Penetration
1) Damage at range is a measure of the specific bullet's Ballistic Coefficient and initial velocity.
2) Penetration is representative of the bullet's Sectional Density and velocity, not sheer power.
3) Bullet selection matters in rifles especially. For example:

The 7.62 M80 round vs M118LR round can be reviewed and compared in game terms.
With an M24 SWS in 7.62x51 using M80 @ 853 m/s vs M118LR @ 792 m/s
The range of the M80 is 85 while the range for the M118LR is 87.
The damage variations: left is M80, right is M118LR
0-150: 4 0-300: 4
150-500: 3 300-700: 3
500-1000: 2 700-1200: 2
1000-1500: 1 1200-1500:1

Penetration is as such, again left and right correspond
0-300: 1 0-400: 1
300 -800: 2 400-1000: 2
Penetration of Nil beyond these ranges.

Does this mean that the M80 is an inferior round? No. For Machineguns it is sufficient, in Sniper Rifles the "Lethality Range" is increased not necessarily the range of the weapon. The cost, or rarity of such rounds, should be decided upon by the referee.
Penetration is obviously is not perfect but once using the tables it is obvious, in game terms, when "NIL" happens. It is my Excel Fu that stinks not, the theory or data. (Although, maybe the formula is bad.. al la 50 BMG.)

Rate of Fire
What is the difference between the L85 IW and FAMAS? Both are bullpups, both fire 5.56? However the IW fires at 750 rpm and the FAMAS at 1,100 rpm. I figure that with an assault rifle a trained soldier will fire a half second burst usually. So, I changed the rates of fire to be truly representative of a type of weapon and how you would use them given their class. The IW has an ROF of 6 and the FAMAS a ROF of 9. Rates of fire are divided as below and can be changed via "Misc. Stuff." J2-J10.
Assault Rifle and Submachine Gun – half second burst
Automatic Rifle and Machine Gun – 0.6 second burst
Battle Rifle and Machine Pistol – 0.4 second burst

Weapon Classes and Action Types
While I was broad brushing most other things I picked on Range Modifiers for action types and weapon classes. (I thought of subdividing by those weapons that fire from an open bolt vs closed bolt! But, haven't felt that ambitious, yet.)

As I think of these things 1 equates to normal, very accurate and efficient action types. So, a pump action is not as effective as a bolt action. And a Bolt is not as efficient as a single shot. This table is found in column B42-B47:
Bolt-action – 1.05
Lever – 1
Pump – 0.95
Revolver – 1.1
Self-loading – 1
Single shot – 1.1

With weapon types I thought of it as a vague cross between open vs closed bolt, manufacture quality, and control ability. Thus a machine gun compared to a civilian hunting rifle has more moving parts, open bolt (in general), and mass produced. This is found in column K2-K10:
Assault Rifle – 1
Automatic Rifle – 0.9
Battle Rifle – 0.9
Civilian Rifle – 1
Machine Gun – 0.85
Machine Pistol – 0.9
Sidearm – 1
Sniper Rifle – 1.05
Sub MG – 1

Oh, and while picking on things Bullpups are at a disadvantage to standard weapons. (It is at about 90% range vs standard. "Misc. Stuff F9.) This is NOT because bullpups are less accurate but the open sight radius is much less, therefore cannot be shot as accurately. Remember, that every military bullpup I can think of has a low power optical sight included by most militaries.

Please use, distribute, and play with the program as you wish, however, there is a caveat!
Some data is a guess and it is reflected in the "Ammunition Table" tab. Specifically column F. If you have access to BCs or someone who has a better guess please, pretty please post it here in this topic and I will change it.

In addition anything else weird or strange please let me know. I have not used this for every small arm known to man, yet. Inform me of any problems please.

Instructions for use, (Copied from Instructions tab):

To use this small arms weapon creator type in the Green boxes on the Weapon Data sheet. Drop down tables are marked. The only trick is that you need to select the description of the bullet in E2 AND the bullet weight in E3.

There are a lot of bullets to choose from. I chose them as a generally good bullet. The selections may not the best of all brands, but they are not the worst either. Often times hard data is not available for which type of bullet the weapon was chronographed with. ie was the chronograph data with the M193 or M885? All I can say is have fun or don't.. Your call.

Yes, I also know that using G1 ballistics for many, many bullets is not appropriate. With that being said it is representative, not factual. If you are a sniper off to the Sand Box please, please do not use this program for your dope.

Green Boxes are OK to type in (and really must be). Typing in Red Boxes cause severe pain, anguish, and possibly a rash.

On the sheet 'Weapon Data' the cell Q2 is used to increase the lethality of the Damage Results.

In addition if you look at the sheet 'Ammunition Table' the caliber (in inches) may vary slightly from real life. This is so that I could categorize them easily. The metric bullet diameter is correct, which is what the formulas use.

Penetration, I changed from the original also. It is calculated from the Sectional Density of the bullet and the Velocity and it works fairly well. However, my Excel skill are slipping and I cannot portray it well in a table. On the Weapon tab the Damage is on row 5 and the penetration is on row 7. Again, I used real life data to create a generalized formula.

Shotguns I am working on but there is the data to show what Buckshot does in terms of this program. I should say A SINGLE PIECE of buckshot. This could be used for musket balls as well and general formula is in cell 'Ammunition Table' L240.

Straight walled cartridges I have had to change to those designed for a Rifle vs. Pistol. The problem is that a fair amount bridge that gap and just need to be changed from Straight Rifle to Straight in column P on the Ammunition Table tab. These are primarily with older 19th century cartridges like the 44-40 and the 38-40. However... it also includes the 22 rimfires!! Work in progress maybe.

I cannot nor will not claim credit for all of the data and work in this program. As far as I know I am not infringing upon copyrights. This is made for use and sharing amongst the Twilight 2000 Roll playing community.