MERC - Maximum Effective Range Calculator

[speedengineer]

I recently created a Microsoft Excel based tool that calculates your probability of hitting a target. It allows study of the effects of rifle and environmental variables and uncertainties on hit percentage.
Read below, and check out the screenshot to see what it’s all about. I am sharing this with the community for free.


Note - you will need Microsoft Excel on a computer in order to run this tool. Versions of Excel for your phone will not work, as they don’t support Excel Macros.

MERC Download Link


Introduction to MERC - Maximum Effective Range Calculator
In a perfect world, the bullets would always leave your barrel in the direction you intended with identical velocities, and there would be no uncertainty in your wind or range estimation - you would hit your target's center with every shot. Unfortunately, we have to contend with variation and uncertainty in the real world.

This calculator lets the user specify information about their rifle and the shooting conditions, including the uncertainty in each input. The calculator applies an amount of random variation based on those specified uncertainties, and determines where the bullet would have hit. It then repeats this process for many hundreds of shots, and calculates a hit probability based on the number of hits and misses. It also plots the hit locations for the first 300 of these shots on a graph, to visually show the distribution of their impacts.


What can I use MERC for?
MERC can be used to study many shooting situations. Here are some examples to get you started:

• Comparison of one cartridge/rifle/bullet to another. For a given set of conditions, which has better hit%, etc.
• Identify the longest range you would be comfortable hunting at, given a specific set of conditions and uncertainties.
• Estimating what hit rates to expect for a yardage that's farther than you've shot before.
• Comprehend how errors in your scope zero affect hit probability.
• How much does "x" variable matter. For example, does a 0.5 MOA rifle help my hit% for my shooting scenario? How about StdDev in MV?
• How accurately do you need to estimate range and wind in order to have consistent hits?
• For a desired hit percentage, how much extra range does using a higher BC bullet provide?
• Understanding how wind angle uncertainty can cause groups that aren't centered around your point of aim.

Special thanks to Scott B. (entoptics on LRH), who convinced me that it would be worthwhile to release this tool to the shooting community. Together, we jazzed up the user interface, design/layout, instructions, and many other details. Hopefully you find it useful!

 

[Salmon River Solutions]

Cool man! I saw this on the LRH forum too! Hope people on here can get some use out of it too! Looks like you spent a lot of time on it.

 

[entoptics]

Cool man! I saw this on the LRH forum too! Hope people on here can get some use out of it too! Looks like you spent a lot of time on it.

Heh. Yeah SE put some sweat into this...

Let us know how you like it (or have trouble), please post any fun comparisons you come up with, and tell us how it compares to your real world experiences.

 

[speedengineer]

Updated the Download link for MERC.

 

[willfrye027]

This is an awesome tool, you can really answer a lot of questions/what if’s with it.

Super easy to use

 

[Shraggs]

Will it work with Apple numbers? All I have currently

 

[entoptics]

Will it work with Apple numbers? All I have currently

Unfortunately not. I don't believe anything but Excel will allow the macros to function.

 

[Shraggs]

Darn, really would like a copy. Imagine all the iPhones running numbers and field use possibilities!

 

[Q child]

Holy smokes! Thank you.

 

[speedengineer]

Here is an example of one use for MERC. How much does wind angle uncertainty matter when you are shooting with a headwind or tailwind?

Situation: Shooting a 6.5 PRC with 143 ELDX into a 10mph headwind.
The shooter has to estimate the direction of the wind. There will always be some error in your wind direction call. Plus, wind is rarely steady and the direction is constantly changing. How does this uncertainty in wind angle affect the horizontal spread of your impacts?

Case A (Red): 10mph straight headwind with 5 degrees wind angle uncertainty
Case B (Blue): 10mph straight headwind with 25 degrees wind angle uncertainty

This simple example suggests that your estimation of the wind direction can be just as important as your estimation of the wind speed!


