Ballistics for Excel Forum
BfX => Ballistics => Topic started by: 375CT on June 05, 2014, 11:17:55 PM

Hi there,
I'd assume most have read about the WEZ analysis Litz is now including as a feature in its PC software.
Would like to hear about opinions on its value as "hit probability" tool, if it's such, and mostly about the math behind it, if it makes sense. So far, I was only aware of Montecarlo simulations but apparently this feaure is not based off of it.
Any comments are greatly appreciated.

WEZ is a Monte Carlo simulation.
I find it hard to opinate about it  it does what it says.
Quite frequently I use a similar tool understand ballistic effects in a target shoot competition setting.
It is available as download on the BfX site. Look for the descriptive text:
"This workbook hosts a simulator with which you can estimate your results in a (multi distance, multi target) match, e.g. the NRA 90 shot full bore regional match course."

WEZ is a Monte Carlo simulation.
I find it hard to opinate about it  it does what it says.
Quite frequently I use a similar tool understand ballistic effects in a target shoot competition setting.
It is available as download on the BfX site. Look for the descriptive text:
"This workbook hosts a simulator with which you can estimate your results in a (multi distance, multi target) match, e.g. the NRA 90 shot full bore regional match course."
Robert,
Thanks for the answer, however how do you know WEZ is based off of Montecarlo? I've read all the available literature by Litz and that word is not present even once.
thanks!

Maybe "Monte Carlo" is a definition issue. In this case, i imagined that input parameters are generated in such a way that they are consistent with a certain probability distribution. Then a trajectory calculated and evaluated. The process is repeated hundreds of times. This is what one might describe as Monte Carlo.

Maybe "Monte Carlo" is a definition issue. In this case, i imagined that input parameters are generated in such a way that they are consistent with a certain probability distribution. Then a trajectory calculated and evaluated. The process is repeated hundreds of times. This is what one might describe as Monte Carlo.
Nice reply, much clear now. Will take a look to your workbook and for sure coming back with more questions, if you don't mind :)

Ok,
I understand what you understand  nothing special meant here. Let me make things less abstract.
Basically what Monte Carlo does is to generate "outcomes", e.g. where a bullet ends, for, varying conditions  the input parameters. The input parameters are varied according to a certain probability distribution. If for example a shooter is able to aim with an accuracy of 1 MoA then people like me model that as a bellshaped (Gaussian) probability distribution that averages to 1 MoA mean inaccuracy. With this we acknowledge the fact that in some cases the shooter aims very well and in others badly.
In a Monte Carlo simulation then, the computer draws a random number and converts that to an inital horizontal and vertical aiming angle. This is done in such a way that average inaccuracy is 1 MoA. After the horizontal and vertical launch angles are chosen, the bullets trajectory is calculated.
If, for instance, in addition the effects of a nonconstant wind is being studied, one might model that primitively as a wind distribution that is half of the time zero and the other half 1 m/s. In a Monte Carlo calculation then one first draws a random number that determines the launch angle, and an another random number is used to determine if there is wind or not in the trajectory calculation (with the afforementioned spreadsheet I deal on a more sophisticated way with non constant wind along a trajectory). Hence we end up with a set of bullet end coordinates that were affected by both wind and the shooters abilities.
In practise, the more inputs one generates according to a probability distribution, the less the details of a probability distribution matters. The distribution might as well be flat, meaning that the probability for a certain input value is constant in a certain range and zero elsewhere.

