Ballistic’s Spreadsheet – July 2013
People on the SJG Forums still reference the work I did a while ago on bullet statistics conversion from real-world data. The fact is, the equations for weapon penetration did not change form 3e to 4e, and where there are some real issues with scaling weapons, the derivation of penetration is the same, and (more or less) the wound channel (damage size, bullet type) modifiers are the same.
So it holds up well.
That being said, I wrote it in 2002, meaning it’s been kicking around now for more than ten years.
Yeah, I feel old.
I’ve tweaked it here and there, and added more cartridges where I needed them. Some sample results follow.
But for those who want the most recent version of the sheet I use myself, well: Here it is.
What rounds does it contain? Here’s a list, with some stats.
you mentioned it has an issue with weapons scaling; I tried using the Energy to Diameter formula you have on UT weapons and I get very high damage results.
Like the Assault Cannon 25mm in UT, is 10d but in your formula 18d? or did I just did something wrong? I'm using 1/2 range limit for velocity.
Yeah, the 1/2D range is definitely not velocity! Let's see if we can reproduce something like it. We'll start by copying cells BN3 through BN15 – the stat block for the 14.5mm KPV in Cartridge Stats – and pasting them into the Plugin page from D12 to D24.
Then, let's fix stuff. Leave chamber pressure alone for now. Barrel bore is 25mm, as is projectile caliber (D14 and 21). Let's assume a 3.5:1 aspect ratio – actually, the KPV is already 3.6, so leave it there.
Let's leave case length where it is, but assume it's caseless and make the chamber bore ALSO 25mm. Let's assume an assault-rifle length barrel of about 500mm. That makes the density (cell I22) only 2, where a solid bullet of jacketed lead is about 9.5, and jacketed mild steel is probably closer to 9.
Let's set "bullet mass," cell D18, to a value that gives a density of 9. We use Goal Seek to do that, and get about 283 grams (4375 grains)!
OK, so that still gives us a lot of damage, so we lower burn length (D20) to 25, and again use goal seek on Chamber Pressure (D13) to set the armor penetration (E30)to 35 points, or the value of 10d listed in the book. We get 22,250psi.
So, what we have here is a low pressure cartridge designed to leave the barrel at pistol velocities (we get 311 m/s, or 1020 fps, with these stats). It's a huge, heavy bullet that's reasonably high sectional density, so it retains that velocity for over a mile (!), with a max range of 6460 yds (!!).
This is a payload rifle round, not a velocity-killer.
As you might gather, you need some specialized knowledge and to know how to fiddle with the sheet. I suggest reading the old 2002 article – it's free on Pyramid and linked on the blog site – as a sort of "user manual."
Oh man, I've been waiting for this. First Luke gave us a kick awesome update on energy weapons and now you've updated your ballistic sheet, this is some good times to be a crunch gamer.
I will promise this: If David and Hans ever wind up publishing their weapons design formulae, I will rewrite the sheet to be consistent with it. It would mainly be in the conversion of energy and caliber to damage, and possibly implementing a slightly different way of thinking about bullet size modifier. But if that day comes, I'll update.
Wow, we are right; not much has changed from the original. Must of just been my foggy memory making more out what you said on that age of nelson thread.
Oh, one thing I've been wondering about is for your max range formula you have a constant called "A" in cell A4 that's just a zero in B4. It's suppose to modify the aspect ratio by aspect ratio^(Cell B4)but since it's zero it just always returns a one so I'm wondering what's it there for. Just a remnant from an older formula or am I missing something here?
As to the official formula, I got my hopes it will be in the Vehicle Design System but till then I'll have fun trying to crack it. I thought I figured it out a while back when I came across a Kromm post talking about a factor based on Density^1/3/SQRT(HP modifier*2)but I couldn't find a way to make it work heh.
The charts are impressive, and the data it gives are fairly close. I worked around were the base information is all on a single line. Took some work, but now i have a spreadsheet that I can flow down the number with. It could be used to create a dropdown menu on another page with lookup to pull the data.
I am curious, though. Where did you find the information on the Chamber Bore size, Aspect Ratio, Burn Length, and Expansion Ratio? Everything else is fairly easy to find.
Two things: one of the sheets should allow the drop-down feature. It does in my home version, but perhaps I removed that for compatibility reasons. I’ve got multiple versions kickin’ around.
To your question:
Chamber Bore size is taken from the diameter of the cartridge at the shoulder for a rifle, or just about anywhere on a straight-wall cartridge like a pistol. You can usually find cartridge drawings on Wiki or other sites.
Aspect ratio is a bit trickier. If you can find the dimensions of an actual bullet you can use that – it’s just the length/diameter ratio. Failing that, rifles TEND to be about 4-5, and pistol cartridges tend to be 1.5 (.45 ACP) to a bit more than 2 (9mm is about 2). You can also use Goal Seek to set a reasonable density once you have the caliber, bullet mass. Pure lead will be 11.0 to 11.3 (inaccuracies due to shaping and approximations in my formula). Fat, heavy bullets that are jacketed lead will be 10.3 to 10.8. Rifle bullets, with thicker jackets and low aspect ratio, may be 9.5 to 10.3. Solid brass or copper bullets will be 8.5 to 9.3. There’s a bit of an art to it. 🙂
Expansion ratio can usually be looked up on whatever website you’re getting your information about hollow-points from. Lacking that, take a guess. Older JHP bullets for pistols tended to 1.3 to 1.5 or so. Modern ones can be 1.5 to as much as 1.8. Jacketed soft points (rifle bullets) will squish down and expand to a ratio of 1.8 to 2.2.
Finally, burn distance is basically a fitting parameter. Find the pressure and barrel length and whatever, and then use Goal Seek to set the burn distance to a value where the test velocity you’re trying to match out of the barrel that matches the configuration (“this bullet got 2756 fps out of a 24″ test barrel”).