Oh Boy... Little manual math. But this is doable. Thanks for the idea.
- Mr. Bill
…Little manual math.
Hi Mr. Bill,
You can use 2 summary fields to automatically calculate the average (approximation of mean) and standard deviation.
That leaves a calculation field for the normal distribution.
( 1 / Sqrt ( 2 * Pi * standard_deviation^2 )) * Exp ( - ( x - average )^2 / ( 2 * standard_deviation^2 ) )
Hey Jade- Looks like that worked like a charm. Can't tell you how helpful this is to me. Many thanks for your contribution.
I just noticed something. This formula is providing the probability density and I need the cumulative form. I'm far form a mathematician. I've just used normdist in excel and magically, I have the results I needed. Any idea how to get the true from (cumulative form)?
OK…this is well into my category: quit while you're ahead.:smileywink:
I found an approximation of the cumulative form using standard functions but have no means of verifying the results.
Let(z=(x-Average)/(Standard_deviation*Sqrt(2)) ; 0.5*(1+(z/Abs(z))*Sqrt(1 - Exp(-(z^2)*((4/Pi)+0.140012*z^2)/(1+0.140012*z^2)))))
If this does not work, perhaps someone else can help.
WOW! I think my head just exploded :smileytongue:
It looks like its doing what I want it to do... Basically, give a score based on the inputs. So this way, the highest input (number) is given the highest score (say 5 is .5) and lowest is given the lowest (say 1 is .1).
It appears to be doing this. Example:
The newnorm is the column with the updates formula you gave me and its matching excel to the decimal. Nice work.
Many thanks for staying with this. Now I actually have to build what I want, but at least I know it can be done.
The updated formula may give an error when x=Average (divide by zero) so I revised it to check for this:
Let([z=(x-Average)/(Standard_deviation*Sqrt(2)) ; a=0.140012] ; If(z=0; 0.5; 0.5*(1+(z/Abs(z))*Sqrt(1 - Exp(-(z^2)*((4/Pi)+a*z^2)/(1+a*z^2))))))
Excellent. Again, many thanks for your assistance.
This is very similar to what I have been looking for. I have calculated z scores for growth parameters of children based on CDC growth data. I'm trying to get FM to convert the z score to the percentile. Could you tell me what the a=.140012 represents in your equation?
"a" is a constant and an approximation of: (8*(Pi - 3)) / (3*Pi*(4 - Pi)). Using this value, the largest error in the fit of the distribution is about 0.00012. The formula above using standard FMP functions is only an approximation of an integral equation.
See the definition of the Error Function "Approximation with elementary functions" for more details.
Hope this helps a bit…
P.S. This also may be of interest: Normal Cumulative Distribution "Standard deviation and confidence intervals"