Hit dist plot at 600 yards

 

[entoptics]

Figured I'd throw out another example of just how powerful this tool is.

For perspective, I've put a lot powder down the tubes of various rifles, and in retrospect, I wish I'd had this puppy a decade ago to help me hone in on my ultimate goal. Hitting what I'm aiming at. I wasted a lot of time, money, and barrel in various experiments that probably could have been far better spent by learning what variables I could control, and what variables I had to learn to deal with.

So...A quick comparison of 30-06 and 7mm Rem Mag. All uncertainties are the same, only the bullet and velocity have changed.

Shooter/Rifle = 0.5 MOA (I've never owned or been such a thing, but one can dream right?)
Muzzle V standard deviation = 10 fps (good quality handloads)
Wind = 10 mph from the right.
Wind uncertainty = 30% (± 3 mph with a 10 mph nominal wind, good mirage and a kestrel?)
Wind direction uncertainty = 45° (shooting in some variable terrain across a canyon for instance)
Range uncertainty = 5 yards (got a pretty good rangefinder reading, but not perfect)

This is what you'll see after you've run your two simulations. Red is 7mm Mag and blue is 30-06 in all the graphs and table headings.



To zoom in on the information at hand.

1) Note how the groups are not centered around the bullseye (500 yds). This is due to variability in the wind angle and speed. We'll hopefully address this cool insight in another post. The 30-06 with it's slower, lower BC bullet is affected more than the 7mm RM obviously. At 500 yds, it's 8 out of 10 for the '06 vs 9 out of 10 for the 7mm.



2) Will my gun actually get the job done, and drop the slavering beast in it's tracks, if I make a good hit? We can help answer that with the energy and velocity thresholds you enter. In this scenario, we're asking 1800 fps and 1500 ftlbs of energy on target (you choose values you're comfortable with in the inputs section). The results table will highlight the output in red if it's below this threshold, and there's also a line on the velocity and energy graphs to show this.







Note that the 7mm Rem (red simulation A) is still carrying plenty of speed/energy to 950 yds. The 30-06 (blue simulation B) with its lower B.C., runs out of steam at only 650 yds.

Also note, that despite both rifles having 0.5 MOA precision, the hit percentage drops below ethical odds way before the 10" target is half MOA. At 1000 yds (~1 MOA for 10"), it's only 27% even for the superior ballistics of a 7mm Mag. Both rifles run out of accuracy long before they run out of power.

In fact, we hit "coin flip" at 650 yds for the 30-06 and 750 yds for the 7mm.

3) But what about recoil? Of course a more powerful cartridge can go further, but what price am I paying? We've got you covered...

By inputting your rifle's weight (and optionally charge weight for more accurate calculations), MERC will calculate the free recoil, which is displayed on the far right.





Hope that helps clarify some of what you can do with MERC.

 

[speedengineer]

I wanted to demonstrate the effect of each one of the uncertainty parameters individually. It is helpful to understand how each parameter is contributing to your shot dispersion at range. For this analysis, I am using a 6.5 PRC with 143 ELDX shooting at a 6.282" diameter target (1 MOA at 600 yards). Edit (I might not have used the 6.5 PRC trajectory data for this example...might be 30-06. But, the same trends apply, despite the label on the plots. I also didn't update the 'title' field for each plot. But just read the descriptions above each image...

MERC is able to consider uncertainty in five parameters:

• Rifle (and shooter) Precision (MOA)
• Muzzle Velocity
• Range Estimation
• Wind Speed Estimation
• Wind Angle Estimation

First, if you set all of the above uncertainties to zero, then all of your shots will hit exactly at your point of aim! You'll calculate a 100% hit probability, obviously.




Second, if you set only the "Rifle (and shooter) Precision" value to be 1 MOA, you'll get 90% hits at 600 yards. This is because we defined a 1MOA rifle as hitting a 1MOA target 9 out of 10 shots. Notice the hit distribution plot shows a scattered hit pattern that is centered around your point of aim.




