Years ago I made a spreadsheet to do the weapons for Twilight 2000. Being a sadist I went ahead and started doing the recipes for the other rpgs that I enjoy, and am still working on it as you can see.
I only recently bought GURPS 4e and am not very comfortable with the rules yet. However, I thought that this excel file may be of some value to any designs that you all may want.
The program is to realistically use Ballistic Coefficients, Sectional Density, and Cartridge data to portray small arms that are currently in existence. TL5-9 (I think. See above.) I reload ALOT. I shoot competitively ALOT. I do not use Excel a lot so please excuse some errors.
The realism parts are on tabs called Ballistics Table and Ammunition Table.
From the real external ballistics look at columns D-N and U - AA on the Ballistics Table tab. These are not guesses or gamey. These are formulas that I got out of Brian Lytz's book about ballistics (I think).
The Ammunition Tab has 210 different Cartridges that are typically used in hand held small arms. (The RT-20 and it's 20mm cannon rounds are debateable but you get the point... 210.) Like I say I reload for many different calibers and I have access to a whole bunch of information. The Cartridge in colums M, N, and O are real. Just because the 45-100 Sharps was also known as the 45 2 6/10" I did not assume. I looked it up from Mike Venturinos book, Shooting Buffalo Rifles of the Old West.
On the same tab is the table A:3 to H: 248 these are the individual bullets that are used for the cartridges. These are all real bullets by manufactures (Barnes, Sierra, etc) or taken from verified sources for military bullets (for example the M903 50 BMG SLAP round). Take note that for example there are 12 different weight 0.224 bullets for 22-250s, 220 Swift, 5.56x45, etc. In column F are the Ballistic Coefficients. They are the G1 BCs, the G7 work well for BTHP bullets but not so well for all others LRNFP, JHC, etc. The G1s are my allowance for consistency. Note that I did have to make some wild and educated guesses. These BCs are color coded so that IF someone figures out the BC of the Chienese DAP92 5.8x21 pistol round they can correct my guess. Column G is the calculated Section Density. Column H has notes on basic velocity and published penetration data for comparison.
The tab Weapon Data is where you input you firearm and cartridge data. Column B is to be filled in with the weapon data. Barrel length, sight radius, ROF, etc. Note on Rate of Fire, there are two ROF inputs one is to be used for burst weapons like MP5a2. They fire in both a burst, 2 or 3, and Full Cyclic. You will have to adjust the burst ROF to get the desired out comes. For example in TW2000 I divide the Real ROF by about 120. This is a half second burst for Assault rifles and by 100 for MGs. The rationale being that MGs are usually fired for another round or two longer than a center mass point target like from an M4. This can be changed on tab Twilight 2000 J:2-J:9. Cells B:8-B:11 are tabs. Select what is best representative of the weapon you are statting out. ie Assault Rifle, Sub Gun, Standard, Bullpup, etc.
D:1-E:7 is the cartridge data. Most of it is selectable tabs. DO NOT write in D:9-E:14, all this data is collected as you fill out the green boxes above. Note that in D:16 are usually notes on real life published data for velocity, penetration, etc. For example the M193 5.56x45 round has variable velocities of 975-990 m/s. Because of many variables I would take an educated guess or use data you find. My specific HBAR sends them at about 3225 f/s. Note that cells E:5-F:6 there is a handy conversion. Type in 3225 in cell E:6 and whaalah in cell F:5 is the m/s conversion of 982.98 m/s.
Cells A:24-J:27 are some common diagnostic formulas for bullet impacts at ranges of 5, 100, 300, 600, and 900 meters. Note: I started this again for Twilight 2000 using Fire Fusion and Steel. Their fakeary formula is in A:31. OGW= Optimum game weight, I used large animal formula.
Finally the Weapon tab is where all the data is collated and put into more readable form. Thus far is the data for TW2000 and Phoenix Command.
I wish I have had the time to offer a finished product, but I don't. With 2 kids 2 full time jobs and a working wife it's not looking good before 5e is released. So, I offer it as it is. Hopefully after reading this thread about 1,122 times I have a handle on what formulas I have and need to start statting GURPS small arms.
What I would ask are a few things:
Please let me know of any mistakes in the tables. I have not finished what I wanted yet. (Take note of Weapon L:12, I need to fix this. This is the range that a supersonic bullet breaks the sound barrier and becomes way less stable and accurate.)
Also as people comeup with formulas for ST, Range, etc please update this tread maybe? It would be nice to have a consensus of formulas in one place. Or did I completely miss it?
Any help to where formulas can be found would be GREATLY appreciated and it would help me to add GURPS to this one and re post it.
Any questions, please ask me. I would like to be more thorough but.. I am "wordy" as it is. I would like to have the community use every tool at their disposal, well. So ask me, please.
Also no money can be made by using this spreadsheet by anyone. I offer it as is and trust that it is for personal use. I also accept no liability. If you're going over there and use this as your DOPE well... for my conscience please don't. I can help you with that and not by using this spreadsheet.
https://www.dropbox.com/sh/70hj7hoe2...CCv7S0vxa?dl=0