See Hit probability thread as well. My tool is based on analytical formulas. I have compared the results with litz's tool and the difference is usually less than 5%. I have done some assumptions that are not mathematically exact. However it's still pretty close. In theory benefit is that calculation time is reduced significantly compared to monte carlo method. However I'm not an expert in programming and my code is not as effective as robert's. My program also includes more variables. That's why theoretical benefit in calculation time is not redeemed in practice.
The limitation also is that I can only calculate round targets while with monte carlo target shape is not limited.
Here (https://www.google.fi/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&cad=rja&uact=8&ved=0CCUQFjAB&url=http%3A%2F%2Fwww.dtic.mil%2Fcgibin%2FGetTRDoc%3FAD%3DAD0644106&ei=1numU77QHea6ygPOp4CoDQ&usg=AFQjCNFqPcIRwx2G1yGmdgBCdB4CTfGG7A) is as an article about circular probability error which I have used. It is also far better measure for gun accuracy than extreme spread when it comes to probability of hitting something.
Robert, how many iterations you need to do with monte carlo before you get hit probability say in accuracy of 1%? 99 % of the time?

Excellente discussion and insight! :D
Just a simple question, since I'm not that familiar with Excel programming (just the basics), do you think that using a random number generator (RNG), for using a common programming language, could do the same? I mean, assuming that the RNG is based on a Gaussian distribution?
On the other hand, what other kind of probability distribution do you guys thinks is worth exploring for a realistic scenario?
I find this subject very interesting for developing some sort of BfX simulator, besides the excellent work already done by Robert.
Mman, if you don't me asking, why you state your work is not mathematically exact?

I'll let robert answer the programming stuff...
If you look into commercial programs gaussian distribution is the most common for gun accuracy. To apply anything else you would need really extensive testing and fitting.
I have done a shooting diary as well (see Group and velocity statistics thread) and I have now around 500 groups in my database. When it comes to statistics it's not much but it is something. In avarage all the real world group measures I have saved fit very well to gaussian distribution. So I would say it really is pretty good probability distribution for the application.
If you accept that holes distribute according to gaussian distribution you can calculate lot's of things. For example I have done extensive testing with monte carlo comparing different group size measures. There are lots of them. To mention few: radial extreme spread (the most common and worst), ES X and Y directions, radial standard deviation, SD X and Y directions, diagonal, Circular error probability (which is best I have found). There are at least two important things which define the best measure: 1. effectivenes which means that how many rounds you have to shoot for two different loads to define which is more accurate. 2. independence which means how number of shots or group shape affects to measure in question.
The best feature in SD based group measures is that they are not depended on number of shots in the group like extreme spread measures. For example with monte carlo you can calculate that 10shot group is around 1,6x (if my memory serves me right) larger than 3shot group. And that's only true if groups are round in average. While CEP is in average approx. the same for both 3 or 10shot groups. If group ellipcity factor is not higher than 4.
Mman, if you don't me asking, why you state your work is not mathematically exact?
Read the article about CEP. Derivation includes few approximations at least on ellipcity and bias. Then again I have assumed that all the dispersion factors are independed. This is pretty good approximation but not exactly true as roberts states on Hit probability thread.

In visual basic and as an Excel function I have published random number generators, two of them generate numbers with a constant probability ("flat").
The other one. =bfx_rang(....) generates numbers according to a gaussian distribution.
The properties of Excels random generators did not suit my purpose.
The "flat" distribution is the basis for generating numbers distributed according to another distribution.
For gaussian distributions efficient algorithms can be googled.
Nevertheless, the more random variables involved in a simulation, the less details of a certain random variable matter.

What's wrong with excel's RAND ( ) ? My monte carlo is based on that and I got same averages as some references I used...

In visual basic and as an Excel function I have published random number generators, two of them generate numbers with a constant probability ("flat").
The other one. =bfx_rang(....) generates numbers according to a gaussian distribution.
The properties of Excels random generators did not suit my purpose.
The "flat" distribution is the basis for generating numbers distributed according to another distribution.
For gaussian distributions efficient algorithms can be googled.
Nevertheless, the more random variables involved in a simulation, the less details of a certain random variable matter.
Understood the last part, but it's not clear why RANDOM in VB is not good enough. I got the point on Gaussian but I read somewhere that random() is indeed gaussian, so more confussion for me at least :(

http://office.microsoft.com/engb/excelhelp/randfunctionHP010342816.aspx
This is flat. You can convert it into gaussian if you want.
There is discussion about it:
http://elsmar.com/Forums/showthread.php?t=22358
There seems to be an agreement what robert said about it. It would be interesting to hear why and how bad rand() actually is.

Confirmed here
http://msdn.microsoft.com/enus/library/2dx6wyd4(v=vs.110).aspx
Random in VB.net yields an uniform distribution...now, I need to figure out how bad this could be for creating something akin a WEZ run.
Anyway, like Robert pointed out, a RNG can be easily programmed if such need ever arises with for instance the Ziggurat algorithm.
Mman, thanks for the links, they just confirmed my suspicions on this too.
Now will also review CEP. Well, so far the original questions remains, is Litz's WEZ (a term taken from Air Combat for budget error analysis) flat or not? :\ :\

Well, so far the original questions remains, is Litz's WEZ (a term taken from Air Combat for budget error analysis) flat or not? :\ :\
That's easy one. It must be normal distribution. No sense to use flat distribution, that would lead to strange results.

Well, so far the original questions remains, is Litz's WEZ (a term taken from Air Combat for budget error analysis) flat or not? :\ :\
That's easy one. It must be normal distribution. No sense to use flat distribution, that would lead to strange results.
Makes sense, but how to be sure? I get and agree to the point you made, but making no sense, well, that's the hard part out of this, because we are not dealing with any way to test the actual implementation.
http://ballistipedia.com/index.php?title=Main_Page
Useful link for further discussion.
I understand you are recomending going CEP for a more realistic simulation, am I right on my assumption?

Makes sense, but how to be sure? I get and agree to the point you made, but making no sense, well, that's the hard part out of this, because we are not dealing with any way to test the actual implementation.
Litz speaks about standard deviations when he mentions distribution factors. This alone tells us that probability distribution can't be flat. Another proof is that litz's tool gives approx. same results as mine. And mine is based on normal distributions.
I understand you are recomending going CEP for a more realistic simulation, am I right on my assumption?
No, I'm only recommending CEP for group size measuring. For wez tool you can use monte carlo or CEP depending which properties you value. I decided to go with CEP because for me it was more important to compare different calibers, bullets and conditions than get absolutely accurate results in hitting probability. Problem with monte carlo is that you get slightly different results for every calculation. That's not desirable if you try to compare something.
In practise you can never accurately calculate hitting probability anyway. That's simply because you don't know exact standard daviations for distribution factors. WEZ tool is always at it's best for comparing not for absolute probabilities.

Makes sense, but how to be sure? I get and agree to the point you made, but making no sense, well, that's the hard part out of this, because we are not dealing with any way to test the actual implementation.
Litz speaks about standard deviations when he mentions distribution factors. This alone tells us that probability distribution can't be flat. Another proof is that litz's tool gives approx. same results as mine. And mine is based on normal distributions.
I understand you are recomending going CEP for a more realistic simulation, am I right on my assumption?
No, I'm only recommending CEP for group size measuring. For wez tool you can use monte carlo or CEP depending which properties you value. I decided to go with CEP because for me it was more important to compare different calibers, bullets and conditions than get absolutely accurate results in hitting probability. Problem with monte carlo is that you get slightly different results for every calculation. That's not desirable if you try to compare something.
In practise you can never accurately calculate hitting probability anyway. That's simply because you don't know exact standard daviations for distribution factors. WEZ tool is always at it's best for comparing not for absolute probabilities.
Mman, thanks for the tips, they are much appreciated for sure.

1) My random number generator generates the same output sequence for the same seed.
2) My random number generator is not recalculated if somewhere else on the spreadsheet an (unlinked) cell is recalculated.