Next, let's set the Rifle/Shooter Precision value back to zero. Actually, let's put it at 0.05 MOA (almost zero) since it'll make viewing the shot dispersion of the subsequent examples easier (shots won't all be on a single line).

Now, let's specify 10fps of muzzle velocity standard deviation. In this situation, with no wind, MVSD causes vertical dispersion on your target.





Now lets add in some 10mph crosswind from right to left. BUT, still zero uncertainty in wind speed estimation and wind angle estimation. With the addition of a crosswind, you can observe that SDMV results in hits that trend at an angle on the target! What this is showing is that when the rifle has a round that fires at a faster velocity than nominal, it hits the target high (less drop than anticipated) but it also hits that target to the upwind side! Since it was traveling faster, it experienced less wind drift than you held for. Rounds that leave the muzzle at a lower velocity than nominal will hit low, and be blown farther to the downwind side!





Set MVSD back to zero, and specify 10 yards of range uncertainty. Much like MVSD, range uncertainty causes vertical dispersion of the impact points. When the actual range is different from the range that you dialed for, your shot will hit the target either high or low.





...To be continued in the next post. I guess only 10 image attachments are allowed per post!

 

[speedengineer]

...continued...

Ok, set Range uncertainty back to zero. Now let's set wind speed uncertainty to 20%.
Because the wind speed for each shot is different than the nominal wind speed that you hold for, your hits will spread out horizontally.





Let's set wind speed uncertainty back to zero, and set wind angle uncertainty to 25 degrees.
Because we are shooting in a 10mph 'full value' crosswind, any error/deviation in wind direction from that 90 degrees results in LESS crosswind speed than the shooter held for. Example, if wind direction uncertainty for a particular shot was 20 degrees less than actual, then the wind angle used for the shot would be 70 degrees, which will result in less crosswind speed than 10mph. Actually for this case, it would be 9.4 mph. Because of this effect, and wind angle uncertainty with a crosswind will results in shots impacting the target only towards the upwind side!!! Very interesting to note this, as it means that your groupings can become non-centered around your point of aim when shooting in a crosswind situation with variable wind direction! You may even attain a better hit percentage by aiming slightly downwind of your target center. You can experiment with this by entering a 'horizontal scope error' value into MERC.





Lastly, let's combine all of the above mentioned uncertainties together. This shows the total combined effect of everything acting together to affect your rifles accuracy, hit locations, and resulting hit percentage!
1 MOA
10fps MVSD
10yd Range Uncertainty
20% Wind Speed Uncertainty
25 degree Wind Angle Uncertainty

 

[gelton]

Thanks for uploading this...just took a look at it and I am confused...how does it determine your BC because I dont see a place to input it?

 

[entoptics]

Thanks for uploading this...just took a look at it and I am confused...how does it determine your BC because I dont see a place to input it?

Greetings,

You'll have to input 2 full trajectory calculations for each simulation page, using a ballistic calculator with your rifle/environmental information in it. We prefer JBM online calculator. If you go to the tab at the bottom that says "JBM Paste", there's full instructions.

Any time you want to compare a different rifle/environment, you'll have to add the new trajectories.

Seems a little complicated, but after you've done it a couple times, it's easy and only takes a minute or two.

 

[HighUintas]

This is awesome. Thank you!

 

[ShortMountain91]

This thing is amazing. I could play with this for hours. Really wish I hadn't found it while at work....

Sent from my SM-G950U using Tapatalk

 

[tdbaugha]

I downloaded this last year and it worked fine. Now I'm getting errors when trying to run it. I elect for excel to try to repair the file and word pops up with this message:



Repair Result to MERC v2.7.04-20.xml

Errors were detected in file '/Users/torybaughan/Downloads/MERC v2.7.04-2.xlsb'

Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))


What should I do differently to repair this correctly?