Page 2 of 2

Re: NORM.DIST methodology

Posted: June 23rd, 2018, 4:06 pm
by ppauper
Thanks but the problem is the ERF number. I'm using 0.842700793 which multiplied by 1/sqrt(2) = 0.59... and not the 0.682689 that you have.
NO
erf is a function.
you want erf( 1/sqrt(2) )
you have used ( erf( 1) ) /sqrt(2)

Re: NORM.DIST methodology

Posted: June 23rd, 2018, 8:06 pm
by Cuchulainn
I agree with ppauper: start again. You are missing something in your analysis.. 

I already showed how I did it (and I checked it multiple times).

Let us recall it.
return 0.5*std::erfc(-x / std::sqrt(2.0));
Image

Re: NORM.DIST methodology

Posted: June 23rd, 2018, 8:20 pm
by Cuchulainn
I've already replicated ERF via Abramowitz & Stegun 7.1.5 but ERF does not  equal NORM.DIST(x,mean,standard_dev,cumulative). 

No one here ever claimed they were the same! (except possibly yourself)

BTW are you saying you coded up 7.1..5? If yes, how did you truncate the series?

Re: NORM.DIST methodology

Posted: June 23rd, 2018, 8:23 pm
by Cuchulainn
I've already replicated ERF via Abramowitz & Stegun 7.1.5 but ERF does not equal NORM.DIST(x,mean,standard_dev,cumulative). 

=NORM.DIST(1,0,1,TRUE) = 0.84134475
=ERF (0,1) = 0.84270079
Sure, but see how I do it with erfc. i.e. N() is computed in terms of ERF.
recall the last sentence.

Re: NORM.DIST methodology

Posted: June 24th, 2018, 3:42 pm
by Cuchulainn
Thanks but ERF or ERFC that doesn't replicate =NORM.DIST(1,0,1,TRUE) = 0.84134475 - unless I'm missing something here.
I checked the stuff 
Excel
N(1) = 0.841344746
C++
erf(1) = Same as ppaupers' up to 16 digits (0.8427 0079 2949 7149)
N(x) = 0.8413447460685428

Re: NORM.DIST methodology

Posted: June 24th, 2018, 5:53 pm
by miltenpoint
Thanks guys. I was being a bit slow there but all clear now. I appreciate your help - you are stars !!!

Re: NORM.DIST methodology

Posted: June 24th, 2018, 6:41 pm
by Cuchulainn
You're welcome.

Re: NORM.DIST methodology

Posted: March 4th, 2019, 10:36 pm
by Jordy
Don't forget the paper "Better approximations to cumulative normal functions" by Graeme West. If I remember correctly, the double precision implementation matches Excel exactly.

Cheers,
Jordy

Re: NORM.DIST methodology

Posted: March 5th, 2019, 10:08 am
by Cuchulainn
Don't forget the paper "Better approximations to cumulative normal functions" by Graeme West. If I remember correctly, the double precision implementation matches Excel exactly.

Cheers,
Jordy
Indeed. I spent some time on this

1. In C++11 N(x) is essentially a built-in function as already noted.
2. I did extensive (stress) tests (randomly generated parameters) on BVN using Graeeme's code and Quantlib. Same output.
3. As sanity check of 2, I posed BVN as a Goursat PDE that I solved using Box (2nd order) and extrapolated schemes
4. For TVN you can solve it as a Goursat-Fuchs PDE.
5. All tested with Chooser option from Collector's  Big Book.
6 So, Excel implements GENZ algorithm?

Tactic 2 (PDE posing) can be posed for any distribution (I examine Student in my 2018 C++ book) and it has a gear-box to tune to desired accuracy.
double N(double x)
{ // aka CdfN(x)

  return 0.5*std::erfc(-x / std::sqrt(2.0));
}

Re: NORM.DIST methodology

Posted: June 20th, 2019, 2:13 pm
by ikicker
NORM.DIST(x,mean,standard_dev,cumulative). When cumulative is TRUE this function returns the cumulative density function which is the normal density function integrated from negative infinity to x.
I understand that there is no closed form solution for  integrating the NDF but does anyone know which methodology Excel uses? I've tried series approximations, continuous fractions and polynomials but get slightly different values .
There is a closed form approximation. Approximation isn't always bad. It's like when you deal with duration and convexity. We don't traditionally go beyond convexity, but it is possible to get a more accurate estimate. It's a taylor series. There is an ERF function. The closed form equation extends out until the amount is meaningless. That's probably what Excel does.