Robert can you explain input values of your random number generators?

From Bfx_help(...)
90 ==========================Non volatile random numbers===========================
91 i is a positive integer number, b is the bottom value, t (>b) is the top value
92 <r> is an optional range
93 random_number = BfX_Ran(i; <r>) 0 <= random_number <= 1]
94 b <= random_number <= t = BfX_Ranb(i; b; t; <r>)
95 c and s are center and width of normal distribution
96 normal distribution b <= random_number <= t= BfX_Rang(i; c; s; b; t; <r>)
97 use <r> to link cells to guide Excels calculation order
i is the seed.
<r> is a cell or a range
B C D
2 seed 1234567
3
4 0,051118901 =BfX_Ran(seed)
5 0,207298019 =BfX_Ran(seed;C4)
6 0,529684412 =BfX_Ran(seed;C5)
7 0,164931189 =BfX_Ran(seed;C6)
8 0,760708209 =BfX_Ran(seed;C7)
9 0,711878956 =BfX_Ran(seed;C8)
10 0,434484066 =BfX_Ran(seed;C9)
11 0,032066679 =BfX_Ran(seed;C10)
12 0,018770791 =BfX_Ran(seed;C11)
13 0,197369616 =BfX_Ran(seed;C12)
14
15 bfx_ran refers to the previous cell in order to force
16 a specific order of random number (cell) processing
My random number is non volatile.
Volatile means according to MS:
Excel supports the concept of a volatile function, that is, one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments (if it takes any) has changed. Excel reevaluates cells that contain volatile functions, together with all dependents, every time that it recalculates. For this reason, too much reliance on volatile functions can make recalculation times slow. Use them sparingly.
The following Excel functions are volatile:
NOW
TODAY
RAND
OFFSET
INDIRECT
INFO (depending on its arguments)
CELL (depending on its arguments)

