Quote:
Originally Posted by hal
I was able to download it as an XLSX file without a problem. The next step for me is to try and figure out what the formulas you are using are, what the numbers are used in the formula, and maybe recreate it sufficiently enough that I can write a VBA code or VB.NET code to do the same thing.
|
My spreadsheet is a straightforward application of the drag formula and freshmen physics and introductory calculus. I can break it down for you.
Calculate the static portions of the drag equation as -0.5*1.23*(object coeffecient of drag, roughly 0.47/object aspect ratio)*cross sectional area/mass. 1.23 is the density of air, more or less. Calculate subsonic drag static portion as 1.4* the previous value.
For each row, calculate instant velocity as the square root of the sum of the squares of the previous row's horizontal and vertical velocity, and current angle of the inverse tangent of vertical velocity/horizontal velocity. Calculate instant horizontal and vertical acceleration as the square of instant velocity times static portions of the drag equation (using the subsonic number if instant velocity is below the speed of sound) times the cosine or sine of current angle, and add the static acceleration due to gravity for the vertical accel. Instant horizontal and vertical velocity are the previous horizontal or vertical velocity plus instant horizontal or vertical acceleration times delta-T, and total distance traveled in the horizontal and vertical are the sums of horizontal and vertical velocity times delta-T.
I calculate 1/2d range as the horizontal distance when instant velocity has dropped below muzzle velocity * square root (1/2), since that velocity gives you half damage in Doug's formula.
With a little approximation of the co-effficient of drag, the current spreadsheet gets the maximum range for the RIA M2A1 105mm howitzer within 10 yards, and the maximum height and range of a 5"/38 cannon within 2%, so I'm pretty satisfied with it. I just need to speed it up a bit.