External Ballistics Calculator
A Ballistic Spreadsheet
Using BallisticSS – a ballistic spreadsheet (notes by J. S. Andrew Dec. 2009)
This public domain (free) spreadsheet file is made available for download by Owen Guns. The Ballistics Calculator will give a lot of answers to questions by shooters who have a computer with either Excel or OpenOffice.org installed. To obtain a copy of the FREE Ballistics Calculator, please send an email to owenguns@spiderweb.com.au with the subject line “Ballistics Calculator”.
DESCRIPTION
By entering values for any practical combination of MV (or velocity at a nominated range), Ballistic Coefficient, Bullet Weight, Sight Height (scope axis above bore axis), a desired Zero Range, and the actual target ranges you are interested in, the spreadsheet will instantaneously display ballistic predictions at those ranges. Remaining velocity and kinetic energy, drop from line of departure, bullet path above or below line of sight, and the bullets time of flight are given in metric and Imperial units. If you also care to nominate a crosswind speed in metres per second, deflection at each nominated range will be given, in metres, inches, and M.o.A, along with the wind strength equivalent in Km/h, MPH, or Knots.
Inputs for range can be entered in metres, yards, or both and output can be selected to display either or both in combination with the predictions for those ranges, also in the desired measurement units, for printing.
A lot of questions can be answered by working the process backwards. For instance published ballistics for factory ammo are based on a MV that is often higher than shooters find when they test the ammo in a shorter barrel over a chronograph. In this case the implied BC of the factory projectile can be found by getting the spreadsheet (usually in the “G1” drag model worksheet) to mimic the published ballistics. This is done by nominating the same MV and increasing or decreasing the BC value as required to obtain the same remaining velocity at the furthest range given. If, as likely, the manufacturer also used the G1 drag model, the remaining velocity at intermediate ranges should match also. With the BC found this way you then simply nominate the actual chronographed velocity and the range of the midpoint of the screens (the Range X input), and you’ll get a set of more realistic downrange predictions at any of the ranges that you want.
Other calculations such as finding how hard a particular bullet has to be driven to deliver a desired amount of kinetic energy at a certain range can also be worked in reverse by trial fitting the MV. Better still, if you know the velocity that gives the desired KE for your bullet weight at the required range, simply input that range as “Range X” (Cell B6) and input the remaining velocity needed in Cell B5.
It is often interesting to find where the other Zero Range is. The usually nominated Zero Range, at 200 metres for instance, is where the bullet falls back across the line of sight after which it hits ever lower as range increases. But where does the bullet first cross the line of sight as it climbs from the muzzle, which is, for practical purposes, at the “sight height” distance below? By trial and error on one of the range input cells you’ll soon find this other point where the bullet crosses the line of sight and the bullet path value comes up “0.0”, the short range zero.
COMPARISONS
Currently there are numerous ballistic programs available for PCs but the better ones cost money and may offer little advantage in practical terms over this free file (BallisticSS.xls) that adapts common spreadsheet software to answer the same questions. BallisticSS actually has some advantages, as already mentioned, with its’ versatility of range input and the usual spreadsheet facility that allows output to be shuffled in different combinations of columns containing just the useful info for the job at hand. For instance you could show just Bullet Path data in the desired units for uncluttered use in the field. Or you might include a “Remaining Energy” column if theoretical comparisons are to be made about target damage. Being able to nominate any particular shooting distance (range) is also likely to be an advantage over some bought programs that are restricted to even increments of range.
ORIGIN
Hatcher’s Notebook, first published in 1947 describes how drag functions/models work, It also describes how to construct a table of space (range) and time values from them corresponding to decrements in velocity. These decrements are caused by the atmospheric drag acting on the standard projectile at a standard atmospheric pressure, temperature, and humidity. Applying a particular bullets’ BC as a factor to space and time intervals for the standard projectile (which has a BC of 1.0 ) allows predictions to be made for the bullet of known BC.
There are sources, on the internet, for retardation functions for all the commonly known drag models including the one for Krupp’s 1881 standard projectile, the basis of Mayevski’s work that was then adapted to Imperial units by Ingalls for his table published in Hatcher’s Notebook.
The third edition, second printing of Hatcher’s Notebook, lists retardation functions (in log form) on page 559 and the method of finding drag at a particular velocity, and thereby the distance and time increments as velocity decreases on pages 565 to 567. With the power of spreadsheet software it was easy to make a three column table for velocity in 1 fps decrements with corresponding distance and time increment, (3600 rows in the case of Ingalls’) in a matter of minutes. A whole new ballgame from the days when Julian S. Hatcher’s book was first published in 1947….After creating such a table on a spreadsheet there remains the task of extracting and processing data from such long columns. This turns out to be a breeze when Excel’s LOOKUP function is unleashed.
As well as the Krupp-Mayevski-Ingalls version Hatcher’s book also gives the retardation functions for the British 1909 model so tables for this can also be easily replicated on a spreadsheet in the same quick and easy manner. Hatcher mentions the origin of the now widely used G1 model but does not list it’s retardation functions. These are available by Google search which leads to www.snipercountry.com/ballistics/index.html. One advantage of the G1 model is that it covers velocities above 4230 fps. Just how far above is uncertain but BallisticSS applies the top retardation function to over 5000 fps with the idea that at least it would provide some approximations at that extreme level. Another advantage is that the G1 drag model is now by far the most popular one in the sporting arms industry.
Things have certainly moved on since the pre-computer days. The same principles apply but the application is so much easier that there are no longer any time or effort impediments to following your curiosity about practical ballistic questions using modern ballistic software, which includes this spreadsheet.
MORE DESCRIPTION
Presently the spreadsheet will calculate results by the G1, Ingalls, and British 1909 models, each on its’ own separate worksheet. All three are similar, probably having a standard projectile of the same sectional density, but are apparently different enough in form factor (or accuracy of original test data) to require slightly different Ballistic Coefficients to be determined for a particular bullet. A fourth worksheet called “G1 Sierra” is included. It is the same as G1 except there is provision for inputting various values for BC in different brackets of the velocity spectrum in the manner advocated by the bullet maker Sierra. Based on their own tests they publish these varying BC values (on the www) for the bullets they make. So why not use them if it will improve accuracy in the calculated data?
Using the current (Dec.’09) G1 Sierra worksheet requires entry of the various BC values in the order in which they occur during the bullets flight with a corresponding entry of the velocity at which each BC value first applies. Not difficult at all, and certainly much easier than dragging the values down a column with thousands of rows required in the previous version (Sept. ’09) which at least worked unlike the first version that had a fault causing errors.
Lots of other commercially available bullets have their BC stated by their manufacturer in either the Ingalls’ or G1 model, mostly G1 nowadays. If a bullet’s BC in one drag model is known it is simple to arrive at a figure in another by trial guessing it till you get the same velocity drop over the same distance (Range). Likewise a BC can be found from manufacturers published ballistics for factory loaded cartridges. This will enable the calculation of better info if your rifle or pistol produces a different MV than the one published. This will usually be the case, especially if your gun has a different barrel length than the factory test gun.
Regardless of which drag model is used to find remaining velocities (and time of flight), the subsequent calculations for Kinetic Energy, Drop, Bullet Path, and Cross Wind Deflection are all done with the same formulas that are normally used to produce published ballistics. In addition to the worksheets for each of the drag models mentioned there is a supplementary worksheet that provides an easy way to calculate an unknown BC from a known SD and shrewdly estimated Form Factor, or to use a known BC to calculate a Form Factor etc.. there is also provision for calculating a change in the value of a BC due to temperature and or barometric pressure varying significantly from standard conditions.
Note:-
There was a fundamental error in the “G1 Sierra” worksheet on the first version of BallisticSS circulating since early in 2009. This was fixed in the Sept. ’09 version and the current improved Dec. ’09 version that should be used instead. The early faulty ones can be identified by checking cells C27 and D27 which should correctly show “T Increment” and “S Increment” and not “T Sum” and “S Sum m” which are only correct for the worksheets other than “G1 Sierra”. Get a later copy if those two cells show the same names in the “G1 Sierra” worksheet as they do in the others.
The post Sept.’09 versions also have provision (in the supplementary calcs. Worksheet) for finding the BC of spherical projectiles of various diameters and weights calculated according to the density (SG) of lead, iron, etc.. According to the Coxe-Bugless chart the form factor (i), and consequently the BC, is different depending on whether a spherical projectile is traveling in the supersonic, transonic, or subsonic zone. So here’s another use for the G1 Sierra worksheet if your projectile will be dropping into a lower speed zone to reach a range where you want answers.
The file BallisticSS.xls runs in the popular Microsoft Excel. If you don’t have that software check out OpenOffice.org which is free and includes spreadsheet software that will also handle the .xls format.
As supplied the area to the left of column “S” in the various drag model worksheets is “protected” with only the red numbers and text changeable as user inputs. This avoids accidentally disrupting cells with formulas or fixed data. “Protected” status can be removed without a password via the “Tools” menu if constructive changes are needed. Save an original version somewhere in case your modifications don’t turn out well.
OPERATING TIPS
To save space on your computer you should save just one or two copies of the BallisticSS file as it is supplied and rename working copies of it which can then be greatly reduced in size by deleting the worksheets that aren’t needed. As the “Ingalls” and “British 1909” would rarely be used and only one of the other two (G1 and G1 Sierra) would normally be used for a particular purpose an almost 75% reduction in file size can be achieved in your renamed working version.
Where you want to easily compare two ballistic scenarios, you can create a duplicate worksheet by inserting a new blank worksheet perhaps called “G1a”, then do a “select all” and “copy” with the “G1” worksheet open, then open the blank “G1a” worksheet and “paste” at the top left cell. Then by using each worksheet for one or the other case you are comparing you can flick back and forth between the two worksheets to see what performance differences you’ll get.
If you want to save numerous calculation results by renaming the working file to “Load Z for rifle Y” for instance, you could also start to use a lot computer memory to store a relatively small data table. The remedy to this is to format the data in the desired combination in cells to the right of column “S” then copy just the required output data to a storage file by using Excel’s “Special paste” function. This way you won’t be keeping an unneeded copy of the relatively huge tables used to calculate each and every final result that you want to store.
END NOTES
There is some uncertainty on the subject of the standard projectile that the G1 drag model is meant to describe. Hatcher’s Notebook seems to indicate Ingalls and British 1909 tables are for a one inch diameter flat based projectile weighing one pound with a two diameter radius ogive and no mention of a meplat (flat spot) on the nose. This may or may not also be the definition for standard G1 projectile.
Whether the Coxe-Bugless chart shown in Hatcher’s Notebook (for the purpose of estimating form factor) is meant to be used with all three models or just the Ingalls’ Tables is also unclear. Either way it adds further to the confusion by indicating that a two calibre radius ogive without a meplat (the most likely recipe for the standard projectile) has a form factor of 0.85 instead of the expected 1.0 to accompany a weight of 1.0 pounds and a diameter of 1.0 inches which is needed to arrive at a BC of 1.0, as expected for the standard projectile. In the face of this I’ve taken the approach of just doing the best I can with available information and am forced to be content to be able to mimic numerous published ballistic tables by using the same input values. With a chronographed value for velocity near the muzzle and the best available value(s) for BC in the drag model used (G1, Ingalls’ etc.), I am confident that the spreadsheet will give useful predictions for times of flight up to half a second, adequate to cover any sensible hunting range for any class of cartridge.
To extend the useful accuracy of calculations to abnormally long range, shooters (using bullets other than Sierra’s) might need start collecting ‘time of flight” data additional to a simultaneous short gap velocity measurement) with widely spaced chronograph screens (say 50 m apart). These measured times of flight (with simultaneous observations for temperature pressure and humidity) would indicate true BC values at various brackets of velocity in the method pioneered by Sierra in producing BC data for their bullets. This approach virtually removes any doubts on the appropriateness of the drag model (you’d probably use G1) by bending it to the shape you want by using different BC values through various velocity zones as required.
End
2844 total views, 1 today
