[cpttango30]

Ok so you have just purchased a Chronograph. Yah ok it read off the velocity to you for every shoot (Most of the time). What do you do with all that data? Sure you can buy expensive ballistics programs but if you have Microsoft Excel you can make your own with a little time that is cheap and easy to use. I will walk you through building a usable ballistics table that you can use for any of your reloading efforts. It may not show you drop and wind deflections but there are other ways of doing that like online calculators.
1. Set up your data fields.
a. I like to use the following
i. Date
ii. Weather conditions
iii. Bullet weight
iv. Powder name
v. Powder charge
vi. Primer used
vii. Case used
viii. Shot # 1-10
ix. Velocity
x. Deviations
xi. Standard Deviation
xii. Extreme Spread
xiii. High velocity
xiv. Low velocity
xv. Average Velocity
I like to set them up to look like the ones below. You can color code them however you like.


2. Now that you have that all set up we need to start inputting all your formulas. These have to be right so take your time and make it right I messed up once and didn’t know it I was getting some very crazy numbers like a 5000fps SD and extreme spread of 5 fps.
a. First you are going to set your average velocity formula.

i. Click in the box right below the average velocity header and enter the following into the F(x) (Function bar) Now the D8 and M8 are your cell numbers check and make sure that your cell reference is input correctly.


3. Now we are going to set the deviations. In the cell next to the Header Deviation click to select the cell. Now Input the following formula in to the cell =D8-$N$8 once again make sure your cell references are correct.

[cpttango30]

a. Now we see that the second number has $ in it. This notation makes it a static cell. Meaning that as you drag your formula from cell D9 to M9 it will always take cell M8 as the second number. Now instead of typing all this in for each of the ten cells we are going to drag the formula to all the cells for our 10 shots. This is very easy as you just need to click in cell D9 and then move your courser to the bottom right corner of the cell until you see the big + (Plus sign). Now click and hold and drag it to cell M9 and let it go. This should place the formula in to each cell. It should look like below. See how the cells are all highlighted. I like to click in each cell and look at the function bar to make sure the formula is correct.


4. Now we need to make some calculations that you don’t need to really see. I normally just set the font color to white after I get the formula input. This is an easy one. Click in the cell below Shot #1 Deviations We have to square the number divided by 2 the formula will look like this =POWER(D9,2). Now after you do that, just take and perform the same drag operations as in part 3a above.

[cpttango30]

5. Now we need to add the last row all together. That is done with this formula =SUM(D10:M10).


6. Now we are going to set up our standard Deviation cell. This will tell you and average of the amount of deviation from the mean of the shot string. This is done with the following formula =SQRT(N10/9). This formula needs to be in the cell next to the header Standard Deviation.

[cpttango30]

7. Ok before we do the extreme spread cell we need to find the minimum and maximum velocity we are going to do this and also we are going to highlight each in the shot string. This is two separate operations.
a. First you need to click in your cell below the Max Velocity. We are going to use this formula to find the max value for shot string by entering =MAX(D8:M8)


b. Now you want to highlight the min and max values in the shot string. Easy to do with Conditional formatting.
i. Ok highlight the row with the velocities in it. Now go to conditional formatting and choose highlight cells equal to then I make mine highlight the cell RED and make the font WHITE. Then make it equal to the Max cell above buy clicking in the max value cell. Then do the same for the min cell. I set it like above green cell fill and white font. Once you do this and you enter your velocities the sheet will perform all the calculations for you and spit out the answers and highlight your min and max velocities.

[cpttango30]

To set the Extreme Spread cell you need to highlight the cell next to the header then input the following formula =F13-G13. All this is doing is subtracting the min from the max velocity.



Now we are done and all you need now is to get out and shoot some bullets over your chrony and then come home and input the velocities and see what comes up. It should look like below when you are all done.

[Dillinger]

That's a really cool idea cpt! I like that enough to add that to my weekend projects list.

Really cool. Thank you for sharing.

JD