Simulating NORMDIST function of Excel in C#

ddanbe 0 Tallied Votes 3K Views Share

Well, there was a thread about a frequency function and a bell curve in Excel.
http://www.daniweb.com/forums/thread295203.html
So here it is how about to do the NORMDIST function in Excel in C#.

// Evaluation of the bell or Gauss curve. 
        // See http://en.wikipedia.org/wiki/Normal_distribution
        public static double NormalDist(double x, double mean, double standard_dev)
        {
            double fact = standard_dev * Math.Sqrt(2.0 * Math.PI);
            double expo = (x - mean) * (x - mean) / (2.0 * standard_dev * standard_dev);
            return Math.Exp(-expo) / fact;
        }

        // Simulation of Excel's NORMDIST function, I guess not as it is done there
        public static double NORMDIST(double x, double mean, double standard_dev, bool cumulative)
        {
            const double parts = 50000.0; //large enough to make the trapzoids small enough

            double lowBound = 0.0;
            if (cumulative) //do integration: trapezoidal rule used here
            {
                double width = (x - lowBound) / (parts - 1.0);
                double integral = 0.0;
                for (int i = 1; i < parts - 1; i++) 
                {
                    integral += 0.5 * width * (NormalDist(lowBound + width * i, mean, standard_dev) + 
                        (NormalDist(lowBound + width * (i + 1), mean, standard_dev)));
                } 
                return integral;
            }
            else //return function value
            {
                return NormalDist(x, mean, standard_dev);
            }
        }
Johan_1 0 Newbie Poster

Hi, your code does not work for the cumalative. Here is how I'm doing it:

public static double normdist(double x, double mean, double standard_dev, bool cumalative)
{
    if (cumalative == false)
    {
        double fact = standard_dev * Math.Sqrt(2.0 * Math.PI);
        double expo = (x - mean) * (x - mean) / (2.0 * standard_dev * standard_dev);
        return Math.Exp(-expo) / fact;
    }
    else
    {
        x = (x - mean) / standard_dev;
        if (x == 0)
            return 0.5;
        double t = 1.0 / (1.0 + 0.2316419 * Math.Abs(x));
        double cdf = t * (1.0 / (Math.Sqrt(2.0 * Math.PI)))
                        * Math.Exp(-0.5 * x * x)
                        * (0.31938153 + t
                        * (-0.356563782 + t
                        * (1.781477937 + t
                        * (-1.821255978 + t * 1.330274429))));
        return x >= 0 ? 1.0 - cdf : cdf;
    }
}
ddanbe 2,724 Professional Procrastinator Featured Poster

Hi Johan_1, welcome here at DaniWeb.
As I stated in my title and also have a look at the comment on line 10 of my code, I don't know the exact way how this calculation is done.
For your convenience I tested it again with the values 40,50,5,true.
Here are the results:

me     0.0227501321785498
you    0.0227500620142519
excel  0.0227501319481792

When I want to improve my accuracy I just have to augment the constant on line 13. My trapezoids become smaller that way.
With your numerical approach(derived from some old FORTRAN code?)
you would have to tweak alot of coefficients I guess.

dlhockey12 0 Newbie Poster

for the record, Johan_1's solution worked better for me:

9, 8.4, 4.778143, true = 0.5499645553 in excel vs 0.5105880336699529 from ddanbe and 0.5499645958175403 from Johan_1
4.5, 4.84, 2.604, true = 0.4480583882 in excel vs 0.41651960800453064 from ddanbe and 0.4480583517439033 from Johan_1

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.