Thanks Robert! I made another WEZ tool based on monte carlo using your bfx_Rang random number.
Input values:
(http://i.imgur.com/qvItXFS.jpg)
CEP based WEZ versus monte carlo based WEZ. Results for 4 different bullets/conditions.
(http://i.imgur.com/61mmPct.jpg)
+/ values for monte carlo are to show 95 % confidence interval which means that 95 % of the time error is less than that. To get that narrow confidence interval I had to calculate 100 000 simulations per bullet per range. It took 92 minutes while CEP tool did the same (and other calculations as well) in 7 sec. However CEP tool seems to give errors of 0  7 percentage points. As expected CEP tool is still most of the time good enough for comparison.

Cool.
would it be possible to include the effects of a hold over/under (and the horizontal equivalent)? (in combination with cant)

Mman,
Is this WEZ tool inlcuded in your Hit Prob spreadsheet? Asking because I'd like to ry it if you don't mind.
LIked the idea of going Monte Carlo for the random sequence then using CEP for hit prob. Nice touch.
I know, you guys are Excel experts, now I'm going to have to do some VB.net and see what I can come up with.
Not quite clear the difference in the MV fields errors, any more detail will be kindly grateful.

would it be possible to include the effects of a hold over/under (and the horizontal equivalent)? (in combination with cant)
This input field allows you to move group center in relation to target, if that is what you meant?
(http://i.imgur.com/XUCdZ1B.jpg)
Is this WEZ tool inlcuded in your Hit Prob spreadsheet? Asking because I'd like to ry it if you don't mind.
Yes, it is. I'll attach it later today.
LIked the idea of going Monte Carlo for the random sequence then using CEP for hit prob. Nice touch.
That's not the case. I calculated hit probability with two different methods:
1. CEP which is nothing to do with random sequence. Basicly it is just analytical way to express hit probability on round target.
2. Monte carlo which is based on Robert's rang.
Not quite clear the difference in the MV fields errors, any more detail will be kindly grateful.
On dispersion factors field you define SD of group (all values are expressed in standard deviations). On group center bias field you define how far away in average group center is from target center.
e.g. "Muzzle velocity variation" of 3 m/s means that your MV SD is 3 m/s. "Wrong muzzle velocity" of 3 m/s means that you have assumed muzzle velocity to be 3 m/s higher than it actually is. > Your ballistic calculation gives you wrong holdover/scope correction clicks which leads to group center shift downwards.

This input field allows you to move group center in relation to target, if that is what you meant?
Yes. I our case, at 300m, people aim with their telecopes at a white sticker 20cm below the electronic target center.

This input field allows you to move group center in relation to target, if that is what you meant?
Yes. I our case, at 300m, people aim with their telecopes at a white sticker 20cm below the electronic target center.
But I guess aiming error is compensated with equivalent scope correction so that group center is on correct place?
You can download my wez tool from here:
http://wikisend.com/download/171348/WEZ CEP&MC eng.xlsm
Most of it is still in finnish, sorry.

Mman,
Great work, really liked the new sheet. Sorry if I miswrote my question, but I meant that MC was just for creating the random sampling, then applying CEP to yield the HP in one of the calcs you did. Please correct me if I'm wrong.
Got it regarding the variations and SD inputs.
Any time later, english comments would be nice, my finnish is awful :(

In case you guys need to go VB.net some day here is a useful RNG (Gaussian) snippet I tested and works really well. The original code was written in C# which I later converted.
Public Class RandomGenerator
Private ReadOnly _random As Random
'indicates that an extra deviates was already calculated
Private _hasAnotherDeviate As Boolean
'The other deviate calculated using the BoxMuller transformation
Private _otherGaussianDeviate As Double
Public Sub New()
Me.New(New Random())
End Sub
Public Sub New(ByVal seed As Integer)
Me.New(New Random(seed))
End Sub
Public Sub New(ByVal random As Random)
_random = random
End Sub
' returns a normally distributed deviate with zero mean and unit variance.
' Adapted from Numerical Recipe page 289: Normal (Gaussian) Deviates
Public Function NormalDeviate() As Double
Dim rsq, v1, v2 As Double
If _hasAnotherDeviate Then
'we have an extra deviate handy. Reset the flag and return it
_hasAnotherDeviate = False
Return _otherGaussianDeviate
End If
Do
v1 = UniformDeviate(1, 1) 'pick two uniform number
v2 = UniformDeviate(1, 1) 'in the square extending from 1 to +1
rsq = v1 * v1 + v2 * v2 'see if they are in the unit circle
Loop While rsq >= 1.0 OrElse rsq = 0.0
'now make the boxmuller transformation to get two normal deviates.
Dim fac As Double = Math.Sqrt(2.0 * Math.Log(rsq) / rsq)
'Return one and save one for next time
_otherGaussianDeviate = v1 * fac
_hasAnotherDeviate = True
Return v2 * fac
End Function
' Returns a uniformly distributed random number between min and max.
Public Function UniformDeviate(ByVal min As Double, ByVal max As Double) As Double
Return (max  min) * _random.NextDouble() + min
End Function
' Returns a random number between 0 and 1
Public Function NextDouble() As Double
Return _random.NextDouble()
End Function
End Class