Solved

Normal Distribution

Posted on 2004-03-29
7
2,044 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Bit flags and bit flag manipulation is perhaps one of the most underrated strategies in programming, likely because most programmers developing in high-level languages rely too much on the high-level features, and forget about the low-level ones. Th…
Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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