littlecharva
asked on
Normal Distribution
Hi,
I need to convert a complicated Excel document that's been created by some Maths boffin into a program in C#. I've been doing really well (despite my Mathematical handicap) until I hit the Excel NORMDIST(x,mean,stdDev,cum ulative) function. System.Math doesn't have any functions to help me out here, and I just can't seem to work out how to convert the formula into C# code.
Can anyone help?
LittleC
I need to convert a complicated Excel document that's been created by some Maths boffin into a program in C#. I've been doing really well (despite my Mathematical handicap) until I hit the Excel NORMDIST(x,mean,stdDev,cum
Can anyone help?
LittleC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you need to calculate the cumulative distribution, let me know and here is a link if you want to do it yourself: http://functions.wolfram.com/GammaBetaErf/Erf/06/01/
ASKER
It is actually the cumulative distribution I need to know, I had a look at the link and it didn't make any sense to me. If you wouldn't mind showing me how to do it, that'd be cool.
LittleC
LittleC
The following code:
System.Diagnostics.Debug.W riteLine(N ormalDistr ibution(42 , 40, 1.5, true));
System.Diagnostics.Debug.W riteLine(N ormalDistr ibution(42 , 40, 1.5, false));
produces the output:
0.908788780275089
0.109340049783996
which matches Microsoft Excel's help file:
NORMDIST(42,40,1.5,TRUE) = 0.908789
NORMDIST(42,40,1.5,FALSE) = 0.10934005
### C#.NET CODE ###
/// <summary>
/// Normal Distribution
/// </summary>
/// <param name="x">The value for which you want the distribution.</param>
/// <param name="mean">The arithmetic mean of the distribution.</param>
/// <param name="deviation">The standard deviation of the distribution.</param>
/// <param name="cumulative">If cumulative is true, functions returns the cumulative distribution, otherwise the function returns the probability mass.</param>
/// <returns>Returns the normal distribution for the specified mean and standard deviation.</returns>
public static double NormalDistribution(double x, double mean, double deviation, bool cumulative)
{
if(cumulative)
return CumulativeDistribution(x, mean, deviation);
else
return NormalDensity(x, mean, deviation);
}
private static double NormalDensity(double x, double mean, double deviation)
{
return Math.Exp(-(Math.Pow((x - mean)/deviation, 2)/2))/Math.Sqrt(2*Math.PI )/deviatio n;
}
private static double CumulativeDistribution(dou ble x, double mean, double deviation)
{
// TODO: Change the number of iterations (16) for more or less precision.
// You could also change the logic of the recursive function (stop calling
// for more terms, when the values are below a specific threshold for example.
return (ErrorFunction((x - mean)/deviation/Math.Sqrt( 2), 0, 16) + 1)/2;
}
private static double ErrorFunction(double x, int iteration, int iterations)
{
double partValue;
partValue = 2/Math.Sqrt(Math.PI)*Math. Pow(-1, iteration)*Math.Pow(x, 2*iteration + 1)/Factorial(iteration)/(2 *iteration + 1);
if(iteration==iterations)
return partValue;
else
return ErrorFunction(x, iteration + 1, iterations) + partValue;
}
private static int Factorial(int x)
{
if(x==0)
return 1;
else
return x*Factorial(x-1);
}
###
Don't trust me though - I'd have the experts at https://www.experts-exchange.com/Miscellaneous/Math_Science/ or someone else take a look at this code.
System.Diagnostics.Debug.W
System.Diagnostics.Debug.W
produces the output:
0.908788780275089
0.109340049783996
which matches Microsoft Excel's help file:
NORMDIST(42,40,1.5,TRUE) = 0.908789
NORMDIST(42,40,1.5,FALSE) = 0.10934005
### C#.NET CODE ###
/// <summary>
/// Normal Distribution
/// </summary>
/// <param name="x">The value for which you want the distribution.</param>
/// <param name="mean">The arithmetic mean of the distribution.</param>
/// <param name="deviation">The standard deviation of the distribution.</param>
/// <param name="cumulative">If cumulative is true, functions returns the cumulative distribution, otherwise the function returns the probability mass.</param>
/// <returns>Returns the normal distribution for the specified mean and standard deviation.</returns>
public static double NormalDistribution(double x, double mean, double deviation, bool cumulative)
{
if(cumulative)
return CumulativeDistribution(x, mean, deviation);
else
return NormalDensity(x, mean, deviation);
}
private static double NormalDensity(double x, double mean, double deviation)
{
return Math.Exp(-(Math.Pow((x - mean)/deviation, 2)/2))/Math.Sqrt(2*Math.PI
}
private static double CumulativeDistribution(dou
{
// TODO: Change the number of iterations (16) for more or less precision.
// You could also change the logic of the recursive function (stop calling
// for more terms, when the values are below a specific threshold for example.
return (ErrorFunction((x - mean)/deviation/Math.Sqrt(
}
private static double ErrorFunction(double x, int iteration, int iterations)
{
double partValue;
partValue = 2/Math.Sqrt(Math.PI)*Math.
if(iteration==iterations)
return partValue;
else
return ErrorFunction(x, iteration + 1, iterations) + partValue;
}
private static int Factorial(int x)
{
if(x==0)
return 1;
else
return x*Factorial(x-1);
}
###
Don't trust me though - I'd have the experts at https://www.experts-exchange.com/Miscellaneous/Math_Science/ or someone else take a look at this code.
ASKER
That works great except when the result is less than 0 or more than 1, then I get a different result than Excel gives.
For example, with
mean = 3.24088418968677
stddev = 0.427899387582919
when x = 2.01490302054226
Your function gives me -0.0912668409947619
Excel gives me 0.00208435088016279
and when x = 4.44265125649032
Your function gives me 1.04564007143895
Excel gives me 0.997511505019538
All of the results Excel seems to give back are between 0 and 1, which is the way I need it to work because I need to display the results as percentages.
There's another 250 points in it for you if you can help me out on this one. I don't understand any of what I'm doing, I just need to transfer the programming from Excel to C# and get the same results.
Cheers,
LittleC
For example, with
mean = 3.24088418968677
stddev = 0.427899387582919
when x = 2.01490302054226
Your function gives me -0.0912668409947619
Excel gives me 0.00208435088016279
and when x = 4.44265125649032
Your function gives me 1.04564007143895
Excel gives me 0.997511505019538
All of the results Excel seems to give back are between 0 and 1, which is the way I need it to work because I need to display the results as percentages.
There's another 250 points in it for you if you can help me out on this one. I don't understand any of what I'm doing, I just need to transfer the programming from Excel to C# and get the same results.
Cheers,
LittleC
ASKER
I've managed to get it sorted, thanks for the help though.
You can see the help topic with the formula also here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/sec02.asp
More info:
http://support.microsoft.com/default.aspx?kbid=827371&product=xl2003
What normal distribution is:
http://pirate.shu.edu/~wachsmut/Teaching/MATH1101/Testing/distribution.html