Solved

Normal Distribution

Posted on 2004-03-29
7
2,050 Views
Last Modified: 2009-03-25
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,cumulative) 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
0
Comment
Question by:littlecharva
  • 3
  • 3
7 Comments
 
LVL 20

Expert Comment

by:TheAvenger
ID: 10708206
NORMDIST is a statistical function. It has a "nice" formula that is given in the Excel help (go to index, write NORMDIST). C# supports all these functions, just be careful to use the largest possible data types in C# (long, double) otherwise you will lose accuracy.

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
0
 
LVL 10

Accepted Solution

by:
eternal_21 earned 250 total points
ID: 10711848
I have put together a C# function that matches the Microsoft Excel's operation when you call NORMDIST with Cumulative=FALSE.

  /// <summary>
  /// Normal Distribution (Probability Mass Function)
  /// </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>
  /// <returns>Returns the normal distribution for the specified mean and standard deviation.</returns>
  static double NormalDistribution(double x, double mean, double deviation)
  {
        return Math.Exp(-(Math.Pow((x - mean)/deviation, 2)/2))/Math.Sqrt(2*Math.PI)/deviation;
  }

This function is consistent with the result shown in the Microsoft Excel help.
 Excel: NORMDIST(42,40,1.5,FALSE) = 0.10934005
 c#: NormalDistribution(42, 40, 1.5) = 0.109340049783996

References:

  http://www.mathstat.uottawa.ca/~givanoff/week8overheadstypeset.pdf
0
 
LVL 10

Expert Comment

by:eternal_21
ID: 10711950
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/
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:littlecharva
ID: 10712041
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
0
 
LVL 10

Expert Comment

by:eternal_21
ID: 10718219
The following code:

  System.Diagnostics.Debug.WriteLine(NormalDistribution(42, 40, 1.5, true));
  System.Diagnostics.Debug.WriteLine(NormalDistribution(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)/deviation;
    }

    private static double CumulativeDistribution(double 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 http://www.experts-exchange.com/Miscellaneous/Math_Science/ or someone else take a look at this code.
0
 

Author Comment

by:littlecharva
ID: 10732548
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
0
 

Author Comment

by:littlecharva
ID: 10739223
I've managed to get it sorted, thanks for the help though.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now